Understanding the SQL query plan of SQL Server (1)

Getting started

Let's take a simple example to help you understand how to read the query plan. You can run the SET SHOWPLAN_TEXT On command, you can also set the same options in the configuration attributes of SQL Query Analyzer to get the Query plan.

Note:: In this example, the pubs. big_sales table is used. This table is exactly the same as the pubs. sales table, except for the record with 80000 rows, which is used as the main data in the simple explain plan example.

As shown below, this simplest query will scan the entire clustered index if it exists. Note that the clustered key value is in the physical order and data is stored in this order. Therefore, if the clustered key value exists, you may avoid scanning the entire table. Even if the column you selected is not in the clustered key value, for example, ord_date, the query engine uses an index to scan and return the result set.

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

The above query shows that the returned data volume is very different, so the query of small result set (ord_date) runs faster than other queries, because there are a large number of underlying I/O. However, the two query plans are actually the same. You can use other indexes to improve performance. For example, a non-clustered index exists in the title_id column:

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

The execution time of the preceding query is very small compared with the SELECT * query, because all results can be obtained from non-clustered indexes. This type of query is called covering query overwrite query) because all result sets are overwritten by a non-clustered index.

SEEK and SCAN

The first thing is that you need to differentiate the SEEK and SCAN operations in the query plan.

Note:: A simple but useful rule is that SEEK operations are efficient, and SCAN operations are not very efficient even if they are not very poor. The SEEK operation is direct, or at least fast, and the SCAN operation needs to read the entire object table, clustered index or non-clustered index ). Therefore, SCAN usually consumes more resources than SEEK. If your query plan is only for scan operations, you should consider adjusting your query.

The where clause can significantly differ in query performance, as shown below:

Select * From big_sales Where stor_id=’6380’ StmtText -----------------------------------------------------------------------------|--Clustered Index Seek(OBJECT: ([pubs].[dbo].[big_sales].[UPKCL_big_sales])), SEEK: ([big_sales].[stor_id]={@1} ORDERED FORWARD)

The preceding query performs the SEEK operation on the clustered index instead of the SCAN operation. This SHOWPLAN accurately describes the SEEK operation based on stor_id and the results are sorted in the order stored in the index. Because SQL Server supports the same performance of index FORWARD and BACKWARD scrolling, You can see ORDERED FORWARD or ORDERED BACKWARD in the query plan. This only tells you how to read the table or index. You can even use the ASC and DESC keywords in the order by clause to perform these operations. The query plan returned by the range query is similar to the query plan directly queried previously. The following two range queries provide some information:

Select * From big_sales Where stor_id>=’7131’ StmtText ------------------------------------------------------------------------------|-Clustered Index Seek(OBJECT: ([pubs].[dbo].[big_sales].[UPKCL_big_sales] ), SEEK: ([big_sales].[stor_id]>=’7131’) ORDER FORWARD

The preceding query looks like the previous example. Except for the SEEK predicate, it is a bit different.

Select * From big_sales Where stor_id between ‘7066’ and ‘7131’ StmtText ------------------------------------------------------------------------------|-Clustered Index Seek(OBJECT: ([pubs].[dbo].[big_sales].[UPKCL_big_sales] ), SEEK:([big_sales].[stor_id]>=’7066’ and ([big_sales].[stor_id]<=’7131’) ORDER FORWARD)

This looks the same. Only the search predicates have changed. Because the search is very fast, this query is quite good.

SEEK and SCAN can also contain Where predicates. In this case, This predicate tells you which records the Where clause filters out from the result set. Because it is executed as a component of SEEK or SCAN, the Where clause usually does not damage or improve the performance of this operation. The Where clause will help the query optimizer find indexes that may have the best performance.

An important part of query optimization is to determine whether to perform the SEEK operation on an index. If so, an index with the best performance is found. In most cases, the query engine is able to find existing indexes. However, there are currently three common indexing problems:

◆ Database designers, usually application developers, do not create any indexes in the table.
◆ Database designers generally cannot guess the common query or transaction types, so the index or primary key built on the table is often inefficient.
◆ When an index table is created, even if the Database Designer guesses more accurately, the transaction load will change over time, making these indexes less efficient.

If you see a large number of scans instead of SEEK in your query plan, you should evaluate your index again. For example, look at the following query:

Select ord_num From sales Where ord_date IS NOT NUL And ord_date>’Jan 01,2002 12:00:00 AM’ StemtText ----------------------------------------------------------------------------------|-- Clustered Index Scan(OBJECT: ([pubs].[dbo].[sales].[UPKCL_sales] ), WHERE : ([sales].[ord_date]>’Jan 1,2002 12:00:00 AM ’))

Now this query performs the seek index operation on the newly created sales_ord_date INDEX.

Article content]

Page 1: Getting Started Guide

Page 2: Describes the branch steps by comparing connections and subqueries

Page 3: Three join policies