SQL Server: Базы данных и индексы

Microsoft логически делит объекты управления БД (DMO) на уровне базы данных и файлов на две категории:

Относящиеся к базам данных Эти объекты содержат административные представления (DMV), которые позволяют изучить таблицы и странички индексов и число строк данной БД, также выделение страничек на уровне файла. Несколько DMV служат для анализа использования базы данных TempDB.
Относящиеся к индексам Сюда относятся представления DMV, связанные с индексами, их чертами, с тем как индексы употребляются, они также помогают определять, какие индексы подходят для ваших задач.

Имена всех представлений в этих 2-ух категориях начинаются с «sys.dm_db_». Эти типы представлений DMV помогают найти эффективную стратегию индексирования, потому что это один из наилучших методов обеспечения, чтоб важнейшие и нередко исполняемые запросы могли читать нужные им данные упорядоченными образом и не создавать лишнюю нагрузку на подсистему ввода/вывода. Определение правильного баланса меж очень огромным и очень малым количеством индексов и реализация «правильного» набора индексов только важны для обеспечения наибольшей производительности SQL Server.

Необходимо также смотреть за базой данных TempDB. TempDB — глобальный ресурс, хранящий временные данные юзера и внутренних объектов всех юзеров, присоединенных к данному экземпляру SQL Server. Сюда, а именно, входят внутренние рабочие таблицы, в каких хранятся результаты курсоров, также пользовательские объекты, такие как временные таблицы и табличные переменные.

Поиск отсутствующих индексов

Для обнаружения индексов, которых не хватает в базе данных, используют три тесновато связанных представления DMV. 1-ое, sys.dm_db_missing_index_group_stats, описывается последующим образом:

«Возвращает сводку сведений о группах отсутствующих индексов, кроме пространственных индексов. Сведения, возвращаемые представлением sys.dm_db_missing_index_group_stats, обновляются при каждом выполнении запроса, а не при каждой компиляции либо повторной компиляции запроса. Статистика использования не сохраняется и хранится только до перезапуска SQL Server. Админы базы данных должны временами делать запасные копии сведений об отсутствующих индексах, если нужно сохранить статистику использования после перезагрузки сервера».

А вот описание второго, sys.dm_db_missing_index_groups:

«Возвращает сведения об отсутствующих индексах, содержащихся в определенной группе отсутствующих индексов, кроме пространственных индексов».

Это в сути таблица соединения sys.dm_db_missing_index_group_stats и третьего представления DMV, sys.dm_db_missing_index_details, которое описывается так:

«Возвращает подробные сведения об отсутствующих индексах, кроме пространственных индексов».

Соединяя эти три представления, получаем нужный запрос, позволяющий узнать, каких индексов не хватает (рис. 1).

Рис. 1. Выявление индексов, которые могут быть полезны в базе данных

– Missing Indexes in current database by Index Advantage
SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )
AS [index_advantage] ,
migs.last_user_seek ,
mid.[statement] AS [Database.Schema.Table] ,
mid.equality_columns ,
mid.inequality_columns ,
mid.included_columns , migs.unique_compiles ,
migs.user_seeks ,
migs.avg_total_user_cost ,
migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK )
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK )
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK )
ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY index_advantage DESC ;

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

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

Необходимо подразумевать, что у этого подхода есть ряд ограничений. Во-1-х, этот запрос не всегда показывает на лучший порядок столбцов в индексе. Если в equality_columns or inequality_columns указывается несколько столбцов, необходимо поглядеть на избирательность этих столбцов, чтоб найти лучший столбец в предполагаемом индексе. Во-2-х, не учитываются фильтруемые индексы, которые появились в SQL Server 2008. В конце концов, в общем случае запрос стремится предложить включенные столбцы и новые индексы.

Никогда не надо слепо создавать все предлагаемые запросом индексы, в особенности если это OLTP-система. Лучше пристально проанализировать результаты запроса и вручную отфильтровать результаты, которые не соответствуют вашей постоянной рабочей нагрузке.

Начните с анализа столбца last_user_seek. Если возраст временной метки last_user_seek некоторое количество дней либо даже недель, то вероятнее всего запросы, на основании которых SQL Server посчитал нужным соответственный индекс, являются случайными либо относящимися к нечасто исполняемому запросу по созданию отчета. С другой стороны, если давность last_user_seek составляет несколько секунд либо минут, тогда это вероятнее всего часть постоянной нагрузки и необходимо отнестись к соответственному предлагаемому индексу серьезнее.

Независим от советов запроса, перед внесением всех конфигураций всегда следует проанализировать имеющиеся индексы таблицы, также их статистику использования. Как вы понимаете, в более изменчивой таблице необходимо меньше индексов, чем более статичной. Необходимо с большой осторожностью относиться к добавлению новых индексов в таблицу (в OLTP-системе), если в таблице уже есть 5 либо 6 действенных индексов.

Не запамятовывайте, что системная хранимая процедура sp_helpindex не указывает информацию включенных столбцов. Это значит, что необходимо пользоваться подменой либо просто сделать предложение CREATE INDEX для имеющихся индексов.

Анализ использования индексов

Одно из самых нужных представлений DMV в категории индексации — dm_db_index_usage_stats, которое описывается последующим образом:

«Возвращает количество разных операций с индексами и время, которое было затрачено на последнее выполнение операции каждого типа. Любая отдельная операция поиска, просмотра, уточняющего запроса либо обновления на данном индексе при выполнении 1-го запроса засчитывается как внедрение этого индекса и наращивает на единицу соответственный счетчик в данном представлении. Данные выводятся как для операций, вызванных пользовательскими запросами, так и для операций, вызванных внутренними запросами, к примеру при выполнении операции просмотра для сбора статистики».

Это представление DMV предоставляет неоценимую информацию о том, употребляются ли индексы и как нередко это происходит. Это представление позволяет также получить информацию о:

Рассредотачивании вашей рабочей нагрузки по отношению к имеющимся индексам.
Индексах, которые не употребляются, и, потому, являются первыми кандидатами на удаление.
Индексах с огромным числом операций записи и нулей либо малым числом операций записи (они также являются кандидатами на удаление.)

1-ый из этих 3-х сценариев (рис. 2) представляет перечень всех таблиц кучи, кластеризованных и некластеризованных индексов, также число операций чтения, записи и коэффициент наполнения каждого индекса.

Рис. 2. Эти сценарии позволяют узнать, как употребляются индексы

— Index Read/Write stats (all tables in current DB)
SELECT OBJECT_NAME(s.[object_id]) AS [ObjectName] ,
i.name AS [IndexName] , i.index_id ,
user_seeks + user_scans + user_lookups AS [Reads] ,
user_updates AS [Writes] ,
i.type_desc AS [IndexType] ,
i.fill_factor AS [FillFactor]
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
WHERE OBJECTPROPERTY(s.[object_id], ‘IsUserTable’) = 1
AND i.index_id = s.index_id
AND s.database_id = DB_ID()
ORDER BY OBJECT_NAME(s.[object_id]) ,
writes DESC ,
reads DESC ;

Это нужный запрос для осознания имеющейся рабочей нагрузки. Он позволяет оценить изменчивость того либо другого индекса, также соотношение операций чтения и записи. Благодаря этому вы сможете уточнить и поточнее настроить свою стратегию индексации. К примеру, если у вас сравнимо статическая таблица (очень мало операций записи во всех индексах), можно более уверенно добавлять индексы, из числа предлагаемых SQL Server.

В редакции SQL Server 2008 Enterprise Edition этот запрос позволяет решить, стоит включать сжатие данных (на уровне страничек либо строк). Индекс с маленьким числом операций записи лучше подходит для сжатия данных, чем более интенсивно изменяющийся индекс.

В последующем сценарии (рис. 3) sys.indexes и sys.objects употребляются для обнаружения в текущей базе данных таблиц и индексов, которые отсутствуют в результатах sys.dm_db_index_usage_stats. Это значит, что в этих индексах не было операций чтения либо записи с момента последнего пуска SQL Server либо с момента закрытия либо отключения текущей БД (выбирается самая поздняя дата).

Рис. 3. Обнаружение неиспользуемых индексов

– List unused indexes
SELECT OBJECT_NAME(i.[object_id]) AS [Table Name] ,
i.name
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id]
WHERE i.index_id NOT IN ( SELECT s.index_id
FROM sys.dm_db_index_usage_stats AS s
WHERE s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
AND database_id = DB_ID() )
AND o.[type] = ‘U’
ORDER BY OBJECT_NAME(i.[object_id]) ASC ;

Если SQL Server работал довольно длительно, чтоб рабочую нагрузку за этот период можно было считать полной и репрезентативной, вероятнее всего эти индексы (либо таблицы) «мертвые». Это значит, что они больше не употребляются в базе данных и после маленького исследования их можно удалить.

В последнем запросе результаты sys.dm_db_index_usage_stats фильтруются по текущей базе данных (рис. 4). Сюда входят некластеризованные индексы, Это позволяет решить, оправдывает ли сохранение индекса издержки на его поддержку.

Рис. 4. Обнаружение изредка применяемых индексов

– Possible Bad NC Indexes (writes > reads)
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] ,
i.name AS [Index Name] ,
i.index_id ,
user_updates AS [Total Writes] ,
user_seeks + user_scans + user_lookups AS [Total Reads] ,
user_updates – ( user_seeks + user_scans + user_lookups )
AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH ( NOLOCK )
INNER JOIN sys.indexes AS i WITH ( NOLOCK )
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id], ‘IsUserTable’) = 1
AND s.database_id = DB_ID()
AND user_updates > ( user_seeks + user_scans + user_lookups )
AND i.index_id > 1
ORDER BY [Difference] DESC ,
[Total Writes] DESC ,
[Total Reads] ASC ;

Этот запрос отыскивает индексы, у каких огромное число операций записи и вообщем нет операций чтения. Все такие индексы являются неплохими кандидатами на удаление (после соответствующего исследования). Необходимо убедиться, что SQL Server работал довольно длительно, чтоб набрать статистику, характеризующую типичную рабочую нагрузку.

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

Необходимо глядеть строчки с огромным числом операций записи и малым числом операций чтения. При удалении этих индексов необходимо полагаться на специфику определенных таблиц и свое осознание обычной рабочей нагрузки.

Аналогичный товар: Комментирование на данный момент запрещено, но Вы можете оставить ссылку на Ваш сайт.

Комментарии закрыты.