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

  • Article

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