How are you query plan evaluation skills? Reviewing a query plans is often the easiest way to find out why a specific query is not performing well. You can use the standard reports in SQL Server to identify long running queries, or queries that use a lot of resources. Once identifying those queries you can attempt to execute them and identify the query plan.
There are lots of great helps posted on the web to assist you in optimizing your queries. SSWUG has classes providing even more detailed help on a frequent basis. If you sign up for SSWUG resource access you don’t have to wait for the next one, but can review previous presentations on your schedule.
If SQL Server graphical plans are new to you then here is what you need to do. Open the query to evaluate in a query window. In the tool bar, identify the icon for Show Plan and turn that on. Execute your query and a new table will show up in the results with the query plan.
The way to read the plan is from right to left, top to bottom. Starting at the far right you will see the first thing SQL Server is going to perform. Each new step that is appended to the work is added in.
Clicking on each image reveals details of what is being done. It demonstrates the part of your query being performed, what index is being used to perform the step, and identifies the percentage of overall work that is being done for that step.
Look for the longest running steps and see if you can’t optimize them. Look for table scans where an index may be a better option. Look for multiple steps accessing the same table, probably using different indexes. Maybe a composite index will perform better.
These are just a few high level tips for working with query plans. This is an area that you may need to understand more and more as you work with SQL Server databases.
Share your favorite tip for optimizing queries using query plans. Leave a comment here, or drop an email to btaylor@sswug.org.
Cheers,
Ben