Tuning your SQL Query – Generating a Proper Execution Plan

Nhân đang tìm hiểu lại về Index và Execute Plan, 1 số bài viết liên quan đọc để rõ hơn.

Source : http://www.sql-server-performance.com/2016/tuning-sql-query-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.

[Nguyen : Không có Index thì SQL sẽ scan trong table từng dòng từ đầu đến cuối và thường sẽ mất rất nhiều thời gian. Có khi may mắn dòng cần tìm nằm ngay ban đầu khi SQL scan thì nhanh hơn nhưng tất nhiên không nên trong mong vào may rủi ở đây. Do đó nên tạo Index cho column student_id]

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.

[Nguyen : Lúc này SQL sẽ Scan Index thay vì table do không có tìm cụ thể student nào]

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.