Category Archives: Indexing

Query to Identify Missing Indexes


The following are the missing index DMVs ( From SQL Server 2005 BOL)

sys.dm_db_missing_index_group_stats = Returns summary information about missing index groups, for example, the performance improvements that could be gained by implementing a specific group of missing indexes.

sys.dm_db_missing_index_groups = Returns information about a specific group of missing indexes, such as the group identifier and the identifiers of all missing indexes that are contained in that group.

sys.dm_db_missing_index_details = Returns detailed information about a missing index; for example, it returns the name and identifier of the table where the index is missing, and the columns and column types that should make up the missing index.

sys.dm_db_missing_index_columns = Returns information about the database table columns that are missing an index.

——————————————————————————————————————————————–

SELECT
statement AS [database.scheme.table],
column_id , column_name, column_usage,
migs.user_seeks, migs.user_scans,
migs.last_user_seek, migs.avg_total_user_cost,
migs.avg_user_impact
FROM sys.dm_db_missing_index_details AS mid
CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON mig.index_handle = mid.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats  AS migs
ON mig.index_group_handle=migs.group_handle
ORDER BY mig.index_group_handle, mig.index_handle, column_id
GO

===============================================================================

/* ——————————————————————
— Title: FindMissingIndexes
— Description: This query returns indexes that SQL Server 2005
— (and higher) thinks are missing since the last restart. The
— “Impact” column is relative to the time of last restart and how
— bad SQL Server needs the index. 10 million+ is high.
— Changes: Updated to expose full table name. This makes it easier
— to identify which database needs an index. Modified the
— CreateIndexStatement to use the full table path and include the
— equality/inequality columns for easier identifcation.
—————————————————————— */

SELECT 
 [Impact] = (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans), 
 [Table] = [statement],
 [CreateIndexStatement] = ‘CREATE NONCLUSTERED INDEX ix_’
  + sys.objects.name COLLATE DATABASE_DEFAULT
  + ‘_’
  + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,”)+ISNULL(mid.inequality_columns,”), ‘[‘, ”), ‘]’,”), ‘, ‘,’_’)
  + ‘ ON ‘
  + [statement]
  + ‘ ( ‘ + IsNull(mid.equality_columns, ”)
  + CASE WHEN mid.inequality_columns IS NULL THEN ” ELSE
   CASE WHEN mid.equality_columns IS NULL THEN ” ELSE ‘,’ END
  + mid.inequality_columns END + ‘ ) ‘
  + CASE WHEN mid.included_columns IS NULL THEN ” ELSE ‘INCLUDE (‘ + mid.included_columns + ‘)’ END
  + ‘;’,
 mid.equality_columns,
 mid.inequality_columns,
 mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
 INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
 INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
 INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE (migs.group_handle IN
  (SELECT TOP (500) group_handle
  FROM sys.dm_db_missing_index_group_stats WITH (nolock)
  ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC)) 
 AND OBJECTPROPERTY(sys.objects.OBJECT_ID, ‘isusertable’) = 1
ORDER BY [Impact] DESC , [CreateIndexStatement] DESC

Advertisements

List all Indexes For SQL SERVER Database


SELECT

            so.name AS TableName

            , si.name AS IndexName

            , si.type_desc AS IndexType

FROM

            sys.indexes si

            JOIN sys.objects so ON si.[object_id] = so.[object_id]

WHERE

            so.type = ‘U’    –Only get indexes for User Created Tables

            AND si.name IS NOT NULL

ORDER BY

            so.name, si.type

%d bloggers like this: