未加星标

SQL Server Execution Plan Operators Part 1

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二04 | 时间 2018 | 作者 红领巾 ] 0人收藏点击收藏

In the previous articles of this series, SQL Server Execution Plans overview , SQL Server Execution Plans types and How to Analyze SQL Execution Plan Graphical Components , we discussed the steps that are performed by the SQL Server Relational Engine to generate the Execution Plan of a submitted query and the steps performed by the SQL Server Storage Engine to retrieve the requested data or perform the requested modification operation.

In addition, we clarified the different types and formats of the SQL Server Query Execution Plans that can be used for queries performance troubleshooting purposes. Finally, we discussed the graphical query plan components and how we can analyze it. In this article, we will go through the first set of SQL Query Plans operators.

Before starting with the Query Execution Plan operators, we will create a new simple table and fill it with testing data, using the T-SQL script below:

CREATE TABLE ExPlanOperator ( ID INT IDENTITY (1,1), First_Name VARCHAR(50), Last_name VARCHAR(50), Address VARCHAR(MAX) ) GO INSERT INTO ExPlanOperator VALUES ('AA','BB','CC') GO 1000 INSERT INTO ExPlanOperator VALUES ('DD','EE','FF') GO 1000 SQL Server Table Scan Operator

The previously created ExPlanOperator the table is a heap table, a table in which the data rows are not stored in any particular order within each data page, due to the fact that there is no Clustered index created over that table.

See the article SQL Server table structure overview for more details about the structure of the SQL Server tables.

So that, in order to return the result of any query on that table, the SQL Server Engine will scan all the entire table row by row using the Table Scan operator.

Assume that we run the below T-SQL statement that retrieves all the records from the ExPlanOperator test table after including the Actual Execution Plan of the query to see how it will behave. You will see from the generated SQL Query Execution plan that the SQL Server Engine will scan all the entire table rows, using the Table Scan operator, in order to retrieve the requested data, as shown below:


SQL Server Execution Plan Operators   Part 1

The SQL Server Engine will behave in the same way when we try to get a specific set of records by adding the WHERE clause. Where it will scan all the entire table rows using the Table Scan operator again, as there is no index created on that table to assist with the data retrieval, as shown below:


SQL Server Execution Plan Operators   Part 1

If we create a Non-Clustered index on the ID column on the testing table, using the CREATE INDEX T-SQL statement below:

CREATE INDEX IX_ExPlanOperator_ID ON ExPlanOperator (ID)

Then run the same SELECT statement to retrieve a specific set of IDs. You will see that the SQL Server Engine prefers to scan the entire table, row by row rather than using the created index. There are many reasons for such behavior. For example, the index is not useful, the table contains small number of rows or the query will return most of the table rows, as shown below:


SQL Server Execution Plan Operators   Part 1
SQL Server Clustered Index Scan Operator

When you create a Clustered index on the table, the table will be converted from a Heap table to a Clustered table. A clustered table is a table that has a predefined clustered index on one column or multiple columns of the table that defines the storing order of the rows within the data pages and the order of the pages within the table, based on the clustered index key.

For more information about the Clustered table structure, see SQL Server table structure overview . And for more information about the Clustered index, see Designing effective SQL Server clustered indexes .

Let us drop the Non-Clustered index that we created previously on the ExPlanOperator table and replace it with a Clustered index on the ID column on the ExPlanOperator table, using the T-SQL script below:

DROP INDEX IX_ExPlanOperator_ID on ExPlanOperator CREATE CLUSTERED INDEX IX_ExPlanOperator_ID ON ExPlanOperator (ID)

If we run the same SELECT query that retrieves all rows from our testing table, and include the Actual SQL Query Execution plan of the query, you will see that the Table Scan operator will be replaced by the Clustered Index Scan operator, with the same arrow thickness as the one in the case of the Table Scan, as shown below:


SQL Server Execution Plan Operators   Part 1

From the previous SQL Query Execution plan, the SQL Server Engine decides to use the Clustered Index Scan operator, although it will traverse all the index rows similar to how the Table Scan operator behaves. This is due to the fact that, the Clustered Index can be considered as a sorted replacement for the underlying table that stores all the table data at the lowest level of the index, also known as the leaf, in addition to the index key. If there is no useful Non-Clustered index due to out of date statistics, or the query will return all or most of the table rows, the SQL Server Engine will decide that using the Clustered Index Scan operator to scan all the Clustered index rows is faster than using the keys provided by the index.

SQL Server Clustered Index Seek Operator

If the previous SELECT query is modified to be more efficient by adding a data filtration statement in the WHERE clause, that limits the number of rows returned from the table, the SQL Server Query Optimizer decides to use a very fast way to retrieve the data using the Clustered Index Seek operator, as shown below:


SQL Server Execution Plan Operators   Part 1

Rather than traversing all the table rows, the SQL Server Query Optimizer will locate the appropriate Clustered index and navigate it to retrieve the required rows by providing the SQL Server Storage Engine with the instructions to identify the required rows based on the key values of the selected index. In addition to processing the data quickly to retrieve the needed rows using the Clustered Index Seek operator, no extra steps required to retrieve the rest of the columns, as a full sorted copy of the table is stored in the leaf level nodes of the Clustered index.

SQL Server Non-clustered Index Seek Operator

When the SQL Server Query Optimizer finds that all the data that is requested by the query can be provided by an available index, it will perform a seek against that Non-Clustered index, using the Index Seek operator.

For more information about the Non-Clustered index, see Designing effective SQL Server clustered indexes .

Assume that we have created a Non-Clustered index on the First_Name column of the ExPlanOperator testing table, using the CREATE INDEX T-SQL Command below:

CREATE INDEX IX_ExPlanOperator_FirstName on ExPlanOperator(First_Name) Then run the below SELECT state

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

tags: table,SQL,Server,index,Clustered,will,ExPlanOperator,rows,using
分页:12
转载请注明
本文标题:SQL Server Execution Plan Operators Part 1
本站链接:https://www.codesec.net/view/597041.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(34)