Sql server: додавання статистики читання-запису і відповідна сортування

Мені зовсім не подобається займатися зайвими математичними викладками, хоча я і спеціалізувався на прикладній математиці в університеті. Тому мені б хотілося уникнути перенесення цих результатів в 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_writesFROM sys.dm_db_index_usage_stats AS ixUSINNER JOIN lifeboat.sys.objects AS SOON SO.object_id = ixUS.object_idINNER JOIN lifeboat.sys.indexes AS SION SI.object_id = ixUS.object_idAND SI.index_id = ixUS.index_idWHERE ixUS.database_id = DB_ID ( ``) ORDER BY 5, OBJECT_NAME (ixUS.object_id), ixUS.index_id - * // * ------ -------------------------------- Оновлений варіант коду, без JOINS і МАЦІ, для совме тимость з версіями SQL SERVER 2005- 2016 -------------------------------------- * / SELECTDB_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_updatesWHEN 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_updatesEND 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_writesFROMsys.dm_db_index_usage_stats AS ixUSINNER JOIN sys.indexes AS SION SI.object_id = ixUS. object_idAND SI.index_id = ixUS.index_idWHERE ixUS.database_id = DB_ID () ORDER BY [r_per_w], OBJECT_NAME (ixUS.object_id, IxUS.database_id), ixUS.index_id-

Відео: ORDER BY - Сортування в мові запитів Transact-SQL

При малих масштабах важко приймати якісь рішення. Але поглянемо на результати з виробничого середовища, на підставі яких можна зробити висновки негайно (імена бази даних, об`єктів і індексів змінені або видалені) (див. Скріншот нижче).


Це лише перші 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. Направляючи запити до цього динамічного адміністративному поданням, ви можете отримати статистичні дані про використання, розбиті за операціями чтенія- розділені на пошуки, перегляди та уточнюючі запроси- по оновлень (операціями запису). Дані зберігаються тільки між перезапуск служби, тому важливо враховувати наявність середовища, в якій процеси можуть відбуватися не так часто (раз на місяць або на рік), але при цьому вимагати більш частого обслуговування, пов`язаного з відключенням служб від мережі.