Analyse-Missing-Index-Sql-Server-technothirsty

080. Analyse Missing Index Sql Server

Analyse-Missing-Index-Sql-Server-technothirsty

Analyse Missing Index Sql Server- Script 1

/*
[Date of Creation]: 08/20/2017
[Contact]: technothirsty@gmail.com
*/   

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() + '@technothirsty.com
	*/ ' + 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 '%TableName%'
ORDER BY [Estimated Impact] DESC 

 

Analyse Missing Index Sql Server- Script 2

----********** Missing Index Script Start **********---------
SELECT DISTINCT TableName FROM (
SELECT TOP 50
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') 
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns 
IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
) F
----********** Missing Index Script Ends**********---------

I request you to run both scripts in your testing environment and verify and then run to Analyse Missing Index in Sql Server.

 

One comment

  1. Hi there! Someone in my Myspace group shared this site with us so I came to
    give it a look. I’m definitely enjoying the information. I’m book-marking
    and will be tweeting this to my followers!
    Exceptional blog and outstanding design and style.

Leave a Reply