Recently one of my developers came to and complained of slow performance on a particular query. During the investigation I noticed that the primary cause was the poor query execution plan. In this article, I will discuss the approach which I used to improve the execution plan.
For demonstration purpose, I will be creating a table named student in the student database as follows:
createtablestudent ( student_idint, student_first_namevarchar(50), student_last_namevarchar(50), cityvarchar(50) )
Let’s insert few set of records into it using the below T-SQL.
declare @i int declare @j int declare @k int declare @l int declare @m int select @i =1 select @j=100000 while (@i <= 25000) begin INSERT INTO student (student_id,student_first_name,student_last_name,City) select @i,'Satnam','Singh','Mumbai' select @i=@i+1 end select @k = MAX(student_id)+1 from student while (@k <= 50000) begin INSERT INTO student (student_id,student_first_name,student_last_name,City) select @k,'James','Anderson','USA' select @k=@k+1 end select @l = MAX(student_id)+1 from student while (@l <= 75000) begin INSERT INTO student (student_id,student_first_name,student_last_name,City) select @l,'John','Henry','UK' select @l=@l+1 end select @m = MAX(student_id)+1 from student while (@m <= 100000) begin INSERT INTO student (student_id,student_first_name,student_last_name,City) select @m,'Brian','Murphy','Toronto' select @m=@m+1 end
Now let’s have a look at the query execution plan :
At this stage, on the Table Scan it is obvious you do not have a Clustered Index to cover the table. Let’s create it on the column named student_id which will be holding a unique set of values in the complete table.
createclusteredindexclu_student_idonstudent(student_id)
Now we can take another look at the query execution plan:
Now you can see that the Table Scan gets converted to a Clustered Index Scan which means that the base table is completely scanned by the Clustered Index.
Now let’s modify the query to have a WHERE clause in it as follows:
select student_id, student_first_name from student where city='USA'
Since we have a WHERE condition on the column named city we should create a NON Clustered index on to improve performance as follows :
create index idx_city on student(city)
Now let’s look at the query execution plan :
Note that it's a Clustered Index Scan. If we have a look at the missing index recommendation as shown in the oval above, the Query optimizer is advising us to Create an Index with an Include column. One thing to keep in mind is that there is no need to have the column student_id in the Included column because it is already a part of the Clustered Index. Therefore the Index Creation logic would be as follows:
create nonclustered index idx_key_student_first_name on student(city) include(student_first_name)
Now let’s take a final a look at the query execution plan :
The query execution plan is now greatly improved - the query optimizer now makes a proper use of the Index to fulfill the query execution plan.
Please do let us know if there are any suggestions on the same. Alternatively you can write to me on singhsatnam84@yahoo.com .
]]>