SQL Server: добавления статистики чтения-записи и соответствующая сортировка

Содержание:
1. Индексирующие объекты динамического управления;
2. Сохранение результатов из sys.dm_db_index_usage_stats;
3. Добавления статистики чтения-записи и соответствующая сортировка (Вы читаете данный раздел).

---

Мне совсем не нравится заниматься лишними математическими выкладками, хотя я и специализировался на прикладной математике в университете. Поэтому мне бы хотелось избежать переноса этих результатов в Microsoft Excel, где я выполняю основные вычисления, и получить значение отношения чтения-записи для индекса. Еще я хочу взглянуть на индексы, требующие трудоемкого обслуживания (результат— операции записи и затраты на перестроение и реорганизацию индексов), в сравнении с соответствующими преимуществами индексов при чтении (см. код ниже). Результаты исполнения кода представлены на скриншоте ниже.

/*
------------------------------------
Первоначальный вариант кода, он заключен в комментарии
------------------------------------

SELECT DB_NAME(ixUS.database_id) AS database__name
        , SO.name AS object__name 
        , SI.name AS index__name
        , ixUS.index_id 
        , (ixUS.user_seeks + ixUS.user_scans + ixUS.user_lookups) 
                / IIF(ixUS.user_updates = 0, 1, ixUS.user_updates) AS [r_per_w]
        , ixUS.user_seeks
        , ixUS.user_scans
        , ixUS.user_lookups
        , (ixUS.user_seeks + ixUS.user_scans + ixUS.user_lookups) AS total_reads
        , ixUS.user_updates AS total_writes
FROM sys.dm_db_index_usage_stats AS ixUS
        INNER JOIN lifeboat.sys.objects AS SO
                ON SO.object_id = ixUS.object_id
        INNER JOIN lifeboat.sys.indexes AS SI
                ON SI.object_id = ixUS.object_id
                        AND SI.index_id = ixUS.index_id
WHERE ixUS.database_id = DB_ID('<db_name,,>')
ORDER BY 5
        , OBJECT_NAME(ixUS.object_id), ixUS.index_id;
*/

/*
--------------------------------------
Обновленный вариант кода, без JOINS и МАКЕ,
для совместимости с версиями SQL SERVER 2005- 2016
--------------------------------------
*/

SELECT
 DB_NAME(ixUS.database_id) AS database__name
 , OBJECT_SCHEMA_NAME(SI.object_id, ixUS.database_id) AS schema__Name
, OBJECT_NAME(SI.object_id, ixUS.database_id) AS object__name
 , SI.name AS index__name
 , ixUS.index_id
 , CASE ixUS.user_updates
        WHEN NULL THEN (ixUS.user_seeks + ixUS.user_scans + ixUS.user_lookups)
        WHEN 0 THEN (ixUS.user_seeks + ixUS.user_scans + ixUS.user_lookups)
        ELSE
                (ixUS.user_seeks + ixUS.user_scans + ixUS.user_lookups) / ixUS.user_updates
        END AS [r_per_w]
 , ixUS.user_seeks
 , ixUS.user_scans
 , ixUS.user_lookups
 , (ixUS.user_seeks + ixUS.user_scans + ixUS.user_lookups) AS total_reads
 , ixUS.user_updates AS total_writes
 FROM
 sys.dm_db_index_usage_stats AS ixUS
 INNER JOIN sys.indexes AS SI
 ON SI.object_id = ixUS.object_id
 AND SI.index_id = ixUS.index_id
 WHERE ixUS.database_id = DB_ID()
 ORDER BY [r_per_w]
        , OBJECT_NAME(ixUS.object_id, IxUS.database_id)
        , ixUS.index_id;

При малых масштабах трудно принимать какие-то решения. Но взглянем на результаты из производственной среды, на основании которых можно сделать выводы немедленно (имена базы данных, объектов и индексов изменены или удалены) (см. скриншот ниже).


Это лишь первые 22 результата из более чем 60, возвращенных запросом к базе данных FOO. Какие выводы можно сделать из этих результатов? Ниже приводится типичный процесс анализа результатов первого прохода.

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

2. Я оценил активность в кучах (таблицах без кластеризованных индексов). Вероятно, второй запрос будет выполнен, чтобы посмотреть на все действия с некластеризованными индексами и кучами, чтобы определить, есть ли потребность в кластеризованном индексе, а также исследовать кандидатов для ключа кластеризации. Кучи можно определить по значению index _name, равному NULL, а также значению index_id, равному 0.

3. Затем я начинаю анализировать любые таблицы со значительным числом индексов, чтобы выяснить, можно ли их сократить. Иногда полезно запустить этот запрос во второй раз, изменив предложение ORDER BY для сортировки сначала по object_name, а затем по index_id, чтобы исследовать результаты для этой метрики.

4. Если необходимо улучшить ключ кластеризации, я исследую таблицы, где активность чтения по ключу кластеризации выглядит низкой по сравнению с обновлениями. Этот случай (и как настроить запрос, чтобы упростить данный процесс) будет рассмотрен в следующей статье. Подводим итог: sys.dm_db_index_ usage_stats — главный репозиторий показателей использования индекса (и кучи) для баз данных SQL Server. Направляя запросы к этому динамическому административному представлению, вы можете получить статистические данные об использовании, разбитые по операциям чтения; разделенные на поиски, просмотры и уточняющие запросы; по обновлениям (операциям записи). Данные сохраняются только между перезапусками службы, поэтому важно учитывать наличие среды, в которой процессы могут происходить с меньшей частотой (раз в месяц или в год), но при этом требовать более частого обслуживания, связанного с отключением служб от сети.