Содержание:
1. Синтаксис;
2. Создание тестовой среды;
3. Сопоставление 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.

Если вы уже сейчас ничего не понимаете, тогда нет смысла продолжать читать статью: дальше будет еще сложнее. Поэтому лучше сначала пройдете профильное обучение - подробности на www.kbtu.kz/ru/fit/undergraduate/ac (http://www.kbtu.kz/ru/fit/undergraduate/ac).

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



Сразу хочу обратить ваше внимание на «темную сторону индексации»: негативное влияние добавления новых строк с помощью операций 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');

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

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,

--LEAF LEVEL ACTIVITY:
    ixO.leaf_insert_count ,

--LOCKING ACTIVITY:
    ixO.row_lock_count ,
    ixO.page_lock_count ,

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

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

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;

У тех, кто читал мои статьи о динамическом административном представлении (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 (см. код выше и рисунок ниже).

---

Читать дальше...