Analyzing a Query (original) (raw)

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 statement, 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 SQL Statement Processing and Execution Plan Caching and Reuse.

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.