Tuning your SQL Query – Generating a Proper Execution Plan.
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
let’s have a look at the query execution plan :
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.
Now we can take another look at the query execution plan:
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.
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
Please do let us know if there are any suggestions on the
same. Alternatively you can write to me on firstname.lastname@example.org .