assisted index creation

SQL Server offers a lot of information on performance tuning, but it’s not always easy to find. One such area is finding missing indexes. sys.dm_db_missing_index_groups, sys.dm_db_missing_index_details and sys.dm_db_missing_index_group_stats are DMVs that offer details on indexes, the query optimizer would have used if they were available. The first offers information on missing indexes, the second offers us more details about those missing indexes and the last helps us identify the potential impact those indexes could make and helps us filter for meaningful suggestions. In my case, I defined (stat.avg_user_impact * (stat.user_seeks + stat.user_scans)) > 1000 as the minimum threshold, to avoid too much noise.

In my admin database, I set up the following view:

USE [Admin] 

GO

 

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE VIEW [dbo].[suggestedIndex]

AS

SELECT DISTINCT

      'USE [' + db.name + ']' + CHAR(13)+CHAR(10)

    + 'GO' + CHAR(13)+CHAR(10)

    + 'CREATE INDEX [nci_' + OBJECT_NAME(det.OBJECT_ID,det.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(det.equality_columns,''),', ','_'),'[',''),']','')

    +  CASE

       WHEN det.equality_columns IS NOT NULL AND det.inequality_columns IS NOT NULL

       THEN '_'

       ELSE ''

       END + REPLACE(REPLACE(REPLACE(ISNULL(det.inequality_columns,''),', ','_'),'[',''),']','') + ']' + CHAR(13)+CHAR(10)

    + 'ON ' + det.statement + ' (' + ISNULL (det.equality_columns,'')

    +  CASE

       WHEN det.equality_columns IS NOT NULL AND det.inequality_columns IS NOT NULL

       THEN ','

       ELSE ''

       END + ISNULL (det.inequality_columns, '') + ')' + CHAR(13)+CHAR(10)

    +  ISNULL ('INCLUDE (' + det.included_columns + ')' + CHAR(13)+CHAR(10), '')

    +  CHAR(13)+CHAR(10) AS Statement

 FROM master.sys.dm_db_missing_index_groups mi

 JOIN master.sys.dm_db_missing_index_group_stats stat

   ON stat.group_handle = mi.index_group_handle

 JOIN master.sys.dm_db_missing_index_details det

   ON mi.index_handle = det.index_handle

 JOIN master.sys.databases db

   ON det.database_id = db.database_id

WHERE (stat.avg_user_impact * (stat.user_seeks + stat.user_scans)) > 1000

to identify suggested indexes.

I then use the following lines of PowerShell

$ind = Invoke-Sqlcmd -Query "SELECT * FROM suggestedIndex" -ServerInstance $SQLserver -Database Admin

foreach($i in $ind){Send-MailMessage -From $UserName -Subject SuggestedIndex -To "me@mail.com" -Credential $cred -SmtpServer "smtp.mail.com" -UseSsl -Body $i.statement}

to send a create script for each index to myself. Obviously we can’t just blindly create all suggested indexes and have to apply some common sense, but I find it helpful to identify potential areas for improvement.