T-SQL commands vs. XML AUTO in SQL Server

XML functions may use many more resources than the standard T-SQL commands, depending on the amount of data. SQL Server database expert Michelle Gutzait shows performance implications for each when comparing the XML AUTO function with T-SQL commands.

XML AUTO function vs. T-SQL commands

As a DBA, I tend to look at performance issues and therefore would like to make sure that performance will not be affected by the use of XML (Extensible Markup Language). In this first article of a series, I'll show performance implications by comparing the XML AUTO function with the standard T-SQL commands. In my testing process I will cover only a small and basic portion of the XML AUTO functionality. It is always recommended you test the XML functions that you intend to use, in your own environment.

Testing environment description

For my testing, I've created a table containing 10,000 rows, as described below:

The table contains rows as follows:

select * from Employes

FOR XML Auto with large amounts of data

I used the following queries on my table, starting with a standard T-SQL query and then adding more XML structures to the command each time:

SELECT * FROM Employes
Go
SELECT * FROM Employes FOR XML AUTO
go
SELECT * FROM Employes FOR XML AUTO, TYPE
go
SELECT * FROM Employes FOR XML AUTO, TYPE, ELEMENTS
go
SELECT * FROM Employes FOR XML AUTO, TYPE, ELEMENTS,
ROOT
go

I monitored these commands in SQL Profiler. I figured out that the Duration column in the SQL Profiler is not accurate. The executed XML command was already shown in the Profiler, while the Query Analyzer still processed the results. So I will ignore this column, and look at the other resources: CPU and I/O. Here are the monitoring results showing three executions of the five commands:

It seem to be consistent that the T-SQL query is performing better than the rest. The XML query FOR XML AUTO is using more than eight times more CPU but the same amount of I/O. The complex XML commands are issuing more than 80 times (!) more reads than the T-SQL command, also many writes and above six times the CPU.

When analyzing the Statistics I/O issued by the commands:

Table 'Employes'. Scan count 1, logical reads 8247, physical
reads 0, read-ahead reads 7520, lob logical reads 0, lob
physical reads 0, lob read-ahead reads 0.
Table 'Employes'. Scan count 1, logical reads 8247, physical
reads 0, read-ahead reads 4221, lob logical reads 0, lob
physical reads 0, lob read-ahead reads 0.
Table 'Employes'. Scan count 1, logical reads 8247, physical
reads 500, read-ahead reads 2586, lob logical reads 0, lob
physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 7, physical
reads 1, read-ahead reads 0, lob logical reads 229128, lob
physical reads 285, lob read-ahead reads 43082.
Table 'Employes'. Scan count 1, logical reads 8247, physical
reads 0, read-ahead reads 1394, lob logical reads 0, lob
physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 7, physical
reads 0, read-ahead reads 0, lob logical reads 230170, lob
physical reads 0, lob read-ahead reads 43115.
Table 'Employes'. Scan count 1, logical reads 8247, physical
reads 125, read-ahead reads 3835, lob logical reads 0, lob
physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 7, physical
reads 0, read-ahead reads 0, lob logical reads 230170, lob
physical reads 140, lob read-ahead reads 43115.

It is obvious the more complex XML commands are creating a Worktable. It also shows that every command is doing more work than the previous one.

FOR XML Auto with smaller amounts of data

I will do the same test using the same table, but reading less data.

First, I will create an index on the ID column:
> create unique clustered index UQ_Employes on Employes (id)

Then I will query my table with a WHERE clause, querying 100 rows and then 1000 rows.

100 rows:

SELECT * FROM Employes where id between 5000 and
5100
go
SELECT * FROM Employes where id between 5000 and
5100
FOR XML AUTO
go
SELECT * FROM Employes where id between 5000 and
5100
FOR XML AUTO, TYPE
go
SELECT * FROM Employes where id between 5000 and
5100
FOR XML AUTO, TYPE, ELEMENTS
go
SELECT * FROM Employes where id between 5000 and
5100
FOR XML AUTO, TYPE, ELEMENTS, ROOT
go

SQL Profiler shows no real difference between the commands:

Also, no worktable is created and all the commands have the same amount of work:

Table 'Employes'. Scan count 1, logical reads 12, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
Table 'Employes'. Scan count 1, logical reads 12, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
Table 'Employes'. Scan count 1, logical reads 12, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
Table 'Employes'. Scan count 1, logical reads 12, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
Table 'Employes'. Scan count 1, logical reads 12, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.

Execution plan also shows same amount of work:

1000 rows:

SELECT * FROM Employes where id between 5000 and
6000
go
SELECT * FROM Employes where id between 5000 and
6000
FOR XML AUTO
go
SELECT * FROM Employes where id between 5000 and
6000
FOR XML AUTO, TYPE
go
SELECT * FROM Employes where id between 5000 and
6000
FOR XML AUTO, TYPE, ELEMENTS
go
SELECT * FROM Employes where id between 5000 and
6000
FOR XML AUTO, TYPE, ELEMENTS, ROOT
go

SQL Profiler:

Statistics I/O:

Table 'Employes'. Scan count 1, logical reads 87, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
Table 'Employes'. Scan count 1, logical reads 87, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
Table 'Employes'. Scan count 1, logical reads 87, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 7, physical
reads 0, read-ahead reads 0, lob logical reads 5222, lob
physical reads 0, lob read-ahead reads 242.
Table 'Employes'. Scan count 1, logical reads 87, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 7, physical
reads 0, read-ahead reads 0, lob logical reads 5229, lob
physical reads 0, lob read-ahead reads 245.
Table 'Employes'. Scan count 1, logical reads 87, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 7, physical
reads 0, read-ahead reads 0, lob logical reads 5229, lob
physical reads 0, lob read-ahead reads 245.

Execution plan:

Using a Worktable – when?

Where is the limit for the optimizer to use a Worktable? It depends on the amount of optimizer work related to the memory and data size that needs to be used for the XML parsing. The returned XML in my queries is stored in one large XML variable in the memory. The above limit is not a definite number. In the SQL Server Developer Center it says: "Variables and parameters of type XML can be up to 2 GB. They use main memory as storage as long as the values are small. However, large values are stored in tempdb."

Conclusion

XML data and functions may use many more resources than the standard T-SQL. Therefore, if the queries involve large amounts of data or the XML function is more complex, consider using standard T-SQL in the database level, instead. It is always recommended to test your XML performance in order to make sure the usage of XML in the database will not degrade application performance.

Learn about XML in SQL Server 2005

If you are not familiar with the XML functionality in SQL Server 2005, here are some useful links:

ABOUT THE AUTHOR:
Michelle Gutzait works as a senior database consultant for ITERGY International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the last 10 years, she has worked exclusively with SQL Server. Her skills include SQL Server infrastructure design, database design, performance tuning, security, high availability, VLDBs, replication, T-SQL/packages coding, and more.

This was first published in July 2007

Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close