Using Query Execution Plans

One useful tool that SQL Server offers is the ability to see query execution plans. An execution plan shows you how SQL Server breaks down a query and also identified where issues might exist within the execution plan. Once you have identified statements that take a long time to complete, you can then look at the execution plan to determine tuning needs.

You can obtain an execution plan either by…

#1 Using SQL Server Management Studio

“Include Actual Execution Plan” menu item (found under the “Query” menu) is ticked and run your query as normal.

#2 Using SQL Sever Profiler

1. Open SQL Server Profiler and create a new trace connecting to the desired database against which you wish to record the trace.
2. Under the “Events Selection” tab check “Show all events”, check the “Performance” -> “Showplan XML” row and run the trace.
3. While the trace is running, do whatever it is you need to do to get the slow running query to run.
4. Wait for the query to complete and stop the trace.
5. To save the trace right click on the plan xml in SQL Server Profiler and select “Extract event data…” to save the plan to file in XML format.

Sources:

https://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan