Using Query Analyzer to adjust SQL Server scripts

Query Analysis | server | script

I like to think of the tools bundled with SQL Server as inverted pyramid, the tools for diagnosing and checking general problems at the top, looking for and diagnosing specific areas of problems at the bottom. In addition to providing a convenient way to write SQL scripts, Query Analyzer is the resource that you need to use when you need to resolve specific problems in a particular SQL script. If you need to indicate which query is holding the lock on a particular table, you will also want to use the Query Analyzer.

The key diagnostic feature of Query Analyzer is its ability to display a query's execution plan. This execution plan will provide you with various types of useful information, such as how and when to use or not to use the index during the execution of the query. It also provides many other details, such as sorting, parallelism (parallelism), nested loops, and other things that SQL Server must do when executing a specified query.

Benefits of the Plan

With the Query Analyzer, you can view the projected plan without having to run the query itself, or view the actual plan after the query is executed. Obviously, the real plan is more accurate because it is physically running the query on the database. However, this approach may not be the best choice for a large and/or long-term system load query. In a typical case, I run this tool when I think a particular query is having a problem, or if I expect a query to be called frequently enough in the application so that it can cause performance problems.

You can view the expected execution plan by typing the SQL expression into the Query Analyzer and then pressing [ctrl]l]. The execution plan is then displayed in the tab of the results (Results) panel. The execution plan may be difficult to read at the beginning because it is not read from left to right as in English, but is read from right to left.

Scheduled in operation

Here's an example of what you can do with it, and from this example you can see how to use Query Analyzer to view an execution plan. Consider the following query, which returns the location and related area of an employee 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 this query indicate that there are two EmployeeID in the east (eastern) of the first number, as shown in Figure A.

Figure A

Query results

Figure B shows the expected execution generated by the SQL Server from the sample query. Each node in this icon represents the steps taken by the SQL service period to perform the sample query. Note that each node includes an estimated relative consumption and the name of each index used by the SQL Server when the execution plan is displayed.

Figure B