Missing Index Analysis and Creation.

SQL server is intelligent enough to analyze our query before executing them on the server. You might have seen some green color texts (Missing Index Details) pops up in execution plans, when there is index a suggestion from SQL server. SQL server gives hint that, this index could have been used to perform the query better. 

Looks like the above query is doing a clustered index scan on Sales.Customer table. see we had the actual execution plan ON and that's why we are able to get the missing index detail. This missing index has a performance impact of 91.98% on the whole batch. You can right click and go to 'Missing Index Details.. ' to get the whole script of missing index on a new query window.

Now, we got a little idea about missing indexes. Next, how can we query these information from our DB?

You can do a select * on sys.dm_db_missing_index_details DMV to get missing index details but here We will tweak little bit to generate missing index scripts dynamically with some good analytic information, which will help us to decide whether a new index will be really helpful for the table or not?

/*    
[Author]: deviprasad   
[Date of Creation]: 08/01/2013   
[Contact]: deviprasad@expertsworld.in   
*/   

SELECT TOP 100 PERCENT DB_NAME() AS [DB Name]
, OBJECT_NAME(dmvMID.OBJECT_ID,dmvMID.database_id) AS [Table Name]
, '/* ' + '
	[Author]: '+ REPLACE(SUSER_SNAME(), '.', ' ') + '
	[Date of Creation]: ' + CONVERT(varchar(10), GETDATE(), 101) + '
	[Contact]: '+ SUSER_SNAME() + '@expertsworld.in
	*/ ' + CHAR(10) + CHAR(10)

	+'CREATE NONCLUSTERED INDEX [IDX_' + OBJECT_NAME(dmvMID.OBJECT_ID,dmvMID.database_id) + '_'
	+ REPLACE(REPLACE(REPLACE(COALESCE(dmvMID.equality_columns,''),', ','_'),'[',''),']','') +
	CASE
		WHEN (dmvMID.equality_columns IS NOT NULL AND dmvMID.inequality_columns IS NOT NULL) THEN '_'
		ELSE ''
	END
	+ REPLACE(REPLACE(REPLACE(COALESCE(dmvMID.inequality_columns,''),', ','_'),'[',''),']','')
	+ ']' 
	+ ' ON ' + dmvMID.statement
	+ ' (' + COALESCE (dmvMID.equality_columns,'')
	+ CASE 
		WHEN (dmvMID.equality_columns IS NOT NULL AND dmvMID.inequality_columns IS NOT NULL) THEN ','
		ELSE
	'' END
	+ COALESCE (dmvMID.inequality_columns, '')
	+ ')' + CHAR(10)
	+ COALESCE (' INCLUDE (' + dmvMID.included_columns + ')', '') + CHAR(10)
	+ 'WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80)'
	+ ' GO'
	AS [Missing Index SQL Script]
, dmvMIGS.user_seeks as [User Seeks]
, CAST(dmvMIGS.avg_user_impact AS varchar)+ ' %'AS [Estimated Impact]
, dmvMIGS.last_user_seek AS [Last User Seek]
--, dmvMIGS.avg_total_user_cost AS [Estimated Cost on disk]
, (SELECT COUNT(*) from sys.indexes where OBJECT_ID = dmvMID.OBJECT_ID and type_desc = 'NONCLUSTERED') AS [NC Indexes]
, OBJECTPROPERTY(dmvMID.object_id, 'tableHasClustIndex') as [Is Clust Idx]
,(SELECT
CAST (SUM(
 CASE
 WHEN ps.usedpages > ps.pages THEN (ps.usedpages - ps.pages)
 ELSE 0
 END * 8) AS varchar(100)) + ' KB' AS indexsize
 FROM sys.indexes i
 INNER JOIN (
 SELECT
 OBJECT_ID,
 index_id,
 SUM (used_page_count) usedpages,
 SUM (
 CASE
 WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
 ELSE lob_used_page_count + row_overflow_used_page_count
 END
 )pages
 FROM sys.dm_db_partition_stats
 WHERE object_id = dmvMID.OBJECT_ID
 GROUP BY object_id, index_id
 ) ps on i.index_id = ps.index_id
 WHERE i.object_id = dmvMID.OBJECT_ID) as [Total Index Size]

FROM sys.dm_db_missing_index_groups as dmvMIG
INNER JOIN sys.dm_db_missing_index_group_stats dmvMIGS
	ON dmvMIGS.group_handle = dmvMIG.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dmvMID
	ON dmvMIG.index_handle = dmvMID.index_handle
WHERE dmvMID.database_ID = DB_ID()
--AND OBJECT_NAME(dmvMID.OBJECT_ID,dmvMID.database_id) LIKE '%Customer%'
ORDER BY --[NC Indexes] ASC, 
[Estimated Impact] DESC 

[NC Index]: How many non-clustered indexes are already available on the table?

[Is Clust Index]: Is there a clustered index available on the table or not?

[Total Index Size]: Total index size in KB on datafile/disk including clustered index.

(Pic 1.2)

Wow! we got the index script without really coding it!!

The above index script is generated in [Missing Index SQL Script] column. I have gone ahead and created the same non-clustered index on Sales.Customer table by this script. Now lets analyze the index in detail.

Included columns are our select list with proper order. 

Check this out, after a non clustered index on Sales.Customer table.

Wow Index seek!! The same query is performing healthier than previous. We see the index seek on the non clustered index, we just created. That's a good sign however!

Now compare (Pic 1.0) with (1.3). check the subtree cost and operator cost gone down drastically (0.0999411 to 0.010185) That means we got a huge performance improvement.

Notes:

Conclusion:

Non-clustered index plays a very big role when performance is in consideration. BTW, this little script helped my friend NG, to get missing indexes on his DB project. Normally developers do not think about indexes deeply. If you don't know which index needs to be created to tune your DB, then this is the best option for you to start ahead. Once again these indexes are in result of SQL Server's own analysis!!!

To test this, get a backup copy of your production DB and fire this query on that to get a best result.

This same article can be found at my personal blog www.sqlindia.com

(Caution: Do not test this on your production environment directly.)

Reference:

MSDN and MSSQLTIPS