Повышение производительности запроса

Фрагментация индекса

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

Когда выполняется вставка данных в таблицу, эти данные сохраняются на указанной странице среди страниц уровня листовых вершин кластеризованного индекса. Ключи некластеризованного индекса также должны быть вставлены на правильную страницу среди страниц уровня листовых вершин некластеризованного индекса. Если на этой странице нет места, то SQL Server должен выполнить разбор страниц, это означает выделение новой страницы и привязки новой страницы к соответствующему индексу. Подобные ситуации приводят к фрагментации индекса; в этой ситуации логический порядок страниц данных больше не соответствует физическому порядку. Фрагментация индекса может также произойти в результате выполнения инструкций UPDATE и DELETE.

Чтобы уменьшить фрагментацию, можно использовать при создании индекса параметр FILLFACTOR, который определяет, до какого процента должны быть заполнены страницы уровня листовых вершин индекса, когда создаются индексы. При более низком значении параметра FILLFACTOR фрагментация наименее вероятна, потому что на странице уровня листовых вершин можно поместить большие записи без разбиения страницы. С другой стороны, для более низких значений параметра FILLFACTOR индекс будет больше, потому что изначально на каждой странице уровня листовых вершин хранится меньше данных.

Если индексируемая таблица не является таблицей только для чтения, то ее индексы рано или поздно будут фрагментированы. Фрагмен-тированные индексы можно дефрагментировать для повышения скорости доступа к данным при помощи инструкции ALTER INDEX. Для дефрагментации предусмотрено два параметра:

• REORGANIZE. Реорганизация индекса означает, что страницы уровня листовых вершин сортируются при помощи операции пузырьковой сортировки. REORGANIZE сортирует только страницы данных, а не записи на страницах; это означает, что параметр FILLFACTOR при реорганизации использовать нельзя.
• REBUILD. Перестройка (rebuilding) индекса означает, что перестраивается весь индекс. Это требует больше времени, чем реорганизация индекса, но дает лучшие результаты. Можно указать параметр FILLFACTOR, чтобы страницы снова заполнялись до желаемой степени. Если параметр FILLFACTOR не указывается, то страницы уровня листовых вершин заполняются до предела. Параметр ONLINE также может указываться при перестройке индексов. Если этот параметр не указан, то перестройка индекса выполняется в автономном режиме, что означает блокировку таблицы на протяжении всего процесса. Перестройка в автономном режиме выполняется быстрее, чем в рабочем режиме, но из-за блокировки данных она не может использоваться в то время, когда необходим доступ к данным.

Обслуживаем индексы

1. Запустите SQL Server Management Studio. Откройте окно New Query (Создать запрос) и измените контекст базы данных на Adventure Works.
2. Чтобы получить информацию о фрагментации, используйте функцию наборов sys.dm_db_physical_stats. Чтобы извлечь список индексов с фрагментацией более 50%, введите и выполните следующую инструкцию. Код этого примера можно найти среди файлов примеров под именем IndexFragmentation.sql.

   SELECT object_name(i.object_id) as object_name ,i.name as IndexName
      ,ps.avg_fragmentation_in_percent
      ,avg_page_space_used_in_percent 
     FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL , 'DETAILED') as ps
     INNER JOIN sys.indexes as i 
        ON i.object_id = ps.object_id AND i.index_id = ps.index_id 
     WHERE ps.avg_fragmentation_in_percent > 50 
       AND ps.index_id > 0 ORDER BY 1
3. Чтобы выполнить перестройку индекса PK_Employee_EmployeeID в рабочем режиме, введите и выполните следующую инструкцию.

   ALTER INDEX PK_Employee_EmployeeID
      ON HumanResources.Employee
      REBUILD
    WITH (ONLINE = ON)
4. Закройте окно среды SQL Server Management Studio.

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

Настройка запросов с помощью Помощника по настройке ядра СУБД

Создание правильных индексов для проекта базы данных - непростая задача. Здесь необходимо учесть множество факторов:

• Модель данных базы данных
• Объем и распределение данных в таблицах
• Какие запросы к базе данных обычно выполняются
• Как часто выполняются запросы
• С какой частотой обновляются данные

Чтобы помочь пользователю в проектировании индексов, SQL Server предлагает инструмент, который называется Database Engine Tuning Advisor (Помощник по настройке ядра СУБД). Помощнику по настройке ядра СУБД необходим файл рабочей нагрузки, который может быть текстовым файлом, содержащим оптимизируемые инструкции, или файлом трассировки, который может быть сгенерирован при помощи компонента SQL Server Profiler. Затем Помощник по настройке ядра СУБД оптимизирует базу данных, которая использует оптимизатор запросов SQL Server и существующую базу данных, чтобы сгенерировать рекомендации по поводу изменений в физической структуре проекта (например, по поводу создания, изменения или удаления различных индексов).

Используем Помощник по настройке ядра СУБД

1. Запустите SQL Server Management Studio. Откройте окно New Query (Создать запрос) и измените контекст базы данных на Adventure Works.
2. Введите следующие инструкции, которые мы будем оптимизировать с помощью Помощника по настройке ядра СУБД. Код этого примера можно найти среди файлов примеров под именем UsingDatabaseEngineTuningAdvisor.sql.

   USE AdventureWorks;
     SELECT o.SalesOrderID, o.OrderDate, od.ProductID 
        FROM dbo.Orders o INNER JOIN dbo.OrderDetails od 
          ON o.SalesOrderID = od.SalesOrderID 
        WHERE o.SalesOrderID = 43659;
     SELECT o.SalesOrderID, o.OrderDate, od.ProductID 
        FROM dbo.Orders o INNER JOIN dbo.OrderDetails od
          ON o.SalesOrderID = od.SalesOrderID
        WHERE o.SalesOrderID BETWEEN 43659 AND 44000;
3. Чтобы сохранить этот сценарий в качестве файла рабочей нагрузки, откройте меню File (Файл) и выберите команду Save As (Сохранить как). Сохраните файл под именем dta.sql.
4. В SQL Server Management Studio выберите из меню Tools (Сервис) команду Database Engine Tuning Advisor (Помощник по настройке ядра СУБД). Установите соединение с экземпляром SQL Server
5. Выберите файл, который мы сохранили в пункте 3 как файл рабочей нагрузки и выберите в качестве базы данных, подлежащей настройке, Adventure Works.
   

   
   увеличить изображение
   
6. Нажмите на панели инструментов кнопку Start Analysis (Начать анализ).
7. По завершении анализа откроется окно с рекомендациями, как показано на следующем рисунке:
   

   
   увеличить изображение
   
8. Помощник по настройке ядра СУБД рекомендует создать два индекса. Чтобы сохранить сценарий для генерации индексов, выберите из меню Actions (Действия) команду Save Recommendations (Сохранить рекомендации).
9. Закройте окно Database Engine Tuning Advisor (Помощник по настройке ядра СУБД).

Как мы убедились, SQL Server в меру своих возможностей пытается оптимизировать два запроса. Это выгодно только в том случае, если эти запросы должны оптимизироваться без учета эффекта этой оптимизации на другие операции базы данных. Чтобы оптимизировать все индексы базы данных, неплохой идеей будет использование трассировки SQL Server Profiler, которую предоставляет Database Engine Tuning Advisor (Помощник по настройке ядра СУБД) с нормальной рабочей нагрузкой для всей базы данных. Благодаря этой информации Помощник по настройке ядра СУБД может оптимизировать запросы с другой рабочей нагрузкой в базе данных. После выполнения анализа рабочей нагрузки обязательно сохраните и просмотрите рекомендации.

Заключение

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

Из этой лекции вы узнали, как важно подобрать правильный тип индекса и как Помощник по настройке ядра СУБД может оказать помощь в разработке индекса.

Краткий справочник по 2 лекции

CREATE UNIQUE CLUSTERED INDEX 
<index_name> ON <table>(<column>)

CREATE [ UNIQUE ] NONCLUSTERED INDEX index_name ON <object> ( column [ ASC | DESC ] [ ,...n ] )

CREATE PRIMARY XML INDEX index_name ON <object> ( xml_column_name )

CREATE XML INDEX index_name 
ON <object> ( xml_column_name ) USING XML INDEX xml_index_name 
FOR { VALUE | PATH | PROPERTY }

ALTER INDEX <index> 
                     ON <table>.<column> 
                     REBUILD.