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.