Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
#SQLServer 46
SQL Server 44
SSRS EXPERT 41
BI 36
#BI 33
brh 28
SSIS BEST PRACTICES 23
TSQL 19
MS BI STACKS 18
SSRS 17

Archive · View All
March 2011 24
July 2011 17
February 2011 15
April 2010 13
June 2011 11
May 2011 11
April 2011 11
July 2010 11
March 2010 8
December 2011 7

SQL #52 - set statistics xml on

Oct 24 2013 4:03AM by Sherry Li   

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.

Tags: 


Sherry Li
14 · 12% · 3827
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]