Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Sherry Li's BI Corner

Always wanting to publish novels, but having the fortune to work in the data warehouse corner of technology, Sherry Li started to write the mysteries of the Microsoft Business Intelligence. She writes everything from T-SQL to MDX, ETL to Expressions to Scripting, Reporting to Cubes. You can find her writings at bisherryli.wordpress.com.

SQL #52 – set statistics xml on

When tuning SQL queries, I find that I use the following three SET options quite often. 

set showplan_xml on

When showplan_xml option is ON for a session, the execution of SQL queries in the same session will only show the estimated execution plan. No query results will be generated.

Here is an example. The query is executed in AdventureWorksDW2012.

image

What you will get is the XML presentation of the estimated execution plan.

image

Most often, I’d just click on the XML link, and look at the graphical version of the execution plan.

image

Once in the graphical editor of the XML execution plan, I can right-click on the surface, and have options to either save the execution plan as a *.sqlplan file, or to show the actual XML code in the XML editor. The .sqlplan file can be attached to emails and sent to anybody who can help with the tuning. SQL Server Management Studio can read .sqlplan file in both graphical mode and XML mode.

image

This is part of the XML code. Although most people prefer to read execution plans in he graphical mode, the XML version can be handy if you want to do some text searching.

image

set statistics xml on

When statistics xml option is ON for a session, the execution of SQL queries in the same session will show query results and the actual execution plan.

Now try to set the statistics xml on and execution the query shown in the following screenshot.

image

Here are the query result and also the actual execution plan you will get.

image

Since I prefer coding everything in T-SQL, using the SET commands is my natural choice. If you prefer using the graphical tools, you can access both options in SQL Server Management Studio. From the menu Query, you can toggle the options on/off.

image

set statistics io on

When statistics io option is ON for a session, the execution of SQL queries in the same session will show query results and in the Message tab, it will show the IO statistics.

Using the same query, here is the IO stats you will see.

image 

SQL query tuning is more than just knowing how to get the execution plans, but it’s a start. Enjoy reading the execution plan.


Comments

Leave a comment on the original post [bisherryli.com, opens in a new window]

Loading comments...