By: Ashish Kumar Mehta | Updated: 2010-02-18 | Comments (1) | Related: > 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
- Missing indexes can be a SQL Server performance killer. Be sure to review your query plans and determine if the indexes are appropriate or if they require additional attention.
- The missing index feature, is just one of many nice little features in SQL Server 2008 Management Studio. See what other tips are available with SQL Server 2008 Management Studio.
- Check out these related tips:
About the author
Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2010-02-18
