In this article we will continue discussing the various execution plan operators related to clustered indexes and what they do, when do they appear and what happens when they do.
When we look at execution plans and start actually doing query optimizations, each of these operators will provide us with some indicator of how SQL Server is running.
These operators need to be understood fundamentally, from the contextual activity, when they are seen, whether they are good or bad, as it pertains to performance implications.
If an operator occurs, we should be able to determine the action plan that we need to take so that these operators do not arise again or if the do arise we understand why.
It is most critical to understanding that these particular recommendations have to be taken with a pinch of salt but can be used without much of harm to SQL Server.
When we look at execution plans and start actually doing query optimizations, each of these operators will provide us with some indicator of how SQL Server is running.
These operators need to be understood fundamentally, from the contextual activity, when they are seen, whether they are good or bad, as it pertains to performance implications.
If an operator occurs, we should be able to determine the action plan that we need to take so that these operators do not arise again or if the do arise we understand why.
It is most critical to understanding that these particular recommendations have to be taken with a pinch of salt but can be used without much of harm to SQL Server.
Table Scan
- When: Table scans occur when a table without a clustered index is accessed. Hence, there is no specific order in which SQL server goes about storing them, this particular heap is to be actually queries like in table scan.
- Good or bad: For very small tables it does not sometimes make difference. But if it is for a larger number of rows, we will not be happy with the appearance of this operator.
- Action item: Create a clustered index. Generally, a good practice is that if we have only one index on the table, it is better to be a clustered index.
Now, let us see a table scan operator. We will create the following simple table depending on [SalesOrderDetail] table from [AdventureWorks2014] database using the following script:
Then, we will demonstrate something interesting.
First, let us try to select * from our sample table with including actual execution plan and statistics IO.
Here we can notice that we get the result of 121,317 rows with logical reads of 1,497.
Now, let us try to run the same query using a specific range of values:
The interesting thing, alluded to previously, is that when we don't have a clustered index we will end up with table scan which means we will have the same logic reads no matter how big the result we are selecting is. So, it will be much better if we have created a clustered index. This will be further demonstrated in next section.
Clustered index scan
- When: Table with a clustered index is accessed
- The table does not have non-clustered index
- The query cannot use the non-clustered index
- Good or bad: If we have to make a decision whether it is good or bad, it could be bad. Unless a large number of rows, with many columns and rows, are retrieved from that particular table, a Clustered index scan can degrade the performance.
- Action Item: Evaluate clustered index keys.
Clustered index seek
- When: A table with clustered index is being accessed and the B tree structure is able to narrow down, based on our clustered index, to get a limited set of rows from the table.
- Good or bad: This is the ideal condition. It is generally good to see the Clustered Index Seek
- Action item: Evaluate the possibility of using a non clustered index, so that we gain the possibility of eliminating, if required, even the Clustered Index Seek.
For the previously created table 'MySalesOrderDetail', we are going to create a clustered index primary key on [SalesOrderID] and [SalesOrderDetailID].
We can see that the query completes with 12,317 rows, which is same as what we used to get before.
The message tab, ironically, is giving 1,502 logical reads. Now, this is a little bit more when compared to what we used to get with 1,497. The 5 additional pages that we are actually getting are based on the B tree that had to be formed because the intermediate notes and first IAM had to be built and that is exactly what 1,502 is.
If we get to the execution plan here, we can see that unlike the previous time of Table Scan, you are getting Clustered Index Scan. This is a slight improvement that we have seen.
Now, let me get to the specific case where we went about doing value collection. In this particular case same as what we selected before
Here we get a single row and if we get to the message tab we can see it shows three logical reads.
If we move to the execution plan, we can see the Clustered Index Scan which was in the previous case has been transformed into a Clustered Index Seek. hence SQL server was able to narrow down, using the clustered index key. to obtain this particular value based on the where condition, which is in the seek predicate.
Summary
Execution plans are a very important tool for optimizing query performance. Every DBA needs to know all operators that appear in the execution plan and decide whether it is good or bad and what to do if it is the latter.
