Chapter 36. Query Analysis

by Ray Rankins

IN THIS CHAPTER

• Displaying Execution Plans in Query Analyzer

• Query Analyzer Server Trace

• Query Analyzer Client Statistics

• SHOWPLAN_ALL and SHOWPLAN_TEXT

• Statistics

• Query Analysis with SQL Profiler

SQL Server's cost-based query optimizer typically does a good job of determining the best query plan to process a query. At times, however, you might be a little bit skeptical about the plan that the optimizer is generating or want to understand why it is choosing a specific plan. At the least, you will want to know the specifics about the query plans that the optimizer is generating, such as the following:

• Is the optimizer using the indexes that you have defined, or is it performing table or index scans ?

• Are worktables being used to process the query?

• What join strategy is being applied?

• What join order is the optimizer using?

• What actual statistics and cost estimates is the optimizer using to make its decisions?

• How do the optimizer's estimates compare to actual I/O costs?

Fortunately, SQL Server provides some tools to help you answer these questions. The primary tool is Query Analyzer. Query Analyzer provides a number of features for monitoring the estimated execution plan as well as viewing the actual runtime statistics for your queries. The following features will be looked at in this chapter:

• Displaying the Graphical Execution Plan

• Displaying the Server Trace

• Displaying client statistics

• Managing indexes and statistics

Although Query Analyzer is a powerful and useful tool for query analysis, SQL Server still provides some text-based query analysis utilities as well. These tools are also described in this chapter, along with tips on how to use them most effectively.

NOTE

Note that the examples presented in this chapter use the bigpubs2000 database. The pubs and Northwind databases provided with SQL Server generally do not contain enough data to demonstrate many interesting query plans. A copy of the bigpubs2000 database is available on the CD included with this book. Instructions on how to install the database are presented in the Introduction.