Did you know? – SQL 2008 Management Studio shows missing indexes for a query in human readable format?

  • [アーティクル]

While working with a customer, we identified that there is a easy way to find missing index of a given query using Management Studio (SQL 2008 onwards). So I thought of sharing along with a demo. Below is the demo script to create objects needed.

 

 set nocount on
go
USE [tempdb]
GO
-- create a table with two columns
CREATE TABLE [dbo].[MyTest](
    [i] [int] IDENTITY(1,1) NOT NULL,
    [j] [char](8000) NULL
) 
GO
-- inserting 10000 rows
INSERT INTO dbo.MyTest (j) VALUES ('A')
GO 10000    -- this is another cool feature to run a batch in loop
-- below is to get the plan without running the query
SET SHOWPLAN_XML ON
GO
SELECT * FROM MyTest WHERE I = 5
GO
SET SHOWPLAN_XML OFF

Once you run above code and look at results in grid mode, you will see a hyperlink as below.

SSMS1

Clicking on hyperlink would open another windows in SSMS as shown below.

SSMS2

Hope this helps to someone in the world.

As always, comments are welcome.

- Balmukund

Comments

  • Anonymous
    August 13, 2009
    this is really good post, handy sql command to get the missing indexes
  • Anonymous
    August 13, 2009
    I know and I love it!In SQL Server 2005 it was not easy to get the same information, but doable. See my blog post about the subject http://www.lybecker.com/blog/2008/12/01/easy-win-index-candidates-on-sql-server-2005/
  • Anonymous
    August 13, 2009
    Hi Andres,Thanks for the comment. You are absolutley correct that this can be done in SQL 2005 also but as my blog says "human readable format" :)Balmukund
  • Anonymous
    November 17, 2009
    This is great stuff. one can direct look on what index is required in run time