RSS

Missing Index Feature of SQL Server 2008 Management Studio

20 Apr

By: 

Problem
While looking through the new features and improvements in SQL Server 2008 Management Studio (SSMS) we found a potentially interesting one called Missing Index Hints. Database Developers and Administrators can use Missing Index Hints feature to quickly identify the columns on which adding an index can help running the query faster. Can you give us a detailed explanation of how we go about using Missing Index Hints feature?

Solution
Identifying missing indexes is always challenging for a Database Administrator. In SQL Server 2008 Management Studiothere is an excellent feature to identify missing indexes for a particular query when the T-SQL code is executed within the Query window. In order to use this feature you need to click CTRL + M or select “Include Actual Execution Plan” option from the Query Menu of SQL Server 2008 Management Studio before executing the actual code.

Sample T-SQL Query

Let us go through an example by running the below mentioned T-SQL code in SQL Server 2008 Management Studio once “Include Actual Execution Plan” option is selected. The below T-SQL code needs to be run against Sales.Store table of the AdventureWorks database.

USE AdventureWorks
GO
SELECT CustomerID, Name, SalesPersonID, ModifiedDate
FROM Sales.Store
WHERE (Name=’Bike World’ AND ModifiedDate > ‘2004-10-01’)
GO

Once the above T-SQL code has executed successfully, you need to navigate to Execution Plan tab as shown in the below snippet. SQL Server 2008 Management Studio will display information related to all the missing indexes in the Execution Plan tab for that particular T-SQL code. The best part of this feature is that you can even connect to an SQL Server 2005 instance from SQL Server 2008 Management Studio and get the information with respect to missing indexes. The information is shown from the data which is collected by the Dynamic Management Views since the last time the SQL Server 2008 or SQL Server 2005 instance has been restated.

You can move the mouse pointer on top of Missing Index text and it will display the T-SQL code which is required to create the missing index as suggested by SQL Server 2008 Management Studio.

A DBA can generate the T-SQL code to create the missing index by right clicking Missing Index text and then select theMissing Index Details… option from the drop down list.

The T-SQL code below will be generated by the SQL Server 2008 Management Studio when the Missing Index Details… option is selected.

/*
Missing Index Details from MissingIndexesHint.sql – LOCALHOST\SQL2008.AdventureWorks (sa (54))
The Query Processor estimates that implementing the following index could improve the query cost by 95.0908%.
*/
/*
USE [AdventureWorks]
GO

CREATE NONCLUSTERED INDEX []
ON [Sales].[Store] ([Name],[ModifiedDate])

GO

*/

Here are a few key notes from the SQL Server 2008 Management Studio script:

  • The associated cost benefit by adding the index is included.  Be sure to review the query plan to validate the query is using the index and test the query with the new index as a portion of your change management process.
  • Please note that the script does not include an index name, so this will need to be created based on your naming standards.

Below is the final script that can be used in your change management process:

USE [AdventureWorks]
GO
CREATE NONCLUSTERED INDEX [IX_Store_Name_Modified_Date]
ON [Sales].[Store] ([Name],[ModifiedDate])

GO

Next Steps

Referenced by: http://www.mssqltips.com/sqlservertip/1945/missing-index-feature-of-sql-server-2008-management-studio/

Advertisements
 
Leave a comment

Posted by on April 20, 2012 in SQL Server

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: