Analyzing a Query
The SQL Server Database Engine can display how it navigates tables and uses indexes to access or process the data for a query or other DML, such as an update. This is a display of an execution plan. To analyze a slow-running query, it is useful to examine the query execution plan to determine what is causing the problem. For more information about how SQL Server creates and uses execution plans, see Single SQL Statement Processing and Execution Plan Caching and Reuse.
In SQL Server 2005, you can display execution plans by using the following methods:
- SQL Server Management Studio
Displays either an estimated graphical execution plan (statements do not execute) or an actual graphical execution plan (on executed statements), which you can save and view in Management Studio. - Transact-SQL SET statement options
When you use the Transact-SQL SET statement options, you can produce estimated and actual execution plans in XML or text. - SQL Server Profiler event classes
You can select SQL Server Profiler event classes to include in traces that produce estimated and actual execution plans in XML or text in the trace results.
When you use one of these methods to display execution plans, the best execution plan used by the Database Engine for individual data manipulation language (DML) and Transact-SQL statements is displayed. The plan reveals compile-time information about stored procedures and called stored procedures that are invoked to an arbitrary number of calling levels. For example, executing a SELECT statement may show that the Database Engine uses a table scan to obtain the data. Execution of the SELECT statement may also show that an index scan will be used if the Database Engine determines that an index scan is a faster method of retrieving the data from the table.
In This Section
| Topic | Description |
|---|---|
Lists and describes common causes for slow-running queries and what you can do to improve query performance. |
|
Displaying Graphical Execution Plans (SQL Server Management Studio) |
Contains information about using SQL Server Management Studio to display execution plans. Also provides a reference describing all icons that are used to graphically display execution plans in Management Studio. |
Displaying Execution Plans by Using the Showplan SET Options (Transact-SQL) |
Contains information about using the Transact-SQL SET statement options to display execution plans in XML format or text. |
Displaying Execution Plans by Using SQL Server Profiler Event Classes |
Contains information about using SQL Server Profiler event classes in traces to display execution plans in XML format or text. |
Contains information about the SHOWPLAN permission, which is new in SQL Server 2005, and about what permissions are required for using the various methods to display execution plans. |
|
Contains information about the Showplan XML schema. |
|
Contains information about which Transact-SQL statements produce Showplan execution plan information. |
|
Contains reference information about all possible logical and physical operators that are displayed in execution plans. Use this reference to read execution plan output. |
See Also
Concepts
Analyzing Queries with SHOWPLAN Results in SQL Server Profiler