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

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

Rate This
  • Comments 4
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

Leave a Comment
  • Please add 4 and 2 and type the answer here:
  • Post
  • this is really good post, handy sql command to get the missing indexes

  • 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/

  • 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

  • This is great stuff. one can direct look on what index is required in run time

Page 1 of 1 (4 items)