Как проверить, насколько сервер использует индексы? | OTUS
🔥 Начинаем BLACK FRIDAY!
Максимальная скидка -25% на всё. Успейте начать обучение по самой выгодной цене.
Выбрать курс

Курсы

Программирование
iOS Developer. Basic
-25%
Python Developer. Professional
-25%
Разработчик на Spring Framework
-25%
Golang Developer. Professional
-25%
Python Developer. Basic
-25%
iOS Developer. Professional
-25%
Highload Architect
-25%
JavaScript Developer. Basic
-25%
Kotlin Backend Developer
-25%
JavaScript Developer. Professional
-25%
Android Developer. Basic
-25%
Unity Game Developer. Basic
-25%
Разработчик C#
-25%
Программист С Web-разработчик на Python Алгоритмы и структуры данных Framework Laravel PostgreSQL Reverse-Engineering. Professional CI/CD Vue.js разработчик VOIP инженер Программист 1С Flutter Mobile Developer Супер - интенсив по Kubernetes Symfony Framework Advanced Fullstack JavaScript developer Супер-интенсив "Azure для разработчиков"
Инфраструктура
Мониторинг и логирование: Zabbix, Prometheus, ELK
-25%
DevOps практики и инструменты
-25%
Архитектор сетей
-25%
Инфраструктурная платформа на основе Kubernetes
-25%
Супер-интенсив «IaC Ansible»
-16%
Разработчик программных роботов (RPA) на базе UiPath и PIX
-25%
Administrator Linux. Professional MS SQL Server Developer Безопасность Linux PostgreSQL Reverse-Engineering. Professional CI/CD VOIP инженер Супер-практикум по работе с протоколом BGP Супер - интенсив по паттернам проектирования Супер - интенсив по Kubernetes Administrator Linux.Basic Супер-интенсив «ELK»
Специализации Курсы в разработке Подготовительные курсы
+7 499 938-92-02

Как проверить, насколько сервер использует индексы?

SQL_Deep_30.1_site-5020-d9bd6d.png

В этом нам поможет замечательная DMV sys.dm_db_index_usage_stats. В ней есть информация о том, сколько раз используется индекс при поиске (user_seeks, user_scans и user_lookups), и о том, сколько раз нужно обновить данные в индексе при изменении данных в таблице (user_updates).

Первое поле — коэффициент полезности индекса. Чем он выше, тем чаще индекс используется при поиске и реже при обновлениях. Осторожно, статистика использования индексов сбрасывается при рестарте сервера, поэтому если вы недавно перезапускали SQL Server, лучше подождите и дайте время накопиться статистике.

declare @dbid int
select @dbid = db_id()
select (cast((user_seeks + user_scans + user_lookups) as float) / case user_updates when 0 then 1.0 else cast(user_updates as float) end) * 100 as [%]
    , (user_seeks + user_scans + user_lookups) AS total_usage
    , objectname=object_name(s.object_id), s.object_id
    , indexname=i.name, i.index_id
    , user_seeks, user_scans, user_lookups, user_updates
    , last_user_seek, last_user_scan, last_user_update
    , last_system_seek, last_system_scan, last_system_update
    , 'DROP INDEX ' + i.name + ' ON ' + object_name(s.object_id) as [Command], i.*
from sys.dm_db_index_usage_stats s,
    sys.indexes i
where database_id = @dbid 
and objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
AND i.name IS NOT NULL
AND i.is_primary_key = 0        --исключаем Primary Key
AND i.is_unique_constraint = 0  --исключаем Constraints
--and object_name(s.object_id) = 'MyBigTable'
order by [%] asc

Я не рекомендую сразу идти и удалять индексы, которые не используются, но нужно обратить внимание на них и посмотреть кто, когда и зачем их создал.

Например, у меня в практике был индекс, по которому считался большой и важный отчёт. Он считался редко, 1 раз в месяц. Я, используя статистику, нашла этот огромный индекс вообще без использования, всё проверила, убедилась, что никто его не использует, и удалила.

А потом, настало время расчёта того самого отчёта. И сразу стало понятно, чей был этот огромный индекс. Благо, это был отчётный сервер, на котором индекс можно было создать, не повлияв на клиентов, но просыпаться для этого ночью всё же пришлось.

Поэтому лучше перепроверьте несколько раз, что ваш индекс точно не используется. И перед удалением возьмите таймаут хотя бы на 2 недели, вдруг что-нибудь выявится.

Появились вопросы? Пишите их в комментариях!

Не пропустите новые полезные статьи!

Спасибо за подписку!

Мы отправили вам письмо для подтверждения вашего email.
С уважением, OTUS!

Автор
0 комментариев
Для комментирования необходимо авторизоваться
🎁 Максимальная скидка!
Черная пятница уже в OTUS! Скидка -25% на всё!