База знаний Антона Сидорова

База знаний по аналитике, ИТ архитектуре, API. Документирование. Тесты инструментов.

Project maintained by daemon110282 Hosted on GitHub Pages — Theme by mattgraham

Индексы

Индексы

Термины

Кластеризованный индекс
- сортирует и хранит строки данных таблицы или представления в порядке, определяемом ключом кластеризованного индекса
- реализуется в виде сбалансированного дерева, которое поддерживает быстрое получение строк по значениям ключа кластеризованного индекса.
Некластеризованный индекс
- можно определить в таблице или представлении вместе с кластеризованным индексом или в куче
- Каждая строка некластеризованного индекса содержит некластеризованное ключевое значение и указатель на строку. Этот указатель определяет строку данных кластеризованного индекса или кучи, содержащую ключевое значение.
Кучей
- является таблица без кластеризованного индекса. Для таблиц, сохраненных как куча, может быть создан один или несколько некластеризованных индексов. Данные хранятся в куче без указания порядка.

Паттерны

Не все индексы одинаково полезны. При разработке индексов необходимо учитывать их селективность
- Sorts can be limited with index usage. That is, a certain sort order is supported by an index that is sorted the same way, either ascending or descending.
Using sys.dm_db_index_physical_stats in a script to
- rebuild
- or reorganize indexes (no partitions / SQL Server 2005)
Избыток индексов может увеличить io wait
De-fragmentation of Index can help as more data can be obtained per page. (Assuming close to 100 fill-factor)
- ALTER INDEX REBUILD блокирует таблицу на время выполнения
Измените подходящие для Вашего сервера опции ONLINE , SORT_IN_TEMPDB, MAXDOP=10
- Помним про 3-х повышение производительности при использовании в 2012 и в 2014 SORT_IN_TEMPDB=ON SQL Server 2014. TEMPDB Hidden Performance Gem
Анализ плана выполнения после применения индексов: Scan, Seek, Lookup

Удаление не используемых индексов

Unused index

Влияет на объем БД, скорость операций чтения, изменения данных
- для уменьшения физического объема БД необходимо (место после удаления попадает в unalocated область БД и используется уже данными)
  - ALTER TABLE myHeap REBUILD;
  - for clustered indexes: ALTER INDEX IX_myIndex ON myTable REBUILD;
sys.dm_db_index_usage_stats
- условия отбора по UserSeek и UserScans и UserLookups почти везде нули, что означает, что индексы не используются СУБД для работы, количество же вставок UserUpdates в них очень велико
- перезапуск службы SQL Server сбрасывает данные в sys.dm_db_index_usage_stats

Missing Index

Упрощенное средство для поиска отсутствующих некластеризованных индексов, которые могут значительно повысить производительность запросов:

имеет ограничения и нужно тестировать предложения
- Предложения отсутствующих индексов не являются точными инструкциями по созданию индексов.
- msqsql dmv сбрасываются при перезапуске SQL Server
параметры
- длительность выполения запроса - elapsed_time
- длительность обработки запроса CPU - cpu_time
- длительность ожидания обработки запроса - wait_time = elapsed_time - cpu_time
- user_seeks
- user_scans
- логических операций чтения - logical_reads
- логических операций записи - logical_writes
- общая стоимость запроса для пользователя - avg_total_user_cost
- эффект на пользователя - avg_user_impact
- ожидаемый эффект - estimated_improvement = avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)

Критерии проранжированы по сумме 4х значений:

кол-во использования запросов uses
предполагаемый импакт от будущего индекса Impact
стоимость запроса - кол-во ЦПУ затрачиваемого на один запрос Avg query cost
score

Предложения отсутствующих индексов в планах выполнения могут храниться для этих событий благодаря хранилищу запросов Query Store.

Проверить изменения индексов можно с помощью хранилища запросов Query Store для обнаружения запросов по отсутствующим индексам.