Data Management

Tune your SQL Server scripts with Query Analyzer

Understanding how SQL Server executes your queries and scripts can give you a great start toward optimizing performance. See how the Query Analyzer tool makes the tuning process easier.


I like to think of the bundled SQL Server tools as an inverted pyramid with tools for diagnosing and reviewing general issues at the top, and those for finding and diagnosing specific areas of concern at the bottom. Besides providing a handy way to write SQL scripts, Query Analyzer is the resource to use when you have a well-defined problem you need to solve in a particular SQL script. If you need to figure out which of your queries is keeping a particular table locked, you'll want to use Query Analyzer.

The key diagnostic feature of Query Analyzer is that it can display the execution plan for a query. The execution plan can give you all kinds of useful information, such as how and when indexes are being used or not being used during the execution of a query. It can also show you a number of other details, such as sorts, parallelism, nested loops, and other things that SQL Server must do to execute a given query.

The usefulness of having a plan
With Query Analyzer, you can view the estimated plan without running the query itself, or view the actual plan after executing the query. Obviously, the actual plan is more accurate because it is based on physically running the query against the database. But for heavily utilized systems and/or long-running queries, this may not be the best choice. Typically, I run this utility when I think a particular query may be having a problem, or if I expect that a query will be called frequently enough in the application that it could possibly cause performance problems.

You can view the estimated execution plan for a query by entering your SQL statement into Query Analyzer and then pressing [Ctrl]L. For the actual plan, you’ll need to press [Ctrl]K and then run the query. The execution plan will then be shown in a tab of the Results pane. Plans can be a little hard to read at first because they aren't read from left to right as you read English; instead, they are read from right to left.

A plan in action
Here’s an example you can follow to see how to use Query Analyzer to view an execution plan. Consider the following query that returns a particular employee’s territories and associated regions from the Northwind database:
 
SELECT
TerritoryDescription, RegionDescription
FROM
Employees e
JOIN EmployeeTerritories et ON e.employeeid = et.employeeid
JOIN Territories t ON et.territoryid = t.territoryid
JOIN Region r ON t.regionid = r.regionid
WHERE
e.employeeid = 1
ORDER BY
TerritoryDescription, RegionDescription

 

The results of the query show that EmployeeId number 1 has two territories in the Eastern region, as shown in Figure A.

Figure A
Query results


Figure B shows the estimated execution plan that SQL Server generated for the example query. Each node in the diagram represents a step SQL Server took to execute the example query. Notice that each node includes an estimated relative cost, and the name of each index SQL Server uses while executing the plan is displayed.

Figure B
The execution plan


You can mouse over each node in the execution plan to get more information about that step, such as the type of operation that node represents, the number of rows affected, and a breakdown of the costs associated with that step. Figure C shows the additional information provided for the first node shown in Figure B. The information in these pop-up windows can give you valuable clues as to what improvements, if any, you can make to your query.

Figure C


Works in conjunction with Index Tuning Wizard
You can also use Query Analyzer in conjunction with SQL Server’s Index Tuning Wizard (ITW). I typically use SQL Server Profiler to gather data that I then feed into the ITW. But I’ll sometimes use Query Analyzer instead if I have a query that I think is representative of the type of activity I'm concerned with testing. You can access ITW from the Query menu; it makes performance recommendations about the SQL script in the active editor pane.

For additional information
For more about the ITW, check out “Tweak application performance with the Index Tuning Wizard.”

 

Editor's Picks