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

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 .




Related Articles :

6 Responses to “Tuning your SQL Query – Generating a Proper Execution Plan.”

  1. Like it.

  2. Hardly any more basic than this…

  3. Very basic info…I guess this could be useful for inexperienced DBA’s and Dev’s…

  4. Are you keeping same column (city) in both indexes? or drop the 1st index (index idx_city)?

  5. Satnam, is it okay to remove idx_city since it has already been included in the second index idx_key_student_first_name? You’ve excluded student_id on idx_key_student_first_name but you didn’t dropped idx_city. Won’t that be redundant? What are the pros and cons of having same column in one or more indexes?

  6. Satnam, you gave a good sample. But We should be very carefull by including columns inside creating a noncluster index. The disc space and the cost of Insert statements will be increase by the number of Indexes and the columns which have been included. It depence on the attitude of your Table. For those, which get lots of inserted data, it is not preffered to use. But in the other hand, which are mostly get read from many sessions, the performance will come to front and those cases we suggest to use…

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |