Одну из первых строк в списке способов настройки производительности любого сервера SQL Server занимает оптимизация индексов базы данных. Способность оптимизатора запросов сервера SQL Server правильно использовать индексы при выполнении запроса зависит не только от создания эффективных индексов, но
и от того, насколько эти индексы работоспособны. Ряд динамических административных представлений (DMV) и функций (DMF), вошедших в состав SQL Server™ 2005, может помочь администраторам баз данных оценить эффективность своих индексов и определить проблемы с производительностью.
Эти представления и функции позволяют взглянуть на работу сервера изнутри и получить сведения о состоянии сервера, с помощью которых можно отслеживать работоспособность и производительность экземпляра сервера и определять проблемы. Администраторы баз данных, знакомые с предыдущими версиями SQL Server, обнаружат, что эти представления и функции заменяют использование команд DBCC, выполнение системных хранимых процедур, запросы данных многих системных таблиц, а также сбор событий с помощью приложения SQL Profiler.
Три основные функции и представления, а именно: sys.dm_db_index_physical_stats, sys.dm_db_index_usage_stats и sys.dm_db_index_operational_stats, позволяют понять, работают ли индексы так, как планировалось. С их помощью можно посмотреть, как ведут себя индексы в ходе операций ввода-вывода и при блокировках, а также определить, действительно ли оптимизатор запросов применяет индексы так, что это не приводит к ненужному состязанию в базе данных.
Функция DMF sys.dm_db_index_physical_stats предназначалась для замены команды DBCC SHOWCONTIG. Она показывает фрагментацию индекса. Однако, в отличие от команды DBCC SHOWCONTIG, которой требуется общая блокировка (S) таблицы, содержащей индекс, функция sys.dm_db_index_physical_stats устанавливает только намеренную общую блокировку (IS), что позволяет значительно уменьшить блокирование таблицы во время выполнения функции.
Чтобы определить уровень фрагментации индекса с помощью функции sys.dm_db_index_physical_stats, нужно изучить сочетание трех столбцов результатов выполнения функции. Логическую фрагментацию индексов (фрагментацию экстентов куч) можно определить по значению в столбце avg_fragmentation_in_percent. Логическая фрагментация — это процент неупорядоченных страниц на конечном уровне индекса, а фрагментация экстентов — это процент неупорядоченных экстентов на конечном уровне индекса. Фрагментация обоих указанных типов может влиять на производительность индекса, требуя дополнительных операций ввода-вывода и лишнего движения считывающей головки диска, поскольку головка, чтобы прочитать страницы по порядку, не может перемещаться последовательно. Нужно стараться, чтобы уровень как логической фрагментации, так и фрагментации экстентов был настолько близок к нулю, насколько это возможно.
Внутренняя фрагментация индекса — это процент заполненности страниц. Конечно, хотелось бы, чтобы страница индекса была заполнена настолько, насколько возможно, но нужно еще и соблюдать баланс между заполненностью и числом вставок в страницы индекса, чтобы число разбиений страниц было минимальным.
Узнать заполненность страниц индекса можно с помощью аргумента avg_page_space_used_in_percent функции sys.dm_db_index_physical_stats. Чтобы правильно определить, насколько близко это число должно быть к 100 процентам, нужно настроить коэффициент заполнения индекса, одновременно наблюдая за числом происходящих разбиений страниц. Начиная с некоторого момента число разбиений страниц начнет расти очень быстро. Это означает, что для коэффициента заполнения индекса было задано слишком высокое значение. Настройка коэффициента заполнения индекса требует времени и тестирования, и выполняться она должна только после тщательного планирования. (Для индексов, в которые не выполняются случайные вставки, можно задать коэффициент заполнения равным 100 и не волноваться об увеличении числа разбиений страниц.)
Чтобы определить уровень фрагментации всех индексов таблицы AdventureWorks.HumanResources.Employee, можно воспользоваться примерно такой инструкцией:
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks')
,OBJECT_ID('HumanResources.Employee')
,NULL
-- NULL to view all indexes;
-- otherwise, input index number
,NULL -- NULL to view all partitions of an index
,'DETAILED') -- We want all information
С помощью данной функции DMF можно автоматически определить, какие индексы должны быть перестроены, какие нуждаются в реорганизации, а какие можно не трогать. Выявление значений столбцов avg_page_space_used_in_percent и avg_fragmentation_in_percent этой функции DMF (которые показывают фрагментацию индекса), превышающих некоторый логический порог и порог плотности, поможет определить, какие операции необходимо выполнить с этим индексом.
В зависимости от состояния индексов пример, показанный на рис. 1, может не возвращать данные в конкретной копии образца базы данных AdventureWorks, но его легко можно адаптировать к другим базам данных.
Результаты запросов можно было бы записать в табличную переменную, а затем просмотреть эту переменную, чтобы построить динамическую строку для правильной инструкции ALTER INDEX (см.рис. 2).
Хотя функция sys.dm_db_index_physical_stats является мощной заменой команды DBCC SHOWCONTIG и может помочь оценить работоспособность индекса, на практике часто возникает более сложная задача: определить, какие индексы действительно используются при выполнении запросов данных таблицы. Часто разработчики или администраторы баз данных создают для таблицы индексы, которые, как они думают, оптимизатор запросов будет использовать при выполнении запроса. В предыдущих выпусках сервера SQL Server понять, используются ли на самом деле эти индексы, было значительно сложнее. Для этого нужно было либо удалить индекс и посмотреть, как это повлияет на производительность запросов, либо собрать планы выполнения запросов и изучить их на предмет использования индекса.
Новое динамическое административное представление, sys.dm_db_index_usage_stats, — это более простой способ определить, как индексы используются оптимизатором запросов и запросами данных из таблицы. Достаточно изучить это представление, чтобы оценить полезность индексов, что позволит удалить те из них, которые оптимизатор запросов не использует. Теперь можно больше не волноваться о том, что индекс только зря занимает место или что обслуживание неиспользуемых индексов приводит к снижению производительности базы данных.
Изучив результаты этого представления DMV на предмет индексов с нулевым числом операций поиска и просмотра, можно определить, использовался ли индекс с момента последнего запуска сервера SQL Server. Впрочем, необходимо помнить, что результаты многих динамических административных представлений и функций не сохраняются и сбрасываются обратно в ноль после перезапуска сервера SQL Server. Не следует забывать об этом, применяя представление DMV или функцию DMF для оценки использования индекса. Возможно, индекс просто ни разу не понадобился с момента последнего перезапуска службы, но он потребуется для запросов при составлении недельных, месячных или квартальных отчетов.
Чтобы просмотреть все индексы, которые не использовались на данном сервере с момента последнего перезапуска службы сервера SQL Server, можно воспользоваться следующей инструкцией:
SELECT DB_NAME(database_id),OBJECT_NAME([object_id]) FROM sys.dm_db_index_usage_stats WHERE user_seeks = 0 AND user_scans = 0 AND user_lookups = 0 AND system_seeks = 0 AND system_scans = 0 AND system_lookups = 0
Для определения активности операций индексов очень полезной может оказаться функция DMF sys.dm_db_index_operational_stats. Ее можно использовать для просмотра активности операций ввода-вывода, блокировок, кратковременных блокировок и метода доступа для каждого индекса в базе данных. Такая информация помогает понять, как используются индексы, и отметить случаи блокировки индексов из-за высокой активности операций ввода-вывода или из-за существования в индексе проблемной области.
С помощью столбцов latch wait данной функции DMF можно определить, сколько времени требуется операциям READ и WRITE для получения доступа к ресурсам индекса. Это позволяет понять, соответствует ли дисковая подсистема, которая используется для хранения индекса, активности его операций ввода-вывода. Кроме того, если неудачная структура или неправильное использование индекса привели к появлению проблемной области, в которой высокая активность на одной или нескольких страницах индекса вызывает состязание для данных этих страниц, это тоже будет видно из полученных результатов. Такое состязание часто приводит к избыточному блокированию операций READ или WRITE для данной области.
На Рис. 3 показано, как определить шаблоны поведения блокировок и операций ввода-вывода для всех индексов таблицы AdventureWorks.HumanResources.Employee.
Представления DMV и функции DMF, рассмотренные в этой статье, можно использовать не только так, как рассказывается здесь. На боковой панели «Дополнительные ресурсы» приведены ссылки на статьи электронной документации по SQL Server, которые описывают эти функции и представления и показывают, какой широкий спектр данных можно получить и изучить с их помощью.
Сведения о некоторых дополнительных имеющих отношение к индексам функциях DMF и представлениях DMV, которые не обсуждались в этой статье, можно найти в публикации группы оптимизации запросов сервера SQL Server по адресу: blogs.msdn.com/queryoptteam/570176.aspx.
Иcточник: TechNet Magazine
Tags: SQL, SQL Server