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

Содержание:
1. Синтаксис (Вы читаете данный раздел);
2. Создание тестовой среды;
3. Сопоставление sys.dm_db_index_operational_stats и sys.dm_db_index_usage_stats.

---

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

Пусть термин «функция динамического управления» не вводит вас в заблуждение. Эти объекты похожи на другие функции 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 показан в коде ниже. Концепцию параметров шаблонов я описывал в одной из своих статей, но вы можете просто воспользоваться сочетанием клавиш Ctrl+Shift+M в SQL Server Management Studio (SSMS), когда встретите синтаксис вида , чтобы заменить местозаполнители нужными значениями.

SELECT * 
FROM sys.dm_db_index_operational_stats
(
        DB_ID(), 
        <object_id, if you want to limit to single table or NULL for ALL, NULL>, 
        <index_id,if you want to limit to single index or NULL for ALL, NULL>, 
        <partition_id, if you want to limit a partition or NULL for ALL, NULL>
);

Если оставить команду неизменной, вы получите результаты, охватывающие все объекты (индексы и кучи) и любые связанные индексы, независимо от ограничений конкретного раздела. Конечно, таким образом у вас появится огромный объем информации, но польза от нее невелика из-за отсутствия контекста для результатов. А вреда может быть немало: получить к ней доступ без проблем сможете любой опытный хакер, или обычный пользователь, установивший на ваш рабочий смартфон программу-шпион. Поэтому я всегда соединяю объекты DMO индексирования с другими системными представлениями, которые дают контекст для результатов (а также фильтруют возвращаемые строки наряду со столбцами, которые нужно увидеть). Системные представления для контекста следующие:
• sys.indexes — предоставляет информацию о ваших индексах SQL Server на уровне базы данных, в том числе имя, тип индекса (кластеризованный, некла-стеризованный), уникальность и др.
• sys.objects— можно задействовать системную функцию OBJECT_ NAME (objected), чтобы возвратить имя таблицы или представления, связанные с object id от sys.dm_db_index_operational_stats, но мне также придется фильтровать результат, так как нас интересуют только пользовательские объекты, а не системные таблицы и представления, используемые внутри SQL Server. Для этого необходим доступ к столбцу is_ms_shipped в sys.objects. Можно также возвратить имя объекта (имя столбца) и тип объекта (type_desc).

SELECT * 
FROM sys.dm_db_index_operational_stats
        (
                DB_ID(), 
                <object_id, if you want to limit to single table or NULL for ALL, NULL>, 
                <index_id,if you want to limit to single index or NULL for ALL, NULL>, 
                <partition_id, if you want to limit a partition or NULL for ALL, NULL>
        )
        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;

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

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

SELECT  
--IDENTIFICATION:
        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 ,

--LEAF LEVEL ACTIVITY:
    ixO.leaf_insert_count ,
    ixO.leaf_delete_count ,
    ixO.leaf_update_count ,
        ixO.leaf_page_merge_count ,
    ixO.leaf_ghost_count ,

--NON-LEAF LEVEL ACTIVITY:
    ixO.nonleaf_insert_count ,
    ixO.nonleaf_delete_count ,
    ixO.nonleaf_update_count ,
    ixO.nonleaf_page_merge_count ,

--PAGE SPLIT COUNTS:
    ixO.leaf_allocation_count ,
    ixO.nonleaf_allocation_count ,      

--ACCESS ACTIVITY:
    ixO.range_scan_count ,
    ixO.singleton_lookup_count ,
    ixO.forwarded_fetch_count ,

--LOCKING ACTIVITY:
    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 ,

--LATCHING ACTIVITY:
    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 ,

--COMPRESSION ACTIVITY:
    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;

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

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