SET STATISTICS XML (SQL Server Compact)
Causes SQL Server Compact to execute Transact-SQL statements and generate detailed information about how the statements were executed in the form of a well-defined XML document.
The setting of SET STATISTICS XML is set at execute or run time and not at parse time.
When SET STATISTICS XML is ON, SQL Server Compact caches execution information for each Transact-SQL statement. After this option is set ON, information about all subsequent Transact-SQL statements is returned until the option is set to OFF.
SET STATISTICS XML does not have to be the only statement in a batch.
SET STATISTICS XML caches information in an XML document. Each statement after the SET STATISTICS XML ON statement is reflected in the output by a single document. This cached XML document contains the set of statements, followed by the details of the execution steps. The output shows run-time information such as the costs, accessed indexes, types of operations performed, join order, the number of times a physical operation is performed, the number of rows each physical operator produced, and more. To retrieve the plan information from cache, use the SELECT @@SHOWPLAN value.
The document that contains the XML schema for the XML output by SET STATISTICS XML is copied during setup to a local directory on the computer on which SQL Server is installed. It can be found on the drive that contains the SQL Server installation files, at:
\Microsoft SQL Server\90\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml.xsd
SET STATISTICS XML ON; -- First query SELECT [Employee ID] FROM [Employees] WHERE [City]='London'; -- Second query SELECT [Employee ID] FROM [Employees] WHERE [Country]='USA'; SELECT @@SHOWPLAN; SET STATISTICS XML OFF;