title: SQL Server XML Column Performance
body: <p>Converting nText columns which contained XML to the XML data type has resulted in worse performance in SQL Server. </p>
  
  <p>I am currently working on a project where nText columns have been used to store valid XML. I have successfully migrated these columns to the XML data type.  However according to SQL Profiler the performance of the XML data type is worse than using nText or nvarchar(max) to store the XML.  Everything I have read implies that this should not be the case.</p>
  
  <p>In order to verify this I created two tables with the same indexes etc</p>
  
  <pre><code>Table Name Order1
  [id] [int] IDENTITY(1,1) NOT NULL,
  [uid] [varchar](36) NOT NULL,
  [AffiliateId] [varchar](36) NOT NULL,
  [Address] [ntext] NOT NULL,
  [CustomProperties] [ntext] NOT NULL,
  [OrderNumber] [nvarchar](50) NOT NULL,
  ...
  
  Table Name Order2
  [id] [int] IDENTITY(1,1) NOT NULL,
  [uid] [varchar](36) NOT NULL,
  [AffiliateId] [varchar](36) NOT NULL,
  [Address] [xml] NOT NULL,
  [CustomProperties] [xml] NOT NULL,
  [OrderNumber] [nvarchar](50) NOT NULL,
  ...
  </code></pre>
  
  <p>I have then copied the data using a select/insert statement and rebuilt the indexes on both the tables.  I then created a script with the following SQL.</p>
  
  <pre><code>DBCC DROPCLEANBUFFERS
  GO
  --Part1
  Select id, uid, AffiliateId, Address, CustomProperties, OrderNumber from [dbo].[Order1] where uid = 'F96045F8-A2BD-4C02-BECB-6EF22C9E473F'
  Select id, uid, AffiliateId, Address, CustomProperties, OrderNumber from [dbo].[Order1] where uid = 'A3B71348-EB68-4600-9550-EC2CF75698F4'
  Select id, uid, AffiliateId, Address, CustomProperties, OrderNumber from [dbo].[Order1] where uid = 'CB114D91-F000-4553-8AFE-FC20CF6AD8C0'
  Select id, uid, AffiliateId, Address, CustomProperties, OrderNumber from [dbo].[Order1] where uid = '06274E4F-E233-4594-B505-D4BAA3770F0A'
  
  DBCC DROPCLEANBUFFERS
  GO
  --Part2
  Select id, uid, AffiliateId, Address, OrderNumber,  
  CAST(CustomProperties AS xml).query('CustomProperty/Key[text()="AgreedToTerms"]/../Value/text()')  as "TermsAgreed" 
  from Order1
  
  DBCC DROPCLEANBUFFERS
  GO
  --Part3
  Insert Into Order1 uid, AffiliateId, Address, CustomProperties, OrderNumber
  Select NewId(), AffiliateId, Address, CustomProperties, OrderNumber + 'X' from [dbo].[Order1] where uid = 'F96045F8-A2BD-4C02-BECB-6EF22C9E473F'
  
  Insert Into Order1 uid, AffiliateId, Address, CustomProperties, OrderNumber
  Select NewId(), AffiliateId, Address, CustomProperties, OrderNumber + 'X' from [dbo].[Order1] where uid = 'A3B71348-EB68-4600-9550-EC2CF75698F4'
  
  Insert Into Order1 uid, AffiliateId, Address, CustomProperties, OrderNumber
  Select NewId(), AffiliateId, Address, CustomProperties, OrderNumber + 'X' from [dbo].[Order1] where  uid = 'CB114D91-F000-4553-8AFE-FC20CF6AD8C0'
  
  Insert Into Order1 uid, AffiliateId, Address, CustomProperties, OrderNumber
  Select NewId(), AffiliateId, Address, CustomProperties, OrderNumber + 'X' from [dbo].[Order1] where uid = '06274E4F-E233-4594-B505-D4BAA3770F0A'
  
  DBCC DROPCLEANBUFFERS
  GO
  -- Part4 This updates a .5M row table.
  Update [dbo].[Order1] Set CustomProperties = Cast(CustomProperties as NVARCHAR(MAX)) + CAST('' as NVARCHAR(MAX)), Address = Cast(CustomProperties as NVARCHAR(MAX)) + CAST('' as NVARCHAR(MAX))
  </code></pre>
  
  <p>The results average results from the SQL Profiler are as follows:-</p>
  
  <pre><code>NTEXT
  
  +-------+-------------+-------------+-------------+-------------+
  | Test  |     CPU     |    Reads    |   Writes    |  Duration   |
  +-------+-------------+-------------+-------------+-------------+
  | Part1 | 281.3333333 | 129.3333333 |           0 |         933 |
  | Part2 | 78421.66667 |     5374306 | 10.66666667 | 47493.66667 |
  | Part3 | 281.6666667 |         616 | 27.66666667 | 374.6666667 |
  | Part4 | 40312.33333 | 15311252.67 |      320662 |       67010 |
  | Total |             |             |             | 115811.3333 |
  +-------+-------------+-------------+-------------+-------------+
  
  
  XML
  
  +-------+-------------+-------------+-------------+-------------+
  | Test  |     CPU     |    Reads    |   Writes    |  Duration   |
  +-------+-------------+-------------+-------------+-------------+
  | Part1 |         282 | 58.33333333 |           0 | 949.3333333 |
  | Part2 | 21129.66667 | 180143.3333 |           0 | 76048.66667 |
  | Part3 |         297 | 370.3333333 | 14.66666667 |         378 |
  | Part4 | 112578.3333 | 8908940.667 | 145703.6667 | 114684.3333 |
  | Total |             |             |             | 192060.3333 |
  +-------+-------------+-------------+-------------+-------------+
  </code></pre>
  
  <p>Is the test script flawed? Or is there some other optimisation that needs to be carried out for xml data type columns out side of <a href="https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/administrator/ms345115(v=sql.90)" rel="nofollow noreferrer">https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/administrator/ms345115(v=sql.90)</a></p>
  
  <p>I would expect the XML column type to outperform ntext.</p>