SQL Server. Корисні запити для обслуговування баз даних

Автоматичне керування індексами та статистикою
learn.microsoft.com/...​xes?view=sql-server-ver16

Активні запити до Sql-сервера

Іноді буває корисно подивитись, які запити в сервера перебувають «в роботі». Особливо ті, що виконуються довгий час.
Запит повертає перелік запитів, які зараз обробляє SQL Server

Індекси, яких не вистачає в БД

Дуже зручний запит, який покаже, яких індексів не вистачає при виконанні «важких» запитів, або легких, але тих, що викликаються дуже часто, і потребують індексації.
SELECT migs.group_handle,
mid.*,
’CREATE NONCLUSTERED INDEX IX_’
+ Ltrim(Str(group_handle)) + ’ ON ’
+ mid.statement + ’ (’ + CASE WHEN equality_columns IS NULL THEN ’’ ELSE
equality_columns + ’ ’ END + CASE WHEN inequality_columns IS NULL THEN ’’
ELSE
’, ’+ inequality_columns END + ’)’ + CASE WHEN included_columns IS NULL
THEN ’’
ELSE ’ INCLUDE (’ + included_columns +’) ’ END AS create_index_script,
avg_total_user_cost,
user_seeks
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON ( migs.group_handle = mig.index_group_handle )
INNER JOIN sys.dm_db_missing_index_details AS mid
ON ( mig.index_handle = mid.index_handle )
ORDER BY avg_total_user_cost * avg_user_impact * ( user_seeks + user_scans ) DESC

Запит покаже, як часто виникала потреба в певному індексі, скільки «коштувало» виконання запитів без цього індекса, які поля повинні бути задіяні в індексі.
Особливо цікавим є поле create_index_script, який одразу може створити потрібний індекс.
Майте на увазі, що індекс, хоча і прискорює обчислення та виборки, не є безкоштовним. Індекс належить до інструментів прискорення обчислень типу «заготівля результатів», тому потребує додаткового простору і призводить до зростання розміру бази даних. Тому потрібно відчуття балансу. Особливо це стосується «листових» індексів, які включають в себе не лише поля, по яких відбувається відбір, а ще й поля зі значеннями, які одразу потрапляють у результати запиту. Можна сказати, що грамотна побудова індексів — це ціле мистецтво, і перш за все мистецтво балансу між швидкодією та розміром даних.

Перелік індексів бази даних, які давно не використовувались

Інструмент, який навпаки прибирає «зайві» індекси, які або створені неоптимально, або залишились після того, як певні запити до бази даних втратили актуальність (наприклад, через нову версію програмного забезпечення, яке такі запити викликає).
Зайві індекси, по перше «з’їдають» місце, по друге, додавання та оновлення даних в базі потребує перебудови індексів під час запису, отже зменшує швидкодію.

SELECT
OBJECT_SCHEMA_NAME(i.object_id) AS [Schema Name],
OBJECT_NAME(i.object_id) AS [Table Name],
i.name AS [Index Name],
i.index_id,
i.type_desc AS [Index Type],
i.is_unique,
i.is_primary_key,
i.is_unique_constraint,
i.is_disabled,
ISNULL(s.user_seeks, 0) AS user_seeks,
ISNULL(s.user_scans, 0) AS user_scans,
ISNULL(s.user_lookups, 0) AS user_lookups,
ISNULL(s.user_updates, 0) AS user_updates,
s.last_user_seek,
s.last_user_scan,
s.last_user_lookup,
CAST(ISNULL(ps.used_page_count, 0) * 8.0 / 1024 AS DECIMAL(10, 2)) AS [Index Size (MB)]
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s
ON i.object_id = s.object_id
AND i.index_id = s.index_id
AND s.database_id = DB_ID()
LEFT JOIN sys.dm_db_partition_stats ps
ON i.object_id = ps.object_id
AND i.index_id = ps.index_id
WHERE
OBJECTPROPERTY(i.object_id, ’IsUserTable’) = 1
AND i.index_id > 0
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND ISNULL(s.user_seeks, 0) = 0
AND ISNULL(s.user_scans, 0) = 0
AND ISNULL(s.user_lookups, 0) = 0
GROUP BY
i.object_id,
i.index_id,
i.name,
i.type_desc,
i.is_unique,
i.is_primary_key,
i.is_unique_constraint,
i.is_disabled,
s.user_seeks,
s.user_scans,
s.user_lookups,
s.user_updates,
s.last_user_seek,
s.last_user_scan,
s.last_user_lookup,
ps.used_page_count
ORDER BY
[Index Size (MB)] DESC,
[Schema Name],
[Table Name];

Статистика використання індексів по певній таблиці

Іноді буває потрібно проаналізувати, як використовувались індекси певної таблиці, скільки разів викликались ті чи інші індекси під час виконання запитів.

USE [Base2Base.Tcu5Db10221_New];
GO

SELECT
OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
i.index_id,
i.type_desc AS IndexType,
us.user_seeks,
us.user_scans,
us.user_lookups,
us.user_updates,
us.last_user_seek,
us.last_user_scan,
us.last_user_lookup,
us.last_user_update
FROM sys.indexes AS i
JOIN sys.dm_db_index_usage_stats AS us
ON i.object_id = us.object_id
AND i.index_id = us.index_id
WHERE
OBJECTPROPERTY(i.object_id, ’IsUserTable’) = 1
AND us.database_id = DB_ID(’Base2Base.Tcu5Db10221_New’)
AND (
us.user_seeks > 0
OR us.user_scans > 0
OR us.user_lookups > 0
)
AND OBJECT_NAME(i.object_id) = ’InventoryJournalRecords’
ORDER BY
(us.user_seeks + us.user_scans + us.user_lookups) DESC;

Статистика індексів певної таблиці

Запит схожий на попередній, але в полі IndexSizeMb вміщує розмір індексу всередині бази даних.

SELECT
sch.name AS SchemaName,
t.name AS TableName,
i.name AS IndexName,
i.type_desc AS IndexType,

— 📦 Розмір індексу
SUM(ps.used_page_count) * 8 / 1024.0 AS IndexSizeMB,

— 📊 Використання
ISNULL(us.user_seeks, 0) AS UserSeeks,
ISNULL(us.user_scans, 0) AS UserScans,
ISNULL(us.user_lookups, 0) AS UserLookups,
ISNULL(us.user_updates, 0) AS UserUpdates,

— 🕒 Останнє використання
us.last_user_seek,
us.last_user_scan,
us.last_user_lookup,
us.last_user_update

FROM sys.tables t
JOIN sys.schemas sch
ON sch.schema_id = t.schema_id
JOIN sys.indexes i
ON i.object_id = t.object_id
JOIN sys.dm_db_partition_stats ps
ON ps.object_id = i.object_id
AND ps.index_id = i.index_id
LEFT JOIN sys.dm_db_index_usage_stats us
ON us.object_id = i.object_id
AND us.index_id = i.index_id
AND us.database_id = DB_ID()

WHERE t.name = ’InventoryJournalRecords’ — 👈 назва таблиці
AND i.index_id > 0 — без heap-запису

GROUP BY
sch.name,
t.name,
i.name,
i.type_desc,
us.user_seeks,
us.user_scans,
us.user_lookups,
us.user_updates,
us.last_user_seek,
us.last_user_scan,
us.last_user_lookup,
us.last_user_update

ORDER BY IndexSizeMB DESC;