When I started using SQL Server, I was not using the “Show Execution Plan” properly to analyse the query. I always thought that when I compose a query, it is the best it can be; I never even think of performance on my query and I did not give much importance to looking at the “Execution Plan” on my query. In the initial days of my career, I was happy to retrieve the data, not even knowing how it was returned to me and what sort of “Execution Plan” was used on the query. I presumed SQL Server would handle the query performance. I think it is the nature for software engineers, when starting out, or when first learning new technology, not to take the time to learn everything they really need to know before writing a code. Perhaps this is because of competition and immaturity in the IT field.
Days are starting moving and data has been growing on the database file. On one fine day, my customer was not happy with the performance on the query using the application. He has come to me with an unhappy face stating that he was spending extra time to finish his office work due to the slowness of the queries. Initially, I told the customer to increase the system resources, like increase the hard disk in the machine as a temporary solution. Albeit, hard disk cost is cheap but it is not the permanent solution for the performance degradation in the query. He agreed in half-mind that he will do the required things from his side but he asked me to re-look and fine-tune the queries for a permanent solution instead of suggesting he keep on increasing the system resources. I have to consider his personal opinion because customer satisfaction is important in the IT industry. I have promised to my customer that I will re-look and fine-tune the queries.
HOW?
In the initial days of my career, I know the basic things in MS-SQL Server. To be frank, I was not having any idea on my mind whilst doing promise to my customer. But, I personally felt that I would do something to achieve the task with the help of “GOOGLE” and “BOL”. Thanks to “GOOGLE” and “BOL”.
I was reading MS-SQL books, BOL help and searching on websites. I have heard and crossed the concept of “Show Execution Plan”. We can set this option ON using SQL Query Analyzer. Show Execution Plan is an important graphical tool that enables the developer and DBA to analyse, assist and optimise the query and improve the performance of the query.
Show Execution Plan displays different icons for a different task. I am mainly interested on “Table Scan”, “Index Scan” & “Index Seek” & “Clustered Index Scan” & “Clustered Index Seek” icons in this article. May be I could be writing in my future article on other icons.
As the days and years have moved along like a formula 1 race car, I have decided that it is time for me to fully understand how “Table Scan”, “Index Scan” & “Index Seek” & “Clustered Index Scan” & “ClusteredIndex Seek” icons work.
Now, I am ready to analyse the queries and upgrade the performance on my queries. Before analysing the queries, a few questions have been raised in my mind.
When did MS-SQL Server use “Table Scan”?
When did MS-SQL Server use “Index Scan”?
When did MS-SQL Server use “Index Seek”?
When did MS-SQL Server use “Clustered Index Scan”?
When did MS-SQL Server use “Clustered Index Seek”?
I am mainly concern that in what bases MS-SQL Server would use one of this option to analyse the query. I now have taken that time, and here is what I learned. This information should be useful to new developers and DBAs. I have decided to write this article to share my knowledge to help other to get better idea on these methods.
If you like, you can read this article as is, or in front of a SQL Server, following along with my exercises.
Getting Started
To explain the SQL Server “Table Scan”, “Index Scan” & “Index Seek” & “Clustered Index Scan” & “Clustered Index Seek” using Show Execution Plan; let’s start by creating a table and adding some sample data using the following script. I presume you have got a database to use the following script; otherwise you will need to create one.
Create Table PerformanceIssue ( PRID UniqueIdentifier NOT NULL, PRCode Int NOT NULL, PRDesc Varchar (100) NOT NULL ) ON [PRIMARY]
Table has been created and this table now needs some data; so let’s add 100,000 records in this table using this script. Script will take few minutes to finish executing insert statement for 100,000 records in a table, please bear with the query to get finish.
Declare @Loop Int Declare @PRID UniqueIdentifier Declare @ PRDesc Varchar (100) Set @Loop = 1 Set @ PRDesc = '' WHILE @Loop <= 100000 BEGIN Set @PRID = NewID() Set @PRDesc = ' PerformanceIssue - ' + Convert( Varchar(10),@Loop ) Insert Into PerformanceIssue Values (@PRID, @Loop, @PRDesc) Set @Loop = @Loop + 1 END
Script has been successful completed. Data has been inserted in the table.
Now, let’s view the contents of the table by executing the following command in Query Analyzer:
Select PRID, PRCode, PRDesc From PerformanceIssue GO
I would not display the output of the table due to 100,000 records and it will occupy more pages to display the output. As you would expect, the data we inserted earlier has been displayed in Query Analyzer.
As I told you before about the article, I would be explaining the “Table Scan”, “Index Scan” & “Index Seek” & “Clustered Index Scan” & “Clustered Index Seek”. Which one could be the ideal one that will upgrade the performance in the database system?
SQL Server returns data as it stored and we would like to know that what scan mechanism MS-SQL server has incorporate to retrieve the data. Let’s start with the “Table Scan” topic. This got me to think about the following. When does “Table Scan” happen in SQL Server for a table?
Let’s enable the “Show Execution Plan” in Query Analyzer using Query select “Show Execution Plan” or Alt + Q. Ctr + K is a shortcut method.
Let’s view the graphical execution plan for the query.
Select PRID, PRCode, PRDesc From PerformanceIssue GO
Query has been executed and saw the executions plan for the query. MS-SQL server for the query has used “Table Scan” method. Data have been retrieved using "Table Scan" method. I was asking myself why the “Table Scan” has happen and in what bases MS-SQL server uses "Table Scan" method. It is a simple table that contains 100,000 records. Is it because I have asked MS-SQL server to retrieve 100,000 records from the table? When I was thinking in different angles. Some perplex in my mind. Now, my question how do I prevent "Table Scan" method on my query. I was not having idea what scan mechanism MS-SQL server has used when I was executing the query. I decided to optimise the query. How? I selected two columns [PRID, PRCode] out of three columns in the select statement.
Select PRID, PRCode From PerformanceIssue go
Query has been executed and execution plan is same as my previous query. I still want to optimise the query and decided to have only one [PRID] column in the select statement.
Select PRID From PerformanceIssue GO
Query has been executed and execution plan is same as my first query. Estimated row size was showing difference on the three-execution plan. We are not giving much importance to the Estimated row size attribute. Quickly I decided to retrieve only one record and see what could be the execution plan for the query. I have executed the following query.
Select PRID, PRCode, PRDesc From PerformanceIssue Where PRID = 'D386C151-5F74-4C2A-B527-86FEF9712955' -- PRID GUID value might be differ in your machine GO
Execution of the query was over and execution plan has been displayed.
Query has incorporated “Table Scan” method to display the data. I saw the execution plan and still execution plan uses “Table Scan” method on my query.
Then, I need to think some other alternative solution by spending some sleepless nights to avoid "Table Scan" method. "Sleepless nights", yes, it is common in IT industry. I was talking myself how do I solve this problem. First think was come to my mind was "Index" in a table. Currently, I haven't added an “Index" in PerformanceIssue table. Is it because there is no index in the table? Query has used "Table Scan" method. Is that the case, I decided to create non-clustered index on PRID column in PerformanceIssue table. Is this true adding index will solve my queries using "Table Scan" method? We will discuss those questions on "Index Scan" & “Index Seek” topic. Let's now move on to "Index Scan" & “Index Seek” topic.
Index Scan & Index Seek
Let’s create a non-clustered index on PRID column on my table.
CREATE UNIQUE NONCLUSTERED INDEX UNC_PRID ON PerformanceIssue (PRID) GO
Non-Clustered index has been created successfully. Index has been created. I presume you have knowledge on non-clustered index and how non-clustered index works in MS-SQL Server. This article will not explain on non-clustered index. For more information on use of non-clustered index refer BOL and you can read my article on non-clustered data storage at http://www.sql-server-performance.com/gv_index_data_structures.asp. Let's elaborate how "Index Scan" has been used in MS-SQL Server. I am glad that Index has been created successfully. I presume that query would use "Index Scan" method.
Let's execute the select statement and view the graphical execution plan for the query.
Select PRID, PRCode, PRDesc From PerformanceIssue go
Query has been executed and saw the executions plan for the query. "Table Scan" method has been used on the query. I was really shocked to see the "Table Scan" method used on the query. I was more perplexed and rest less why the “Table Scan” has happen? Why MS-SQL Server has not utilise the non-clustered index on my query. I was asking myself when would "Index Scan" method come to the picture. I decided to optimise the query. I have selected two columns [PRID, PRCode] out of three columns.
Select PRID, PRCode From PerformanceIssue GO
Query has been executed and execution plan is same as my previous query. I still want to optimise the query and decided to have only one [PRID] column in the select statement.
Select PRID From PerformanceIssue GO
Query has been executed and saw the execution plan for the query.
"Index Scan" has been used on the query. I am glad to see the "Index Scan" has been used. My query is using "Index Scan" method and my next question is when would “Index Scan” method happen and how? I have created an index on "PRID" column and selected indexed column [PRID] in the select statement. Query has been executed and MS-SQL server scans all the index pages and uses "Index Scan" method. We have clubbed the indexed and non-indexed columns in the select statement. MS-SQL server was not able recognized and use "Index Scan" method for the previous two queries. MS-SQL Server would use "Index Scan" method only indexed column on the select statement. I do not know how exactly MS-SQL Server does behind the code. After this practical knowledge, understand and execution of the queries, I have come to conclusion that we select only the indexed column in the select statement. MS-SQL server would use "Index Scan" method for the query.
I was refreshing myself how do I incorporate “Index Seek” method. When I was using the word “Seek”, first think would strike to my mind was finding a particular/specific one. For an example, I want to find out the MS-SQL server scholars and experts in the earth. The keyword/filter is "MS-SQL Server" on skillset column. I will filter down the scholars and experts name from knowledge table using the skillset column. My output would be restricted to the particular hunt.
Let’s see how to incorporate the “Index Seek” using our PerformanceIssue table. We have already executed the query with different combination. Execution plan has been display the "Table Scan" and "Index Scan" method. We will see how to get the "Index Seek" in the execution plan using our query. After thinking all the options, I have decided to use WHERE clause in the select statement. I confirmed to use three different select statements using WHERE clause. To find out which select statement MS-SQL Server uses "Index Seek" in the execution plan. I started with the 1st select statement with the WHERE clause.
Select PRID, PRCode, PRDesc From PerformanceIssue Where PRCode = 8 go
Query has been executed and "Table scan" has been used in the execution plan.
I moved on to use the 2nd select statement with the WHERE clause.
Select PRID, PRCode, PRDesc From PerformanceIssue Where PRDesc = ' PerformanceIssue - 8' go
Query has been executed and "Table scan" has been used in the execution plan.
I moved on to use the 3rd select statement with the WHERE clause.
Select PRID, PRCode, PRDesc From PerformanceIssue Where PRID = 'D386C151-5F74-4C2A-B527-86FEF9712955' -- PRID GUID value might be differ in your machine go
Query has been executed and "Index Seek" along with "Bookmark Lookup" has been used in the execution plan. We have understand after execution the three select statement with the WHERE clause. "Index Seek" has been used when we use the indexed column [PRID] in the WHERE clause. We have selected non-indexed column in the select statement. Execution plan has used "Index Seek" and "Bookmark Lookup". "Bookmark Lookup" has been used to find out the non-index column value from the table or Clustered Index. We can ignore the "Bookmark Lookup" in the execution plan by removing non-indexed column from the select statement. Execution plan would be "Index Seek". This does not make meaningful to the select statement to retrieve the PRID value which we already passed in the WHERE clause.
I personally consider "Index Seek" is better than "Index Scan" and "Table Scan" in couple of points to upgrade the performance in the system.
- Index Seek" does not need to scan through "Table" and "Index" page as “Table Scan” and “Index Scan” does.
- Index Seek" will search and retrieve the hunted row [used in WHERE clause] much faster than the "Index Scan" and "Table Scan".
After I finish all the combination, I was explaining to my colleagues. One my colleague asked a genuine question to me. When would MS-SQL server use “Clustered Index Scan” and “Clustered Index Seek”? I was speechless at that point of time. I appreciated him and I decided to find the use of “Clustered Index Scan” and “Clustered Index Seek” topics.
I decided to find the use of “Clustered Index Scan” and “Clustered Index Seek”. I should have “Clustered index” in a table to get the “Clustered Index Scan” and “Clustered Index Seek” in the execution plan. I decided to drop the existing non-clustered index and create a clustered index on PRCode column in PerformanceIssue table. Let's now move on to " Clustered Index Scan" & “Clustered Index Seek” topic.
Clustered Index Scan & Clustered Index Seek
The following script will drop the existing index on PRID column in PerformanceIssue table create a clustered index on PRCode column in PerformanceIssue table.
Drop Index PerformanceIssue.UNC_PRID GO CREATE UNIQUE CLUSTERED INDEX UC_PRCode ON PerformanceIssue( PRCode) go ------------- Clustered index has been created successfully. Index has been created.
I presume you have knowledge on non-clustered index and how non-clustered index works in MS-SQL Server. This article will not explain on clustered index. For more information on use of clustered index refer BOL and you can read my article on clustered data storage at http://www.sql-server-performance.com/gv_index_data_structures.asp. Let's concentrate how “Clustered Index Scan” and “Clustered Index Seek” has been used in MS-SQL Server.
I will execute the select statement to find out the “Clustered Index Scan” in the execution plan. I have executed the following query.
Select PRID, PRCode, PRDesc From PerformanceIssue go
Query has been executed and "Clustered Index Scan" has been used in the execution plan.
We should have a "Clustered index" in a table to get the "Clustered Index Scan" in the execution plan for the select statement. We could have a single column or more than one column in the select statement, execution plan would use "Clustered Index Scan". When do we have "Clustered Index Seek" in the execution plan? How do we get the "Clustered Index Seek" in the execution plan?
We will see how to get the "Clustered Index Seek" in the execution plan using our query. After I was rolling my head, I took decision to use WHERE clause in the select statement. I confirmed to use three different select statements using WHERE clause. To find out which select statement MS-SQL Server uses "Clustered Index Seek" in the execution plan. I started with the 1st select statement with the WHERE clause.
Select PRID, PRCode, PRDesc From PerformanceIssue Where PRDesc = ' PerformanceIssue - 8' go
Query has been executed and "Clustered Index Scan" has been used in the execution plan.
I decided to use the 2nd select statement with the WHERE clause.
Select PRID, PRCode, PRDesc From PerformanceIssue Where PRID = 'D386C151-5F74-4C2A-B527-86FEF9712955' -- PRID GUID value might be differ in your machine go
Query has been executed and "Clustered Index Scan" has been used in the execution plan.
I decided to use the 3rd select statement with the WHERE clause.
Select PRID, PRCode, PRDesc From PerformanceIssue Where PRCode = 8
go
Query has been executed and “Clustered Index Seek” has been used in the execution plan.
Query has been executed and "Clustered Index Seek" has been used in the execution plan. We have understand after execution the three select statement with the WHERE clause. "Clustered Index Seek" has been used when we use the indexed column [ PRCode] in the WHERE clause. We have selected non-indexed column in the select statement. Execution plan has used "Clustered Index Seek". Execution plan will not use "Bookmark Lookup" for the non-indexed columns in the select statement for the clustered index table.
I personally feel "Clustered Index Seek" is better than "Clustered Index Scan" and "Index Seek" in couple of points to upgrade the performance in the system.
- Clustered Index Seek" does not need to scan through all the Clustered index pages.
- Clustered Index Seek" does not create a “Bookmark Lookup” comparing to "Index Seek" for the non-indexed columns in the select statement.
I understand the execution plan and gained practical knowledge on execution plan. I know which scan mechanism will improve the performance in the system and bring back the customer smiling face. Now, I will have couple of beer to have peaceful sleep.
