Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Performance impact of adding XML column?

Author  Topic 

malachi151
Posting Yak Master

152 Posts

Posted - 2012-10-08 : 15:37:09
What are the performance impacts, if any, of adding an XML column to a table, in relation to queries against the non-XML columns?

In other words, if an add an XML column to a table, will it impact queries against the non-XML columns?

I know it has an impact on distributed queries, i.e. if you try to query a table across a Linked Server that has an XML column in it you can't do that, even if you don't include the XML column in the query, etc.

So let's say that I added an XML column, and its mostly NULL, how would this impact performance, how exactly is it stored on the pages, etc?

Thanks

--------------------------------------------------
My blog: http://practicaltsql.net/
Twitter: @RGPSoftware

chadmat
The Chadinator

1974 Posts

Posted - 2012-10-08 : 15:57:38
This behavior is somewhat configureable, but by default, I think XML is stored In Row. So it would affect your page density (Less rows per page). If they are mostly null, then the effect would be minimal.

-Chad
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-10-09 : 01:47:27
There are ways of configuring XML to be stored off-row check http://www.sqlserver-dba.com/2012/09/sql-server-large-value-types-out-of-row-and-performance.html, which replaces the data row with a 16 byte text pointer. Your potential performance hit may be large scans being forced therefore high IO. It depends on what portion of your queries are referencing the XM column.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

- Advertisement -