Поиск перекрывающихся (лишних) индексов в SQL Server 2005+

добавлено: 07 мар 12
понравилось:0
просмотров: 3122
комментов: 1

теги:

Автор: Andraptor

В некоторых приложениях из-за несогласованности работы программистов возникают ситуации, когда создаются индексы в которых нет необходимости. Т.е. запросы могут использовать уже существующие индексы. Из-за этого возникают дополнительные издержки на их обслуживание. Таким образом, если поля индекса перекрываются более широким индексом в том же порядке следования полей начиная с первого поля, то этот индекс считается лишним, так как запросы могут использовать более широкий индекс.
Данный сценарий как раз ищет эти перекрывающиеся (лишние) индексы.
Для поиска всех индексов в базе данных я позаимствовал запрос из блога http://www.jjcoder.com/2011/12/how-to-get-all-table-indexes-with.html

Работоспособность тестировалась на SQL Server 2005. Но на более поздних версиях, скорее всего, тоже будет работать.

SET NOCOUNT ON
DECLARE @SrchInData bit
SELECT @SrchInData = 0 /*Включает поиск лишних индексов в определениях модулей и процедурном кэше (0-выкл, 1-вкл),
                         чтобы понять,  возможно эти индексы указаны непосредственно в коде. 
                         Скрипт с включенным параметром будет работать несколько минут.*/
--===================================================================================================================
/*Погорелов А.А.
  Поиск перекрывающихся(лишних) индексов.
  Если поля индекса перекрываются более широким индексом в том же порядке следования полей начиная с первого поля, то 
  этот индекс считается лишним, так как запросы могут использовать более широкий индекс.*/
IF object_id('tempdb..#tmp_indx') is not null
   DROP TABLE #tmp_indx

--Записываем во временную таблицу список всех индексов в базе данных
SELECT t.name AS table_name 
   ,i.name AS index_name
   ,CASE i.is_unique 
		WHEN 1 THEN 'UNIQUE ' 
		ELSE '' 
	END + i.type_desc AS index_type
   ,CASE 
		WHEN is_primary_key = 1 
		THEN 'PRIMARY KEY' 
		WHEN is_unique_constraint = 1 
		THEN 'UNIQUE CONSTRAINT' 
		ELSE '' 
	END AS based_constraint
   ,c.key_columns
   ,isnull(ic.included_columns, '') AS included_columns
   ,sum((ps.reserved_page_count+0.0)*8/1024) AS [IndexSize_MB]
INTO #tmp_indx
FROM sys.tables t
inner join sys.indexes i 
ON i.object_id = t.object_id
inner join sys.dm_db_partition_stats AS ps
ON i.object_id = ps.object_id AND i.index_id = ps.index_id
cross apply
(SELECT stuff( (SELECT ', ' + c.name + CASE ic.is_descending_key 
										WHEN 1 THEN '(-)' 
										ELSE '' END 
				FROM sys.index_columns ic
				inner join sys.columns c 
				ON c.object_id = t.object_id
				and c.column_id = ic.column_id
				WHERE
					i.index_id = ic.index_id
					and i.object_id = ic.object_id
					and ic.is_included_column = 0
				ORDER BY ic.key_ordinal
				FOR XML PATH('')
			)
		 , 1, 2, '') AS key_columns
) c
CROSS APPLY
(SELECT stuff( (SELECT ', ' + c.name
				FROM sys.index_columns ic
				inner join sys.columns c
				ON c.object_id = t.object_id
				and c.column_id = ic.column_id
				WHERE
					i.index_id = ic.index_id
					and i.object_id = ic.object_id
					and ic.is_included_column = 1
				FOR XML PATH('')
			)
		 , 1, 2, '') AS included_columns   
) ic
WHERE
	t.type = 'U' 
	and i.type > 0 
GROUP BY 
	t.name 
   ,i.name 
   ,CASE i.is_unique 
		WHEN 1 THEN 'UNIQUE ' 
		ELSE '' 
	END + i.type_desc
   ,CASE 
		WHEN is_primary_key = 1 
		THEN 'PRIMARY KEY' 
		WHEN is_unique_constraint = 1 
		THEN 'UNIQUE CONSTRAINT' 
		ELSE '' 
	END 
   ,c.key_columns
   ,isnull(ic.included_columns, '') 

--создаем индекс на временную таблицу, чтобы быстрее крутился курсор
CREATE NONCLUSTERED INDEX IX_TableName
	ON #tmp_indx (table_name)
	INCLUDE (key_columns)

--создаем временную таблицу для результирующего набора
IF object_id('tempdb..#tmp_result') is not null
   DROP TABLE #tmp_result

CREATE TABLE #tmp_result
(       table_name varchar(100)
	   ,index_name varchar(100)
	   ,index_type varchar(100)
	   ,based_constraint varchar(50)
	   ,key_columns varchar(2000)
	   ,included_columns varchar(2000)
	   ,indexsize_mb money
)

--объявляем курсор для перебора индексов
DECLARE @table_Name varchar(20)
	   ,@index_name varchar(50)
	   ,@index_type varchar(100)
	   ,@based_constraint varchar(50)
	   ,@key_columns varchar(2000)
	   ,@included_columns varchar(2000)
	   ,@indexsize_mb money
	   ,@patindex int
DECLARE indx CURSOR FOR 
  SELECT table_name, index_name, index_type, based_constraint, key_columns, included_columns, indexsize_mb FROM #tmp_indx
  WHERE based_constraint <> 'PRIMARY KEY' --не проверяем индексы первичных ключей
  AND included_columns = '' --не проверяем индексы с INCLUDE полями
  AND index_type not in ('CLUSTERED','UNIQUE CLUSTERED') --не проверяем кластерные индексы 
OPEN indx
WHILE (1=1)
	BEGIN;
		FETCH NEXT
		   FROM indx
		   INTO  @table_Name, @index_name, @index_type, @based_constraint, @key_columns, @included_columns, @indexsize_mb
		IF @@FETCH_STATUS < 0 
		BEGIN
			BREAK 
		END
		--сравниваем текущий индекс со всеми индексами таблицы
		SELECT @patindex = sum(patindex(replace(@key_columns,'_','[_]')+',%', key_columns+',')) FROM #tmp_indx 
		WHERE table_name = @table_name
		--если есть совпадения(не считая самого себя), то записываем этот индекс в результирующий набор	
		IF @patindex > 1
			INSERT INTO #tmp_result (table_Name, index_name, index_type, based_constraint, key_columns, included_columns, indexsize_mb)
			VALUES (@table_Name, @index_name, @index_type, @based_constraint, @key_columns, @included_columns, @indexsize_mb)
	END
CLOSE indx
DEALLOCATE indx
--выводим результат
SELECT * 
    ,'DROP INDEX [' +  index_name +    
       '] ON ' + object_schema_name(object_id(table_name))+'.'+quotename(table_name) 
       AS drop_statement
    ,'CREATE ' + index_type + ' INDEX ['+index_name+'] ON ' + object_schema_name(object_id(table_name))+'.'+quotename(table_name) +
	   ' ('+key_columns+')' 
	   AS create_statement 
FROM #tmp_result
--ORDER BY table_name, key_columns
ORDER BY indexsize_mb desc

--Если параметр включен - то выполняем поиск
IF @SrchInData = 1
BEGIN
	--ищем ссылки на найденные индексы в определениях программных модулей
	SELECT sm.object_id, object_schema_name(sm.object_id)+'.'+object_name(sm.object_id) AS [object_name], definition FROM sys.sql_modules AS sm
	WHERE EXISTS 
	(
		SELECT patindex('%'+replace(tm.index_name,'_','[_]')+'%', sm.definition) FROM #tmp_result AS tm
		WHERE patindex('%'+replace(tm.index_name,'_','[_]')+'%', sm.definition) > 0
	) 
	--ищем ссылки на найденные индексы в кэше
	IF object_id('tempdb..#tmp_search') is not null
	   DROP TABLE #tmp_search
	
	SELECT DISTINCT (SELECT TOP 1 SUBSTRING(qt.text,qs.statement_start_offset / 2+1 , 
					( (CASE WHEN qs.statement_end_offset = -1 
							THEN (LEN(CONVERT(nvarchar(max),qt.text)) * 2) 
							ELSE qs.statement_end_offset END)  - qs.statement_start_offset) / 2+1))  AS sql_statement 
	INTO #tmp_search							
	FROM sys.dm_exec_query_stats AS qs
	CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) AS qt
	WHERE qt.objectid is null --исключаем определения программных модулей, так как там уже искали	
	
	SELECT ts.* FROM #tmp_search AS ts
	WHERE EXISTS
	(
		SELECT patindex('%'+replace(tm.index_name,'_','[_]')+'%', ts.sql_statement) FROM #tmp_result AS tm
		WHERE patindex('%'+replace(tm.index_name,'_','[_]')+'%', ts.sql_statement) > 0
	)
END 

[/more]

Комментарии


  • Можно выполнить одним запросом, без цикла:

    WITH cte_index_info AS (
    SELECT
    tSS.[name] AS [SchemaName]
    ,tSO.[name] AS [ObjectName]
    ,tSO.[type_desc] AS [ObjectType]
    ,tSO.[create_date] AS [ObjectCreateDate]
    ,tSI.[name] AS [IndexName]
    ,tSI.[is_primary_key] AS [IndexIsPrimaryKey]
    ,d.[index_type_desc] AS [IndexType]
    ,d.[avg_fragmentation_in_percent] AS [IndexFragmentation]
    ,d.[fragment_count] AS [IndexFragmentCount]
    ,d.[avg_fragment_size_in_pages] AS [IndexAvgFragmentSizeInPages]
    ,d.[page_count] AS [IndexPages]
    ,c.key_columns AS [IndexKeyColumns]
    ,ISNULL(ic.included_columns, '') AS [IndexIncludedColumns]
    FROM
    (
    SELECT
    tSDDIPS.[object_id] AS [object_id]
    ,tSDDIPS.[index_id] AS [index_id]
    ,tSDDIPS.[index_type_desc] AS [index_type_desc]
    ,MAX(tSDDIPS.[avg_fragmentation_in_percent]) AS [avg_fragmentation_in_percent]
    ,MAX(tSDDIPS.[fragment_count]) AS [fragment_count]
    ,MAX(tSDDIPS.[avg_fragment_size_in_pages]) AS [avg_fragment_size_in_pages]
    ,MAX(tSDDIPS.[page_count]) AS [page_count]
    FROM
    [sys].[dm_db_index_physical_stats] (DB_ID(), NULL, NULL , NULL, N'LIMITED') tSDDIPS
    GROUP BY
    tSDDIPS.[object_id]
    ,tSDDIPS.[index_id]
    ,tSDDIPS.[index_type_desc]
    ) d
    INNER JOIN [sys].[indexes] tSI ON
    tSI.[object_id] = d.[object_id]
    AND tSI.[index_id] = d.[index_id]
    INNER JOIN [sys].[objects] tSO ON
    tSO.[object_id] = d.[object_id]
    INNER JOIN [sys].[schemas] tSS ON
    tSS.[schema_id] = tSO.[schema_id]
    CROSS APPLY (
    SELECT
    STUFF((
    SELECT
    ', ' + c.[name] +
    CASE ic.[is_descending_key]
    WHEN 1 THEN
    '(-)'
    ELSE
    ''
    END
    FROM
    [sys].[index_columns] ic
    INNER JOIN [sys].[columns] c ON
    c.[object_id] = ic.[object_id]
    and c.[column_id] = ic.[column_id]
    WHERE
    ic.[index_id] = tSI.[index_id]
    AND ic.[object_id] = tSI.[object_id]
    AND ic.[is_included_column] = 0
    ORDER BY
    ic.[key_ordinal]
    FOR XML
    PATH('')
    )
    ,1, 2, ''
    ) AS [key_columns]
    ) c
    CROSS APPLY (
    SELECT
    STUFF((
    SELECT
    ', ' + c.[name]
    FROM
    [sys].[index_columns] ic
    INNER JOIN [sys].[columns] c ON
    c.[object_id] = ic.[object_id]
    AND c.[column_id] = ic.[column_id]
    WHERE
    ic.[index_id] = tSI.[index_id]
    AND ic.[object_id] = tSI.[object_id]
    AND ic.[is_included_column] = 1
    FOR XML
    PATH('')
    )
    ,1, 2, ''
    ) AS [included_columns]
    ) ic
    WHERE
    tSO.[type_desc] IN (
    N'USER_TABLE'
    )
    AND OBJECTPROPERTY(tSO.[object_id], N'IsMSShipped') = 0
    AND d.[index_type_desc] NOT IN (
    'HEAP'
    )
    )
    SELECT
    t1.[SchemaName]
    ,t1.[ObjectName]
    ,t1.[ObjectType]
    ,t1.[ObjectCreateDate]
    ,t1.[IndexName]
    ,t1.[IndexIsPrimaryKey]
    ,t1.[IndexType]
    ,t1.[IndexFragmentation]
    ,t1.[IndexFragmentCount]
    ,t1.[IndexAvgFragmentSizeInPages]
    ,t1.[IndexPages]
    ,t1.[IndexKeyColumns]
    ,t1.[IndexIncludedColumns]
    ,t2.[IndexName]
    FROM
    cte_index_info t1
    INNER JOIN cte_index_info t2 ON
    t2.[SchemaName] = t1.[SchemaName]
    AND t2.[ObjectName] = t1.[ObjectName]
    AND t2.[IndexName] <> t1.[IndexName]
    AND PATINDEX(REPLACE(t1.[IndexKeyColumns], '_', '[_]') + ',%', t2.[IndexKeyColumns] + ',') > 0
    WHERE
    t1.[IndexIncludedColumns] = '' -- don't check indexes with INCLUDE columns
    AND t1.[IndexIsPrimaryKey] = 0 -- don't check primary keys
    AND t1.[IndexType] NOT IN (
    N'CLUSTERED INDEX'
    ,N'UNIQUE CLUSTERED INDEX'
    ) -- don't check clustered indexes
    ORDER BY
    t1.[SchemaName]
    ,t1.[ObjectName]
    ,t1.[IndexName]
    ;



Необходимо войти на сайт, чтобы оставлять комментарии