MSSQL: тепловые диаграммы индексов в виде TreeView

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

Подготовка окружения

Как всегда, мы будем вытаскивать данные с production, где, скорее всего, ставить ничего нельзя. Поэтому все делаем на нашем компе, а данные будет переносить с сервера.

У вас должен стоять Python с Kinter. Поставьте пакеты plotly, numpy, pandas и kaleido. Далее из репозитория https://github.com/tzimie/sqlpig скачайте heatmap.sql и heatmap.py.

Stored procedure из heatmap.sql надо поставить на целевом сервере, в «технической» базе (которая есть повсюду, как только их не называют).

Создание диаграммы

Вызовите процедуру на целевом сервере. Результат сохраните (Save result to CSV) и передайте на ваш компьютер. Интерфейс программы на питоне такой:

Например, если мы имеем size.csv то запускаем:

В браузере получаем знакомую всем TreeView в палитре thermal, причем цвета показывают «температуру» по логарифму одной из метрик (seeks, scans, updates). Но в tooltip показываются все значения.

Диаграмма «живая» и кликабельна. Она сохраняет это свойство, если ее в браузере сохранить в файл(ы) как страницу целиком.

Что можно выяснить с помощью этой диаграммы?

Напомню, что данные в sys.dm_db_index_usage_stats заполняются с момента старта сервера. После его рестарта выдержите время чтобы собралась релевантная статистика.

Если у вас есть AlwaysOn с вторичными нодами, с которых можно читать, то возможно, что есть индексы бесполезные на первичной ноде, но полезные на вторичной (такое бывает, например, если первичная нода OLAP а вторичная OLTP/Reporting)

Seeks показывают степень активности таблиц. Если индекс в таблице черный (seeks=0) то смотрите scans. Если оба значения около нуля, то индекс (почти) не используется.

Scans наоборот — если таблица в OLTP системе «горячая» по scans, то скорее всего индексов не хватает.

Наконец updates показывает все index updates — SQL вынужден отражать изменения данных во всех индексах, в том числе и ненужных. Черные, холодные таблицы — скорее всего архив. Можно рассмотреть вопрос о целесообразности их хранения