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.
Clicking on hyperlink would open another windows in SSMS as shown below.
Hope this helps to someone in the world.
As always, comments are welcome.
- Balmukund
