Возможно, многие сталкивались с исторически сложившейся за годы, до появления на проекте, ситуацией, когда на таблице создали все возможные индексы со всеми 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.

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 в планах.

Комментарии (4)


  1. minamoto
    11.12.2017 18:21

    Приятно, наверное, начать создавать такой индекс на таблице в 700 млн. строк и к концу обновления получить ошибку:

    Msg 1946, Level 16, State 3, Line 23
    Operation failed. The index entry of length 909 bytes for the index 'IX_ClientFile_StorageId_FolderId_FileStatus_FileName' exceeds the maximum length of 900 bytes.


    Ну или, как у вас, создать новую таблицу с таким индексом, и получить такую же ошибку ближе к концу миграции данных.

    В общем, даже если миграция у вас прошла успешно — вы уже приставили ружье к ноге и взвели курок. Осталось дождаться выстрела.


  1. KristinaMyLife Автор
    11.12.2017 20:28

    Спасибо за замечание, хотя тип действительно NVARCHAR(900), к счастью ни одного файла с таким названием в системе нет, хотя все равно есть предупреждение при создании

    Warning! The maximum key length is 900 bytes. The index 'IX_ClientFile_StorageId_FolderId_FileStatus_FileName' has maximum length of 1809 bytes. For some combination of large values, the insert/update operation will fail.


    1. Kant8
      12.12.2017 11:24

      Учитывая что FileName в индексе аж на 4 месте, и его влияние на распределение данных в индексе скорее всего будет уже очень маленьким, не лучше ли его просто добавить в инклюд?
      Да, работать будет помедленней, тк будет скан листьев индекса (но как я писал, их скорее всего уже очень мало), но никаких падений после этого уже не может быть, а key lookup всё так же исчезнет.


      1. KristinaMyLife Автор
        12.12.2017 11:31

        Вообще когда я бралась за этот проект я очень надеялась избавиться от поля FileName в индексе совсем, так как в некоторых запросах используются много полей таблицы и все равно возникает key lookup. К сожалению, при тестах обнаружили, что его отсутствие сказывается на производительности. Добавили в сам индекс исходя из того, что оно все таки кое-где используется в WHERE и оно было раньше в индексе. Возможно, вы правы и нужно было прогнать еще раз вариант с INCLUDE.