Buscar Missing Index

Query en SQLServer para saber que indices crear:

  1. SELECT CAST(SERVERPROPERTY('ServerName') AS [nvarchar](256)) AS [SQLServer]
  2. ,db.[database_id] AS [DatabaseID]
  3. ,db.[name] AS [DatabaseName]
  4. ,id.[object_id] AS [ObjectID]
  5. ,id.[statement] AS [FullyQualifiedObjectName]
  6. ,id.[equality_columns] AS [EqualityColumns]
  7. ,id.[inequality_columns] AS [InEqualityColumns]
  8. ,id.[included_columns] AS [IncludedColumns]
  9. ,gs.[unique_compiles] AS [UniqueCompiles]
  10. ,gs.[user_seeks] AS [UserSeeks]
  11. ,gs.[user_scans] AS [UserScans]
  12. ,gs.[last_user_seek] AS [LastUserSeekTime]
  13. ,gs.[last_user_scan] AS [LastUserScanTime]
  14. ,gs.[avg_total_user_cost] AS [AvgTotalUserCost]
  15. ,gs.[avg_user_impact] AS [AvgUserImpact]
  16. ,gs.[system_seeks] AS [SystemSeeks]
  17. ,gs.[system_scans] AS [SystemScans]
  18. ,gs.[last_system_seek] AS [LastSystemSeekTime]
  19. ,gs.[last_system_scan] AS [LastSystemScanTime]
  20. ,gs.[avg_total_system_cost] AS [AvgTotalSystemCost]
  21. ,gs.[avg_system_impact] AS [AvgSystemImpact]
  22. ,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]
  23. ,'CREATE INDEX [Missing_IXNC_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE
  24. WHEN id.[equality_columns] IS NOT NULL
  25. AND id.[inequality_columns] IS NOT NULL
  26. THEN '_'
  27. ELSE ''
  28. END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE
  29. WHEN id.[equality_columns] IS NOT NULL
  30. AND id.[inequality_columns] IS NOT NULL
  31. THEN ','
  32. ELSE ''
  33. END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex]
  34. ,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]
  35. FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)
  36. INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK)
  37. ON gs.[group_handle] = ig.[index_group_handle]
  38. INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK)
  39. ON ig.[index_handle] = id.[index_handle]
  40. INNER JOIN [sys].[DATABASES] db WITH (NOLOCK)
  41. ON db.[database_id] = id.[database_id]
  42. WHERE id.[database_id] > 4 -- Remove this to see for entire instance
  43. ORDER BY [IndexAdvantage] DESC
  44. OPTION (RECOMPILE);
Publicada en sql

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *