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.

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

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

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.

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.

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.

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

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.

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.

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