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
- Open SQL Server Profiler and create a new trace connecting to the desired database against which you wish to record the trace.
- Under the “Events Selection” tab check “Show all events”, check the “Performance” -> “Showplan XML” row and run the trace.
- While the trace is running, do whatever it is you need to do to get the slow running query to run.
- Wait for the query to complete and stop the trace.
- 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.