Finding missing indexes which SQL Server recommends for T-SQL performance improvement

Recently, I was working on quite a lot on SQL Server development. I have been developing stored procedures, T-SQL queries and what not. While working with huge amount of data and bigger queries often developers find themselves stuck when queries are not running as fast you would really expect it to. Often developers would receive several support calls when these SQL objects are deployed to UAT or Production environment and your application using these objects is responding very slow.

I was in similar situation with my recent development. I had to work with numerous amount of data rows and several SQL Server objects. Some of my queries where requesting hell lot of data from our OLTP database which were performing very slow. With SQL Server 2005 and above you can see graphically the executing plan which query optimizer uses. The most common way to show execution plan is to either click on "Display Estimate Execution Plan" button from the toolbar or you can toggle "Include Execution Plan" button then run the query.

However, same thing can also be acheived by using following T-SQL set statements. Lets create two tables without any indexes.

IF(NOT EXISTS( SELECT * FROM sys.tables WHERE object_id = OBJECT_ID('MasterTable') ))
CREATE TABLE MasterTable
(
MasterId INT IDENTITY(1, 1),
[Description] VARCHAR(MAX)
)
GO

IF(NOT EXISTS( SELECT * FROM sys.tables WHERE object_id = OBJECT_ID('DetailsTable') ))
CREATE TABLE DetailsTable
(
SubId INT IDENTITY(1, 1),
MasterId INT,
)
GO

INSERT INTO MasterTable ([Description]) VALUES ('Sample 1'), ('Sample 2'), ('Sample 3'), ('Sample 4')
GO

DECLARE @i INT = 1
WHILE (@i <= 10000) 
BEGIN   
INSERT INTO DetailsTable(MasterId) VALUES (1), (1), (2), (2), (2), (2), (3), (3), (4), (4)    
SET @i = @i + 1 
END 
GO 

Now run following statement

SET SHOWPLAN_XML ON
GO

This causes SQL Server not to execute T-SQL statements. Instead, SQL Server returns detailed information about how the statements are going to be executed in the form of a well-defined XML document. Then run the following query

SELECT * FROM DetailsTable
INNER JOIN MasterTable ON DetailsTable.MasterId = MasterTable.MasterId
WHERE SubId IN (1, 2, 3, 4)
GO

This will not show the query result but rather XML in the result pane. Click the XML to view execution plan.

Right-click on the text where it says "Missing Index...". Basically SQL Server produces actual T-SQL for you to create missing index for improving query performance. It also states by how many percentage query performance will be improved. In this case by 82.2327%. When you click on Missing Index Details... You will see following script produced for you to run.

/*
Missing Index Details from ExecutionPlan2.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 82.2327%.
*/


USE [YourDatabase]
GO
CREATE NONCLUSTERED INDEX []
ON [dbo].[DetailsTable] ([SubId])
INCLUDE ([MasterId])
GO

I found this very useful in my day to day task to find out those missing indexes that could really improve my query performance.