Динамическое управление сбором статистики работы индекса

Информация об использовании индекса требуется нам, когда приходится анализировать коэффициент заполнения, разбиение страниц, сжатие, распространение блокировки и настройки производительности. В новой серии статей речь пойдет о том, каким образом типовые операции в графических планах выполнения преобразуются в хранимые метаданные об использовании индекса в двух динамических объектах управления: dm_db_index_operational_stats и dm_db_index_usage_stats. Мы будем учиться использовать динамические объекты управления для упреждающей и последующей настройки экземпляров SQL Server с применением dm_db_index_operational_stats.

Microsoft SQL Server предоставляет различные методы для доступа к внутренним метаданным: представления совместимости системы, системные представления, представления INFORMATION_SCHEMA, профилировщик, расширенные события, объекты динамического управления и т. д.

На мой взгляд, лучший метод реализуют объекты динамического управления. За многие годы я написал несметное количество статей на эту тему, но по-прежнему продолжаю находить интересные способы применения подобных внутренних конструкций. В статьях этой серии я расскажу об одном из самых примечательных среди этих объектов: sys.dm_db_index_operational_stats.

Способы доступа к индексам,блокирование, кратковременные блокировки и сжатие

Объект sys.dm_db_index_operational_stats содержит информацию о многих аспектах доступа к индексам и кучам (таблицы без кластеризованного индекса) в ходе вызовов, выполняемых конечными пользователями баз данных. В частности, можно получить следующие данные.

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

— вставки;

— удаления;

— обновления;

— фантомные строки (только конечный уровень).

• Разбиения страниц.
• Блокирование:

— счетчики;

— счетчики времени ожидания;

— время ожидания.

• Кратковременные блокировки:

— счетчики;

— счетчики времени ожидания;

— время ожидания.

• Активность доступа:

— просмотры диапазонов;

— одноэлементный уточняющий запрос;

— выборки вперед.

• Сжатие страниц:

— попытки;

— успешные выполнения.

С помощью единственной системной функции можно принимать решения относительно оптимального коэффициента заполнения (в какой мере заполняются конечные страницы индекса по умолчанию) и обнаруживать индексы, приводящие к многочисленным блокировкам, в том числе кратковременным, если анализ статистических данных об ожидании покажет, что ожидания — главная причина снижения производительности. Эта функция динамического управления (DMF) также сочетается с другими динамическими объектами управления (DMO), поэтому картина индексов SQL Server отличается большим разнообразием. В первой статье данной серии мы рассмотрим сведения, предоставляемые sys.dm_db_index_usage_stats, динамическим административным представлением (DMV), подробно описанным мною в книге об объектах DMO (http://www.amazon.com/Performance-Tuning-Server-Dynamic-Management/dp/1906434476).

Синтаксис

Пусть термин «функция динамического управления» не вводит вас в заблуждение. Эти объекты похожи на другие функции SQL Server. Вы запрашиваете результаты через инструкцию SELECT, передавая один или несколько параметров. Результаты выдаются в форме набора, возвращающего табличные значения: в виде одной или нескольких строк с несколькими столбцами в строке. Как было показано выше, набор результатов может быть очень широким, если запросить все столбцы. В данной статье я откажусь от возвращения всех столбцов и остановлюсь лишь на важных для рассматриваемой темы. Желающие увидеть полный список столбцов и исчерпывающее объяснение их назначения могут посетить сайт Microsoft, содержащий официальную документацию по sys.dm_db_index_operational_stats (https://msdn.microsoft.com/en-us/library/ms174281.aspx).

Синтаксис вызова sys.dm_db_index_operational_stats показан в листинге 1. Концепцию параметров шаблонов я описывал в одной из своих статей, но вы можете просто воспользоваться сочетанием клавиш Ctl+Shift+M в SQL Server Management Studio (SSMS), когда встретите синтаксис вида <какой_нибудь_параметр, описание, значение_по_умолчанию>, чтобы заменить местозаполнители нужными значениями.

Если оставить команду неизменной, вы получите результаты, охватывающие все объекты (индексы и кучи) и любые связанные индексы, независимо от ограничений конкретного раздела. Конечно, таким образом у вас появится огромный объем информации, но польза от нее невелика из-за отсутствия контекста для результатов. Поэтому я всегда соединяю объекты DMO индексирования с другими системными представлениями, которые дают контекст для результатов (а также фильтруют возвращаемые строки наряду со столбцами, которые нужно увидеть). Системные представления для контекста следующие:

• sys.indexes — предоставляет информацию о ваших индексах SQL Server на уровне базы данных, в том числе имя, тип индекса (кластеризованный, некластеризованный), уникальность и др.
• sys.objects — можно задействовать системную функцию OBJECT_NAME (object_id), чтобы возвратить имя таблицы или представления, связанные с object_id от sys.dm_db_index_operational_stats, но мне также придется фильтровать результат, так как нас интересуют только пользовательские объекты, а не системные таблицы и представления, используемые внутри SQL Server. Для этого необходим доступ к столбцу is_ms_shipped в sys.objects. Можно также возвратить имя объекта (имя столбца) и тип объекта (type_desc).

Получаем следующую базовую структуру, представленную в листинге 2.

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

Читая далее, обратите внимание, что я уже заменил параметры шаблона.

Очевидно, мы не будем получать все столбцы из этих системных объектов. В следующих статьях вы заметите постепенный отход от методологии SELECT *; это позволит сосредоточиться только на столбцах, важных для рассматриваемой темы.

В первой статье я продемонстрирую переход от запросов и планов выполнения к метаданным, собранным из sys.dm_db_index_operational_stats. Учитывая, что я уделил внимание сопутствующему динамическому административному представлению sys.dm_db_index_usage_stats, нам предстоит сравнить и сопоставить это действие, широко применяемое и здесь. Затем мы углубимся в возможные варианты использования sys.dm_db_index_operational_stats для диагностики ожидания блокировок и кратковременных блокировок, познакомимся со случаями, когда полезно изменить коэффициенты заполнения, изучим укрупнение блокировок страниц и выберем подходящих кандидатов для сжатия страниц. Но прежде всего важно понять, как операции отражаются на метриках в DMF.

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

Создание тестовой среды

В развитие темы обработки данных в SQL на проводимых мною занятиях (http://www.sqlcruise.com/) создайте небольшую тестовую базу данных, именуемую lifeboat, с простой моделью восстановления (100 Мбайт будет достаточно). Затем запустите запрос, представленный в листинге 4, чтобы создать малую таблицу с 8 строками и кластеризованным и некластеризованным индексом.

На данном этапе вы сможете выполнить запрос из листинга 5 и выяснить, что не возвращено никаких результатов. Это означает, что в базе данных lifeboat еще не было активности. Вот почему, в частности, мне пришлось создать новую базу данных (если вам захочется поэкспериментировать дома). Порой у вас не будет возможности перезапустить службы SQL Services, как это делаю я, чтобы имитировать чистый набор метрик.

Сопоставление sys.dm_db_index_operational_stats и sys.dm_db_index_usage_stats

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

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

В этой серии тестов нас интересуют результаты, предоставляемые через следующие столбцы в sys.dm_db_index_operational_stats:

• range_scan_count;
• singleton_lookup_count (далее в этой статье);
• row_lock_count;
• page_lock_count;
• page_io_latch_wait_count.

Рассматривая динамическое административное представление sys.dm_db_index_usage_stats, мы сосредоточимся на следующих столбцах:

• user_seeks;
• user_scans;
• user_lookups;
• user_writes.

Запрос 1: обновления индекса

Сразу хочу обратить ваше внимание на «темную сторону индексации»: негативное влияние добавления новых строк с помощью операций INSERT и изменения значений в результате операций UPDATE и DELETE. У нас есть пустая таблица и пора добавить в нее несколько значений. Выполните следующую команду:

INSERT INTO lifeboat.[dbo].[DatabaseTypes]
   (DBTypeID, DBType)
VALUES (1, ‘System’), (2, ‘User’),
        (3, ‘Sample’), (4, ‘Archive’),
        (5, ‘Test’), (6, ‘Stage’),
        (7, ‘QA’), (8, ‘Dev’);

План выполнения должен иметь вид, как на рисунке 1.

Рисунок 1. План выполнения

Теперь можно увидеть последствия вставки восьми значений в таблицу в обоих объектах DMO (см. листинг 6 и рисунок 2).

Рисунок 2. Вставка 8 значений в таблицу

Регистрируется восемь отдельных вставок наряду с блокировками связанных строки и страницы и вспомогательным кратковременным блокированием. Связанное значение page_io_latch_wait_in_ms было равно 0, то есть ожидание кратковременных блокировок составляет менее миллисекунды и потому статистически незначительно. Отсюда можно сделать вывод о дополнительных затратах на индексацию в базах данных SQL. Каждый индекс, добавляемый ради повышения производительности операций чтения, негативно влияет на операции записи. Каждую операцию записи необходимо зарегистрировать во всех индексах.

У тех, кто читал мои статьи о динамическом административном представлении (DMV) sys.dm_db_index_usage_stats (или ixU), может возникнуть вопрос, каким образом sys.dm_db_index_operational_stats (или ixO) различаются в сравнении, поскольку мы также собираем статистику применения. В обоих случаях собирается информация об использовании, но учтите, что операционная DMF (ixO) предоставляет метрики с позиций уровня в сбалансированном дереве индекса, тогда как DMV использования (ixU) предоставляет информацию с позиций самого индекса. Эти восемь строк, зарегистрированные как восемь отдельных вставок конечных элементов, представлены единственной операцией записи индекса с позиций sys.dm_db_index_usage_stats (см. листинг 7 и рисунок 3).

Рисунок 3. Единственная операция записи индекса

Запрос 2: просмотры индекса

Подобно поведению, наблюдаемому для операций вставки-записи, мы можем оценить и влияние просмотров. Я выполню 10 операций чтения, использующих следующий запрос, который выполнит этот оператор SELECT последовательно 10 раз:

SELECT DBTypeID ,
       DBType
FROM lifeboat.dbo.DatabaseTypes;
GO 10

Запрос выполняет просмотр некластеризованного индекса, как видно на плане выполнения (см. рисунок 4).

Рисунок 4. Просмотр некластеризованного индекса

Направляя запросы к объектам DMO sys.dm_db_index_operational_stats и sys.dm_db_index_usage_stats, мы видим расхождение в результатах двух объектов (см. листинг 8 и рисунки 5 и 6).

Рисунок 5. Содержимое sys.dm_db_index_operational_stats

Рисунок 6. Содержимое .dm_db_index_usage_stats

Взгляните на индивидуальные сканирования диапазонов по некластеризованному индексу. Обратите внимание: это отдельные запросы к базе данных и они регистрируются как таковые в sys.dm_db_index_usage_stats. Это один из немногих случаев, когда обе конструкции дают похожие результаты. Ситуация меняется при переходе к поискам.

Запрос 3: поиск в индексе

Можно было предположить, что мы сможем наблюдать последствия поиска в индексе таким же образом, как в случае с просмотрами. На этот раз придется использовать гораздо больший индекс, поскольку тот, с которым мы работаем, настолько мал, что оптимизатор запросов отдает предпочтение просмотру. Сравните следующий план выполнения (базовый запрос не имеет значения для данной темы) с результатами sys.dm_db_index_operational_stats и sys.dm_db_index_usage_stats для перезапущенного экземпляра SQL Server, у которого все предыдущие значения для этой DMF были равны 0 при использовании точно таких же диагностических запросов, как показанные выше. Это простая операция поиска в индексе, что видно из приведенного на рисунке 7 плана выполнения.

Рисунок 7. Простая операция поиска в индексе

На первый взгляд результаты немного неожиданные для DMF рабочей статистики, но только потому, что я опустил важный элемент информации (см. рисунки 8 и 9).

Рисунок 8. Содержимое sys.dm_db_index_operational_stats

Рисунок 9. Содержимое sys.dm_db_index_usage_stats

Поначалу не видно никаких различий между поисками (seek) и просмотрами (scan) в sys.dm_db_index_operational_stats. Операция поиска в индексе записывается как один просмотр диапазона внутри этой DMF в данном случае. С другой стороны, выполняются ожидания относительно sys.dm_db_index_usage_stats. В этом системном представлении данная операция регистрируется как user_seek. Как вы думаете, почему это происходит?

Есть еще одно обстоятельство, которое я пока не отмечал: этот запрос возвращает многочисленные результаты. Кроме того, результаты могут быть получены за один просмотр диапазона. Поэтому метаданные, представленные в sys.dm_db_index_operational_stats, точно такие, как мы ожидали. Да, операция была поиском в индексе, как показано в плане выполнения, однако на самом деле, внутренне, это просмотр диапазона. Поэтому возникает вопрос: как правильно отражать влияние в DMV статистики использования — как поиск или просмотр?

И наоборот, если выполнить запрос, который возвращает единственную строку, план выполнения выглядит идентично во всем, кроме имени индекса в качестве целевого объекта операции. Однако операция проявляется как поиск в sys.dm_db_index_operational_stats, а не просмотр диапазона (см. рисунок 10).

Рисунок 10. Операция проявляется как поиск, а не просмотр диапазона

Запрос 4: уточняющие запросы

До сих пор мы рассматривали операции записи/обновления, а также поиска и просмотра. Но есть еще одна заслуживающая внимания операция чтения: уточняющие запросы (lookups). Уточняющие запросы выполняются, когда результаты возвращаются с использованием некластеризованного индекса, но не все столбцы являются частью или включенным столбцом некластеризованного индекса. Операция получает столбцы, которые являются частью некластеризованного индекса, а затем выполняет уточняющий запрос строки к данным, чтобы получить остальные столбцы, необходимые для удовлетворения запроса. В приведенном на рисунке 11 плане запроса видно, что сделан запрос, подготовлены столбцы из некластеризованного индекса (который в данном случае представляет собой уникальный некластеризованный индекс), а затем, поскольку кластеризованный индекс отсутствует, выполняется уточняющий запрос RID, чтобы удовлетворить запрос.

Рисунок 11. Запрос с уточняющим запросом RID

На этот раз мы получаем результаты, более близкие к ожидаемым, как из sys.dm_db_index_operational_stats, так и из sys.dm_db_index_usage_stats (см. рисунки 12 и 13).

Рисунок 12. Содержимое sys.dm_db_index_operational_stats

Рисунок 13. Содержимое sys.dm_db_index_usage_stats

DMF рабочей статистики представляет уточняющий запрос RID как одноэлементный уточняющий запрос для некластеризованного индекса для сбора данных из единственной возвращенной строки из индекса и другого одноэлементного уточняющего запроса для кучи, чтобы завершить процесс сбора данных в соответствии с запросом. Существуют также связанные блокировки страниц, применяемые как к некластеризованному индексу, так и к куче, а также блокировка строк в некластеризованном индексе. Между тем DMV статистики использования представляет действия выполненного запроса как поиск с применением некластеризованного индекса с последующим уточняющим просмотром в куче.

К чему приводит наличие кластеризованного индекса для таблицы? Это зависит от того, можно ли удовлетворить запрос с использованием комбинации некластеризованного индекса и ключей кластеризации.

Если запрошенные столбцы могут быть получены с помощью комбинации некластеризованного и кластеризованного индексов, то перед нами простой поиск в некластеризованном индексе, так как некластеризованный индекс неявно включает ключи кластеризации. Если же комбинация индексов не охватывает все запрошенные столбцы, то результаты в обоих объектах DMO идентичны показанным выше; единственное исключение — поиск выполняется по кластеризованному индексу, как при поиске ключа, а не по куче, как при поиске RID.

Я надеюсь, эта статья поможет вам понять, как выполнение запроса отражается в sys.dm_db_index_operational_stats и sys.dm_db_index_usage_stats. В следующих статьях серии мы воспользуемся этими фундаментальными правилами при использовании функции динамического управления sys.dm_db_index_operational_stats в качестве инструмента для упреждающей и последующей настройки производительности.

SELECT *
FROM sys.dm_db_index_operational_stats
(
        DB_ID(),
        ,
        ,
        
);

SELECT *
FROM sys.dm_db_index_operational_stats
        (
                DB_ID(),
                ,
                ,
                
        )
        INNER JOIN sys.indexes I
                ON ixO.object_id = I.object_id
                        AND ixO.index_id = I.index_id
        INNER JOIN sys.objects AS sO
                ON sO.object_id = ixO.object_id
WHERE sO.is_ms_shipped = 0;

SELECT
--ИДЕНТИФИКАЦИЯ:
        DB_NAME(ixO.database_id) AS database__name,
        O.name AS object__name,
        I.name AS index__name,
        I.type_desc AS index__type,
    ixO.index_id ,
    ixO.partition_number ,

--АКТИВНОСТЬ КОНЕЧНОГО УРОВНЯ:
    ixO.leaf_insert_count ,
    ixO.leaf_delete_count ,
    ixO.leaf_update_count ,
        ixO.leaf_page_merge_count ,
    ixO.leaf_ghost_count ,

-- АКТИВНОСТЬ НЕКОНЕЧНОГО УРОВНЯ:
    ixO.nonleaf_insert_count ,
    ixO.nonleaf_delete_count ,
    ixO.nonleaf_update_count ,
    ixO.nonleaf_page_merge_count ,

--СЧЕТЧИКИ РАЗБИЕНИЙ СТРАНИЦ:
    ixO.leaf_allocation_count ,
    ixO.nonleaf_allocation_count ,

--АКТИВНОСТЬ ДОСТУПА:
    ixO.range_scan_count ,
    ixO.singleton_lookup_count ,
    ixO.forwarded_fetch_count ,

--АКТИВНОСТЬ БЛОКИРОВАНИЯ:
    ixO.row_lock_count ,
    ixO.row_lock_wait_count ,
    ixO.row_lock_wait_in_ms ,
    ixO.page_lock_count ,
    ixO.page_lock_wait_count ,
    ixO.page_lock_wait_in_ms ,
    ixO.index_lock_promotion_attempt_count ,
    ixO.index_lock_promotion_count ,

--АКТИВНОСТЬ КРАТКОВРЕМЕННОГО БЛОКИРОВАНИЯ:
    ixO.page_latch_wait_count ,
    ixO.page_latch_wait_in_ms ,
    ixO.page_io_latch_wait_count ,
    ixO.page_io_latch_wait_in_ms ,
    ixO.tree_page_latch_wait_count ,
    ixO.tree_page_latch_wait_in_ms ,
    ixO.tree_page_io_latch_wait_count ,
    ixO.tree_page_io_latch_wait_in_ms ,

--АКТИВНОСТЬ СЖАТИЯ:
    ixO.page_compression_attempt_count ,
    ixO.page_compression_success_count
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ixO
        INNER JOIN sys.indexes I
                ON ixO.object_id = I.object_id
                        AND ixO.index_id = I.index_id
        INNER JOIN sys.objects AS O
                ON O.object_id = ixO.object_id
WHERE O.is_ms_shipped = 0;

USE [lifeboat]
GO

CREATE TABLE [dbo].[DatabaseTypes]
        (
                [DBTypeID] [smallint] NOT NULL,
                [DBType] [varchar](20) NOT NULL
        );

ALTER TABLE [dbo].[DatabaseTypes] ADD  CONSTRAINT [PK_DatabaseTypes] PRIMARY KEY CLUSTERED
        (
                [DBType] ASC
        )
WITH
        (
                PAD_INDEX = OFF,
                STATISTICS_NORECOMPUTE = OFF,
                SORT_IN_TEMPDB = OFF,
                IGNORE_DUP_KEY = OFF,
                ONLINE = OFF,
                ALLOW_ROW_LOCKS = ON,
                ALLOW_PAGE_LOCKS = ON,
                FILLFACTOR = 90
        );

CREATE UNIQUE NONCLUSTERED INDEX [NC_DatabaseTypes_DBTypeID] ON [dbo].[DatabaseTypes]
        (
                [DBTypeID] ASC
        )
WITH
        (
                PAD_INDEX = OFF,
                STATISTICS_NORECOMPUTE = OFF,
                SORT_IN_TEMPDB = OFF,
                IGNORE_DUP_KEY = OFF,
                ONLINE = OFF,
                ALLOW_ROW_LOCKS = ON,
                ALLOW_PAGE_LOCKS = ON,
                FILLFACTOR = 100
        );

SELECT *
FROM sys.dm_db_index_operational_stats (DB_ID(), NULL, NULL, NULL) AS ixO
        INNER JOIN sys.indexes I
                ON ixO.object_id = I.object_id
                        AND ixO.index_id = I.index_id
        INNER JOIN sys.objects AS O
                ON O.object_id = ixO.object_id
WHERE O.is_ms_shipped = 0;

SELECT
-- ИДЕНТИФИКАЦИЯ:
        DB_NAME(ixO.database_id) AS database__name,
        O.name AS object__name,
        I.name AS index__name,
        I.type_desc AS index__type,

--АКТИВНОСТЬ КОНЕЧНОГО УРОВНЯ:
    ixO.leaf_insert_count ,

--АКТИВНОСТЬ БЛОКИРОВАНИЯ:
    ixO.row_lock_count ,
    ixO.page_lock_count ,

--АКТИВНОСТЬ КРАТКОВРЕМЕННОГО БЛОКИРОВАНИЯ:
    ixO.page_io_latch_wait_count

FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ixO
        INNER JOIN sys.indexes I
                ON ixO.object_id = I.object_id
                        AND ixO.index_id = I.index_id
        INNER JOIN sys.objects AS O
                ON O.object_id = ixO.object_id
WHERE O.is_ms_shipped = 0;

SELECT O.name AS object__name,
        I.name AS index__name,
        I.type_desc AS index__type,
        ixU.user_seeks + ixU.user_scans + ixU.user_lookups AS total_user_reads,
        ixU.user_updates AS total_user_writes
FROM sys.dm_db_index_usage_stats AS ixU
        INNER JOIN sys.indexes AS I
                ON ixU.index_id = I.index_id
                        AND ixU.object_id = I.object_id
        INNER JOIN sys.objects AS O ON ixU.object_id = O.object_id
WHERE ixU.database_id = DB_ID()
ORDER BY ixU.index_id ASC;

SELECT
--ИДЕНТИФИКАЦИЯ:
        DB_NAME(ixO.database_id) AS database__name,
        O.name AS object__name,
        I.name AS index__name,
        I.type_desc AS index__type,

--АКТИВНОСТЬ КОНЕЧНОГО УРОВНЯ:
    ixO.leaf_insert_count ,
        ixO.range_scan_count,

--АКТИВНОСТЬ БЛОКИРОВАНИЯ:
    ixO.row_lock_count ,
    ixO.page_lock_count ,

--АКТИВНОСТЬ КРАТКОВРЕМЕННОГО БЛОКИРОВАНИЯ:
        ixO.page_io_latch_wait_count

FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS ixO
        INNER JOIN sys.indexes I
                ON ixO.object_id = I.object_id
                        AND ixO.index_id = I.index_id
        INNER JOIN sys.objects AS O
                ON O.object_id = ixO.object_id
WHERE O.is_ms_shipped = 0;

SELECT O.name AS object__name,
        I.name AS index__name,
        I.type_desc AS index__type,
        ixU.user_seeks,
        ixU.user_scans,
        ixU.user_lookups,
        ixU.user_updates AS total_user_writes
FROM sys.dm_db_index_usage_stats AS ixU
        INNER JOIN sys.indexes AS I
                ON ixU.index_id = I.index_id
                        AND ixU.object_id = I.object_id
        INNER JOIN sys.objects AS O ON ixU.object_id = O.object_id
WHERE ixU.database_id = DB_ID()
ORDER BY ixU.index_id ASC;