Как заменить старые индексы и не сломать систему?

Возможно, многие сталкивались с исторически сложившейся за годы, до появления на проекте, ситуацией, когда на таблице создали все возможные индексы со всеми include’ами. Я видела индекс на доставшейся «в наследство» БД, который содержал все поля таблицы. При этом, не всегда есть возможность быстро поменять индексы, так как часто нужна гарантия, что изменения не повлияют на работоспособность системы.

При росте объема таблицы становится мучительно больно за бесцельно занятое место, но просто так индекс уже не убьешь, а статистика использования показывает, что индекс используется.
Описаный пример консолидации индексов на высоконагруженной БД, работающей в режиме 24/7. Приложение использует только хранимые процедуры. Версия MS SQL Server 2012 SP3.

Исходная таблица с кластерным индексом:

CREATE TABLE [dbo].[ClientFile](
	[StorageId] [int] NOT NULL,
	[FolderId] [int] NOT NULL,
	[ClientFileInternalId] [int] IDENTITY(1,1) NOT NULL,
	[FileName] [nvarchar](900) NOT NULL,
	[FileExtension] [nvarchar](10) NOT NULL,
	[FileClientVersionId] [smallint] NOT NULL,
	[ClientFileVersionId] [int] NULL,
	[FileInternalId] [bigint] NOT NULL,
	[FileLength] [bigint] NOT NULL,
	[OrderId] [tinyint] NOT NULL,
	[FileFileExtensionId] [int] NULL,
	[FileStatus] [tinyint] NOT NULL,
	[DirectoryVersionId] [int] NOT NULL,
	[DateDeleted] [datetime] NULL,
 CONSTRAINT [PK_ClientFile] PRIMARY KEY CLUSTERED 
(
	[StorageId] ASC,
	[ClientFileInternalId] ASC
)
)

Некластерные индексы:

CREATE NONCLUSTERED INDEX [IX_ClientFile_StorageId_FileStatus] ON [dbo].[ClientFile]
(
	[StorageId] ASC,
	[FileStatus] ASC,
	[OrderId] DESC
)
INCLUDE ( 	
        [ClientFileInternalId],
	[FolderId],
	[DirectoryVersionId],
	[FileInternalId],
	[FileClientVersionId],
	[FileLength]); 


CREATE NONCLUSTERED INDEX [IX_ClientFile_StorageId_FolderId_FileStatus_FileName] ON [dbo].[ClientFile]
(
	[StorageId] ASC,
	[FolderId] ASC,
	[FileStatus] ASC,
	[FileName] ASC
)
INCLUDE ( 	
        [DateDeleted],
	[DirectoryVersionId],
	[FileExtension],
	[FileInternalId],
	[FileClientVersionId],
	[FileLength]);

В 1м индексе из 14 полей таблицы содержится 9, а во втором — 10.

Эти 2 индекса суммарно занимают до 180 Гб на каждом сервере, серверов 12. Это расстраивает и беспокоит, так как индексы с совпадающими полями, а также по 6 включенных полей в каждом. Кроме того, иногда сервер выбирает эти индексы в случаях, когда эффективнее было бы использовать кластерный индекс, что требовало перекомпиляции процедуры. После перекомпиляции уже использовался кластерный индекс и снижалась нагрузка на ЦПУ.

Шаг 1. Анализ статистики использования индексов

С серверов собирается информация об использовании индексов по таблице ClientFile.

declare @dbid int
select @dbid = db_id()
select (cast((user_seeks + user_scans + user_lookups) as float) / case user_updates when 0 then 1.0 else cast(user_updates as float) end) * 100 as [%]
	, (user_seeks + user_scans + user_lookups) AS total_usage
	, objectname=object_name(s.object_id), s.object_id
	, indexname=i.name, i.index_id
	, user_seeks, user_scans, user_lookups, user_updates
	, last_user_seek, last_user_scan, last_user_update
	, last_system_seek, last_system_scan, last_system_update
	, 'DROP INDEX ' + i.name + ' ON ' + object_name(s.object_id) as [Command]
from sys.dm_db_index_usage_stats s,
	sys.indexes i
where database_id = @dbid 
and objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
and i.name not like 'PK_%'
and object_name(s.object_id) = 'ClientFile'
order by [%] asc

В описанном случае оба индекса используются. Если какой то индекс не используется, это облегчает задачу и часто его можно просо удалить. Данная статистика очищается при рестарте MS SQL Server, при решении об удалении индекса на ее основе необходимо убедиться, что в БД нет какого-нибудь очень важного отчета, который считается раз в месяц и как раз использует этот индекс.

Шаг 2. Выбрать все планы из кэша, в которых есть использование описанных 2х индексов.

Для этого используется процедура spGetPlanUsingIndex (основной запрос взят из статей Jonathan Kehayias www.sqlskills.com/blogs/jonathan), которая складывает статистику использования в таблицу. Настроен джоб с запуском процедуры для сбора статистики раз в 1 час.

Важный момент – не все планы будут в кэше, например процедуры с хинтом RECOMPILE. Если используется такой хинт, процедуры нужно проверить и сформировать для них план и включить его в анализ.

CREATE TABLE [dbo].[LogDataFileIndexUsage](
	[PlanId] [int] IDENTITY(1,1) NOT NULL,
	[PlanDate] [datetime] NOT NULL CONSTRAINT [DF_LogDataFileIndexUsage_PlanDate]  DEFAULT (getutcdate()),
	[DBname] [varchar](50) NULL,
	[SPname] [varchar](256) NULL,
	[ScanCount] [int] NULL,
	[SeekCount] [int] NULL,
	[UpdateCount] [int] NULL,
	[RefCount] [int] NULL,
	[UseCount] [int] NULL,
	[QueryPlan] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[LogDataFileIndexUsage] ADD [IndexName] [varchar](256) NULL
PRIMARY KEY CLUSTERED 
(
	[PlanId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


GO

CREATE PROCEDURE [dbo].[spGetPlanUsingIndex]
	@indexName NVARCHAR(128)
AS
BEGIN	
	SET NOCOUNT ON;

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 	

	-- Make sure the name passed is appropriately quoted 
	IF (LEFT(@IndexName, 1) <> '[' AND RIGHT(@IndexName, 1) <> ']') SET @IndexName = QUOTENAME(@IndexName); 

	WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
	INSERT INTO [dbo].[LogDataFileIndexUsage]
	(
		DBName,
		SPname, 
		ScanCount,
		SeekCount,
		UpdateCount,
		RefCount,
		UseCount,
		QueryPlan,
		IndexName
	)
	SELECT
		DB_NAME(E.dbid) AS [DBName],
		object_name(E.objectid, dbid) AS [ObjectName],
		E.query_plan.value('count(//RelOp[@LogicalOp = ''Index Scan'' or @LogicalOp = ''Clustered Index Scan'']/*/Object[@Index=sql:variable("@IndexName")])','int')  AS [ScanCount],
		E.query_plan.value('count(//RelOp[@LogicalOp = ''Index Seek'' or @LogicalOp = ''Clustered Index Seek'']/*/Object[@Index=sql:variable("@IndexName")])','int')  AS [SeekCount],
		E.query_plan.value('count(//Update/Object[@Index=sql:variable("@IndexName")])','int') AS [UpdateCount],	
		P.refcounts AS [RefCounts],
		P.usecounts AS [UseCounts],
		E.query_plan AS [QueryPlan],
		@IndexName
	FROM sys.dm_exec_cached_plans P
	CROSS APPLY sys.dm_exec_query_plan(P.plan_handle) E
	WHERE	
		E.query_plan.exist('//*[@Index=sql:variable("@IndexName")]') = 1
	OPTION(MAXDOP 1, RECOMPILE);
END

GO

Шаг 3. Анализ собранных данных

В данном случае анализ показал, что оба индекса используются одними и теми же процедурами, то есть процедура использует, то один индекс то другой, для одного и того же запроса.
Всего есть 24 процедуры, которые используют эти индексы. По каждой процедуре проанализировано по каким полям фильтруются данные, делается JOIN и какие поля указаны в SELECT.

Это все делалось вручную в excel таблице. Сейчас я понимаю, что можно было избежать такого количества ручного труда написав запрос с выборкой этого из xml плана. Поля для выборки: seek predicate, predicate и какие поля выбираются из таблицы.

Шаг 4. Формирования нового индекса

На основе данных, полученных при анализе использования индексов в планах сформирован новый индекс.

CREATE NONCLUSTERED INDEX [IX_ClientFile_StorageId_FolderId_FileStatus] ON [dbo].[ClientFile] 
([StorageId], 
[FolderId], 
[FileStatus]);

Шаг 5. Тестирование

На проекте, при внесении изменений, обязательно делается нагрузкочное тестирование. Нагрузочный тест с новым индексом показал, что необходимо добавить поле Name в индекс, чтобы убрать Key lookup. Поле добавлено, так как используется в предложении WHERE.
Также после теста проверяется информация в missing index.

SELECT
mig.index_group_handle, mid.index_handle,
CONVERT (decimal (28,1),
migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
) AS improvement_measure,
'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '')
+ ' with (online=on)' AS create_index_statement,
migs.*, mid.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10 --AND database_id = 12
AND mid.statement like ('%[ClientFile]')
ORDER BY convert(varchar(10), last_user_seek, 120) desc, migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) /*last_user_seek*/ DESC

Итоговый индекс

CREATE NONCLUSTERED INDEX [IX_ClientFile_StorageId_FolderId_FileStatus_FileName] ON [dbo].[ClientFile] 
([StorageId], 
[FolderId], 
[FileStatus], 
[FileName]);

Шаг 6. Применение изменений

Таблица 700 млн строк, поэтому новый индекс применили с помощью создания новой таблицы и миграции данных в нее.

Экономия места на диске 45% на каждом сервере. Сервер чаще использует кластерный индекс, что снижает количество key lookup в планах.

Автор: KristinaMyLife

Источник