Inspect those indexes

Here’s a few queries I often use to review the indexes in our SQL Server systems.

Tables should usually have a primary key. Are all of these intentional in your system?

-- Tables with no primary key
SELECT OBJECT_SCHEMA_NAME(t.[object_id]) AS [schema_name],
OBJECT_NAME(t.[object_id]) AS [table_name]
FROM sys.tables t
LEFT JOIN sys.indexes i
ON i.[object_id] = t.[object_id]
AND i.is_primary_key = 1
WHERE i.index_id IS NULL
AND t.[type] = 'U'
AND t.is_ms_shipped = 0;

Ignoring the PK it may be appropriate for some tables in your system to have a unique index defined to constrain your data. This query will identify those tables for review.

-- Table with no unique constraint (ignoring the PK)
SELECT OBJECT_SCHEMA_NAME(t.[object_id]) AS [schema_name],
OBJECT_NAME(t.[object_id]) AS [table_name]
FROM sys.tables t
LEFT JOIN sys.indexes i
ON i.[object_id] = t.[object_id]
AND i.is_primary_key = 0
AND i.is_unique = 1
WHERE i.index_id IS NULL
AND t.[type] = 'U'
AND t.is_ms_shipped = 0;

Has a developer defined a PK but forgotten to define additional indexes? The developers I work with often do. Do yours? Find out now…

SELECT OBJECT_SCHEMA_NAME(t.[object_id]) AS [schema_name],
		OBJECT_NAME(t.[object_id]) AS [table_name],
		SUM(CASE
				WHEN i.index_id IS NOT NULL
					THEN 1
				ELSE 0
			END) AS index_count
FROM sys.tables t
LEFT JOIN sys.indexes i 
	ON i.[object_id] = t.[object_id]
	AND i.is_primary_key <> 1 -- Ignore primary keys
WHERE t.[type] = 'U'
AND t.is_ms_shipped = 0
GROUP BY OBJECT_SCHEMA_NAME(t.[object_id]),
		 OBJECT_NAME(t.[object_id])
HAVING SUM(CASE
				WHEN i.index_id IS NOT NULL
					THEN 1
				ELSE 0
			END) = 0
ORDER BY [schema_name],
		 table_name;

Here’s a few index properties that may not be a good idea. Check out what they mean in the sys.indexes

-- Potentially "bad" index properties set. Are these justified?
SELECT OBJECT_SCHEMA_NAME(t.[object_id]) AS [schema_name],
OBJECT_NAME(t.[object_id]) AS [table_name],
i.name AS index_name,
i.[ignore_dup_key],
i.is_disabled,
i.is_hypothetical,
i.is_disabled
FROM sys.tables t
INNER JOIN sys.indexes i
ON i.[object_id] = t.[object_id]
WHERE (i.[ignore_dup_key] = 1
OR i.is_disabled = 1
OR i.is_hypothetical = 1
OR i.[allow_row_locks] = 0
OR i.[allow_page_locks] = 0);

Leave a Reply