Monitoring performance of a query by SET STATISTICS XML
SET STATISTICS XML is an XML version of the actual execution plan. It provides all the information in XML format which we used to get in graphical format in the actual execution plan.
Getting ready
We are going to use the SELECT query given in the previous recipe's How to do it... section by replacing SHOWPLAN_XML with SET STATISTICS XML. The main intention to use the same query from the previous recipe Monitoring performance of query by SET SHOWPLAN_XML is to see the difference between two execution plan. Generally, if an index and statistics' histogram is updated, then the execution plan on sampling (estimated execution plan) and execution plan on real table (actual execution plan) remain the same. If you find any difference between these two plans, then it is a time to investigate that.
How to do it...
Perform the following steps to use SET STATISTICS XML:
Write down the following query in SSMS from Start |All Programs | SQL Server...
