Missing indexes and Create Index command from the procedure cache

Recently I was working on a performance issue for SQL Server where I needed to identify the missing indexes on the database instance. This was quite easy since I have a T-SQL script to do this which I have already blogged about before. Now the next task was to identify the SQL statements for which these indexes were suggested. Now this was also easy since my friend Jonathan Kehayias [B | T] had already blogged about this. The next ask now was to get the Create Index command for the list of missing indexes received! Well…. this time I ended up with a strike as I didn’t have any options with me. So I got down to modifying Jonathan’s T-SQL script to add to new commands to provide the CREATE INDEX statement in a separate column of the temporary table #MissingIndexInfo which his script creates.

ALTER TABLE #MissingIndexInfo ADD CreateIndexStatement varchar(8000)

UPDATE #MissingIndexInfo
SET CreateIndexStatement = ‘CREATE INDEX <index name>’ + ‘ ON ‘ + statement + ‘ (‘ + ISNULL (equality_columns,”) + CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN ‘,’ ELSE ” END + ISNULL (inequality_columns, ”) + ‘)’ + ISNULL (‘ INCLUDE (‘ + include_columns + ‘)’, ”)


Before you go ahead and start deploying these scripts to your environments, you need to be cognizant of the fact that the procedure cache may or may not have all the cached plans for all the queries that you want to examine. You would want to find out the missing indexes using the query here and compare it with the list that you retrieved using Jonathan’s query with the modification listed above. This sample script is an attempt to provide you with a list of indexes which may prove beneficial for your queries based on the Missing Indexes feature which was introduced in SQL Server 2005 and above. As always, you would still need to test before implementing these indexes onto a production server instance.


2 thoughts on “Missing indexes and Create Index command from the procedure cache

  1. Pingback: XML Plans Saga –New Moon « TroubleshootingSQL

It is always good to hear from you! :)

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s