Understanding SQL Server's SQL query plan

This article through a detailed analysis of an example to explain the use of seek, scan and other operations and effects, for your reference!

Getting Started Guide

Let's use a simple example to help you understand how to read a query plan by issuing the SET SHOWPLAN_TEXT on command, or by setting the same option in SQL query Analyzer's configuration properties.

Note: This example uses the table Pubs.big_sales, the table with pubs. The sales table is exactly the same, except that there are 80000 more lines of records to use as the primary data for the simple explain plan example.

As shown below, this simplest query will scan the entire clustered index if the index exists. Note that the clustered key values are in the physical order and the data is stored in that order. So, if a clustered key value exists, you will probably avoid scanning the entire table. Even if the column you selected is not in a clustered key value, such as Ord_date, the query engine scans with the index and returns the result set.

SELECT *
FROM big_sales
SELECT ord_date
FROM big_sales
StmtText
-------------------------------------------------------------------------
|--ClusteredIndexScan(OBJECT:([pubs].[dbo].[big_sales].[UPKCL_big_sales]))

The query above shows a very different amount of data, so a small result set (ord_date) query runs faster than other queries, simply because there is a large number of underlying I/O. However, these two query plans are actually the same. You can improve performance by using other indexes. For example, there is a nonclustered index on the title_id column:

SELECT title_id
FROM big_sales
StmtText
------------------------------------------------------------------
|--Index Scan(OBJECT:([pubs].[dbo].[big_sales].[ndx_sales_ttlID]))

The preceding query is very small compared to the select * query because all results can be obtained from a nonclustered index. This class of queries is called covering query (overriding the queries) because all result sets are overwritten by a nonclustered index.