By Richie Lee

Disclaimer: I’m not saying that these three things are the only things you need to know about missing index hints in Execution Plans, I’m sure whole chapters in SQL Books can be dedicated to the topic! And I’m not advocating the use of creating new indexes indiscriminately to a database.

Hello!

Missing index hints in query plans are a potentially quick and fantastic way of speeding up a slow running query. However, there are a few things that are vital before adding a new index recommended via the Missing Index Hint. Let’s setup a test database to give some working examples.


CREATE DATABASE [IX]
GO
ALTER DATABASE [IX] SET RECOVERY SIMPLE
GO
USE IX
GO
CREATE TABLE Me_Heap
(
Me_Heap_ID BIGINT IDENTITY (1,1),
FK_Id_BIG INT,
DateChange DATETIME2 DEFAULT ( CURRENT_TIMESTAMP )NOT NULL ,
DataCol NCHAR (8) DEFAULT ( 'IX' )
)
GO
BEGIN TRANSACTION
DECLARE @i INT = 1
WHILE @i <= 30000
BEGIN
INSERT INTO Me_Heap
( FK_Id_BIG )
VALUES ( @i % 10 )
SET @i = @i + 1
END
COMMIT TRANSACTION
CHECKPOINT
GO 40 

CREATE TABLE Me_Heap_2
(
Me_Heap_2_ID INT IDENTITY (1,1),
A_N_Other_Val INT,
DateChange DATETIME2 DEFAULT ( CURRENT_TIMESTAMP )NOT NULL ,
DataCol NCHAR (8) DEFAULT ( 'IX' )
)

BEGIN TRANSACTION
DECLARE @i INT = 1
WHILE @i <= 30
BEGIN
INSERT INTO Me_Heap_2
( A_N_Other_Val )
VALUES ( @i % 10 )
SET @i = @i + 1
END
COMMIT TRANSACTION
CHECKPOINT
GO

You will notice that there are no indexes, and this is intentional.

Run this query, including the actual execution plan. You can include the actual execution plan by using the hotkey CTRL+M, or via the ribbon (see image below)

When the script has run switch over to the execution plan.

includeactualexecutionplan_png

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

SET STATISTICS IO ON
SET STATISTICS TIME ON

select H1.Me_Heap_ID, H2.DataCol from Me_Heap H1
inner join Me_Heap_2 H2 on H2.Me_Heap_2_ID = H1.FK_Id_BIG
where H1.Me_Heap_ID IN (1,6,9)
and H2.DateChange >= '2015-10-08 16:32:14.0970000'

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

OK, the query optimiser has found a missing index that will speed the query up if we create it. But look closer, and you’ll notice that it is asking for a non clustered index to be created on the column that clearly ought to be the clustered primary key!

non_ci_png


(3 row(s) affected)
Table 'Me_Heap'. Scan count 5, logical reads 6705, physical reads 0, read-ahead reads 6700, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Me_Heap_2'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
CPU time = 203 ms,elapsed time = 1406 ms.

 SQL Server Execution Times:
CPU time = 0 ms,elapsed time = 0 ms.

Before creating the non-clustered index, create the primary key and run the query again.

ALTER TABLE [dbo].[Me_Heap]
ADD CONSTRAINT PK_MeHeap_Me_Heap_ID PRIMARY KEY CLUSTERED (Me_Heap_ID);
GO

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

SET STATISTICS IO ON
SET STATISTICS TIME ON

select H1.Me_Heap_ID, H2.DataCol from Me_Heap H1
inner join Me_Heap_2 H2 on H2.Me_Heap_2_ID = H1.FK_Id_BIG
where H1.Me_Heap_ID IN (1,6,9)
and H2.DateChange >= '2015-10-08 16:32:14.0970000'

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

no_missing_index_png


(3 row(s) affected)
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Me_Heap_2'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Me_Heap'. Scan count 3, logical reads 9, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
 CPU time = 0 ms,  elapsed time = 112 ms.

 SQL Server Execution Times:
 CPU time = 0 ms,  elapsed time = 0 ms.

Now the query will run a lot faster than before, with no logical reads, and looking at the actual execution plan there’s no more missing index hint. So this is the first thing you need to know:

1. Regardless of whether the table as a clustered index or not, the execution plan will only recommend non-clustered indexes.

This doesn’t mean that the first index hint you come across you need to create it as a clustered index. What this really means is that unless you have good reason not to, you need to create a non-clustered index on the table. It’s interesting to note that the non clustered index returns the same page read statistics as the clustered index (create the non clustered index on the heap and try it yourself.)

Drop the primary key, and run the query below. Don’t forget to get the actual execution plan for the query only.


ALTER TABLE [dbo].[Me_Heap] DROP CONSTRAINT [PK_MeHeap_Me_Heap_ID]
GO

select H1.Me_Heap_ID, H2.DataCol from Me_Heap H1
inner join Me_Heap_2 H2 on H2.Me_Heap_2_ID = H1.FK_Id_BIG
where H1.Me_Heap_ID IN (1,6,9)
and  H2.Me_Heap_2_ID < 2345 and H2.A_N_Other_Val > 13
and H2.DateChange >= '2015-10-08 16:32:14.0970000'

Switch over to the execution plan and you’ll see a missing index again. But let’s dig a little deeper: right click over the missing index and select “show execution plan XML”. Look for the MissingIndexes pair and you’ll be surprised:

showexecutionplan_png

missing_indexes_png

2. The Query Optimiser may find more than one missing index hint, but the UI will only return one index, and it may not be the most performant index.

Creating the clustered index mentioned previously will remove all missing index hints. So this emphasizes the need for clustered index.

But let’s say that you do decide to create the non clustered index that is returned in the execution plan. This is a simple enough thing to do, just right click, and select “Missing Index Details” and you get the index commented out, ready for review.

missingindexdetails_png


/*
Missing Index Details from SQLQuery3.sql - (local).IX (WHITESTUFF\richard.lee (52))
The Query Processor estimates that implementing the following index could improve the query cost by 86.3803%.
*/

/*
USE [IX]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Me_Heap] ([Me_Heap_ID],[FK_Id_BIG])

GO
*/

So, we come to the last and possibly most important point:

3. Please, for the sake of all past, present and future DBA’s, rename the index to something more pertinent than <Name of Missing Index, sysname,>

You may think this is obvious that it is almost insulting that I point out such a trivial matter, but you’d be surprised just how many indexes I have seen on tables with that name. It undermines any trust between the people working on the database, both devs and DBA’s, and just generally looks awful. Define and communicate all users a naming convention for indexes. Something like prefixing, eg non clustered unique indexes as UX, non unique as IX, and include the table name and the columns.

Bonus point: as useful as the missing index hints are, the ordering of the columns is not necessarily the optimal ordering,so check the output list of the table to verify the ordering is optimal.The examples above are trivial, but on wider tables this may be worth testing.