Missing Indexes via Extended events

Question

Post reply

Missing Indexes via Extended events

goher2000

SSCertifiable

Points: 5477
More actions
July 4, 2018 at 10:10 am

#357181

Is there a way to identify missing indexes via extended events ?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 1

what makes you think there are missing indexes?

***The first step is always the hardest *******

goher2000 SSCertifiable Points: 5477 More actions · Answer 2

The below query says so, I don't need lecture on indexes/plan cache, what I need to know is if ' is there a way to identify missing indexes via extended events' if yes, then how?

SELECT CAST(SERVERPROPERTY('ServerName') AS [nvarchar](256)) AS [SQLServer]
,db.[database_id] AS [DatabaseID]
,db.[name] AS [DatabaseName]
,id.[object_id] AS [ObjectID]
,id.[statement] AS [FullyQualifiedObjectName]
,id.[equality_columns] AS [EqualityColumns]
,id.[inequality_columns] AS [InEqualityColumns]
,id.[included_columns] AS [IncludedColumns]
,gs.[unique_compiles] AS [UniqueCompiles]
,gs.[user_seeks] AS [UserSeeks]
,gs.[user_scans] AS [UserScans]
,gs.[last_user_seek] AS [LastUserSeekTime]
,gs.[last_user_scan] AS [LastUserScanTime]
,gs.[avg_total_user_cost] AS [AvgTotalUserCost]
,gs.[avg_user_impact] AS [AvgUserImpact]
,gs.[system_seeks] AS [SystemSeeks]
,gs.[system_scans] AS [SystemScans]
,gs.[last_system_seek] AS [LastSystemSeekTime]
,gs.[last_system_scan] AS [LastSystemScanTime]
,gs.[avg_total_system_cost] AS [AvgTotalSystemCost]
,gs.[avg_system_impact] AS [AvgSystemImpact]
,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]
,'CREATE INDEX [Missing_IXNC_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE
   WHEN id.[equality_columns] IS NOT NULL
    AND id.[inequality_columns] IS NOT NULL
    THEN '_'
   ELSE ''
   END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE
   WHEN id.[equality_columns] IS NOT NULL
    AND id.[inequality_columns] IS NOT NULL
    THEN ','
   ELSE ''
   END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex]
,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]
FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)
INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK)
ON gs.[group_handle] = ig.[index_group_handle]
INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK)
ON ig.[index_handle] = id.[index_handle]
INNER JOIN [sys].[databases] db WITH (NOLOCK)
ON db.[database_id] = id.[database_id]
WHERE id.[database_id] > 4 -- Remove this to see for entire instance
ORDER BY [IndexAdvantage] DESC
OPTION (RECOMPILE);

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 3

these are only suggested indexes they may not make any difference at all, are you having issues with performance?

***The first step is always the hardest *******

goher2000 SSCertifiable Points: 5477 More actions · Answer 4

not really, let me rephrase my question, is there a way to find suggested indexes using extended events?

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 5

Are you trying to somehow track a missing index to a query?

You capture warnings: https://sqlperformance.com/2015/10/extended-events/capture-plan-warnings

goher2000 SSCertifiable Points: 5477 More actions · Answer 6

goher2000

SSCertifiable

Points: 5477

July 5, 2018 at 9:36 am

#1996426

Thanks Jones