Analyze the missing index suggestions provided by SQL Server

 

Test the index definition in the table based on SQL Server 2008 AdventureWorks2008, TablePerson. Person:

 

Create nonclusteredindex [IX_Person_LastName_FirstName_MiddleName] ON [Person]. [Person]

(

[LastName] ASC,

[FirstName] ASC,

[MiddleName] ASC

) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,

 

DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

 

GO

 

I want to query the LastName according to FirstName, and check the execution plan after execution:

 

 

650) this. width = 650; "title =" 1.png" style = "float: none;" src = "http://img1.51cto.com/attachment/201312/203828125.png" alt = "203828125.png"/>

 

You can see that SQL Server executes Index Scan, although SQL Server does not have an Index starting with FirstName. When I first learned about SQL Server, I thought that the index can be used only when the filter field is the first field of the index. As a matter of fact, we can see from this example that this understanding is incorrect. Because the LastName Index already exists in the table, the SQL Server engine will know that the Index contains the FirstName field, and the number of Page pages of the Index is much less than the number of pages in the whole table, therefore, using Index Scan is the fastest method, so Index Scan is still used.

 

Return to the SQL Server Missing Index script:

 

USE [AdventureWorks2008]

GO

CREATENONCLUSTEREDINDEX [ix1]

ON [Person]. [Person] ([FirstName])

 

650) this. width = 650; "title =" 2.png" style = "float: none;" src = "http://www.bkjia.com/uploads/allimg/131229/220T0NM-1.png" alt = "203831563.png"/>

 

You can see that SQL Server uses Index Scan + Key Lookup, and the Cost of Key Lookup accounts for 87%. To solve the Key Lookup problem, we can use Include Index to Include this field in the Index created above. Code after index modification:

 

USE [AdventureWorks2008]

GO

CREATENONCLUSTEREDINDEX [ix_firstname]

ON [Person]. [Person] ([FirstName])

Include (LastName)

 

Run the command again and you will see that SQL Server has selected Index Seek:

 

650) this. width = 650; "title =" 3.png" style = "float: none;" src = "http://www.bkjia.com/uploads/allimg/131229/220T03544-2.png" alt = "203833178.png"/>

 

This time the performance is better than the previous one. You can use the following script to prove the performance:

 

Setstatisticsioon

Go

Setstatisticstimeon

Go

Select LastName from Person. Personwhere FirstName = 'ken'

Setstatisticsiooff

Go

Setstatisticstimeoff

Go

Therefore, it is recommended that you use the Missing Index provided by SQL Server after analysis. After creating an Index, pay attention to the Index usage, such as the ratio of Seek/Scan/Lookup/Update, and adjust the data accordingly. In the case of many updates and few queries, creating multiple indexes may cause a burden.

 

 

 

 

This article is from the "follow SQL Server Technology" blog, please be sure to keep this source http://lzf328.blog.51cto.com/1196996/1344845