Missing Index Feature of SQL Server 2008 Management Studio


By:   |   Updated: 2010-02-18   |   Comments (1)   |   Related: More > Indexing

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 Studio there 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 the Missing 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



Last Updated: 2010-02-18


get scripts

next tip button



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips
Related Resources





Comments For This Article




Thursday, November 10, 2016 - 3:37:43 AM - Akshay Ghorpadre Back To Top

Thanks Ashish..

 

 



download


Recommended Reading

Difference between SQL Server Unique Indexes and Unique Constraints

Building SQL Server Indexes in Ascending vs Descending Order

Script out all SQL Server Indexes in a Database using T-SQL

Creating Indexes with SQL Server Management Studio

How to get index usage information in SQL Server





get free sql tips
agree to terms


Learn more about SQL Server tools