Рост размера информационной базы является закономерным явлением ее эксплуатации, но, в некоторых случаях, данный процесс свидетельствует об ошибках в архитектуре системы. Среда SQL Server Management Studio предоставляет возможность легко получить информацию о занимаемом БД месте на диске, в том числе: сводную информацию; в разрезе таблиц базы данных; индексов таблиц. Анализ необычных (для системы в целом) данных может выявить ошибки архитектуры и/или ошибки выполнения регламентных операций. Способы получить такую информацию о размере данных на диске будут рассмотрены в данной статье.
«Стандартные отчеты» в пользовательском интерфейсе Management Studio
SQL Server Management Studio предоставляет минимальный необходимый набор стандартных отчетов для получения информации о размере базы данных/ее файлов/таблиц/индексов в режиме пользовательского интерфейса.
Доступ к этим отчетам может быть выполнен через «Обозреватель объектов» (Object explorer) → Правый клик мыши по базе данных → «Отчеты» (Reports) → «Стандартный отчет» (Standard reports)
Отчет «Занято места на диске» (Disk Usage)
Отчет содержит общие сведения об использовании места на диске базой данных.
В отчете представлена информация следующего рода:
- Общий объем, занятый на диске (Total space reserved)
- Место, занятое файлами данных (Data files space reserved)
- Место, занятое журналом транзакций (Transaction log space reserved)
- Отражает графически процент пространств в составе файлов данных: индексов (index), данных (data), не выделенного (unallocated) и не используемого (unused)
- Отражает графически процент примененного (used) и неиспользуемого (unused) пространства в составе журнала транзакций
- Выводит записи событий автоматического увеличения (autogrow) и/или сжатия (autoshrink) для базы данных
- Выводит информацию о месте на диске, используемом файлами данных
![Отчет "Занято места на диске" (Disk Usage)](https://ausevich.ru/wp-content/uploads/2017/09/DataUsageReport.png)
Отчеты «Использование дисковой памяти таблицей» (Disk Usage by Table), «Использование дисковой памяти верхними таблицами» (Disk Usage by Top Tables)
Отчет содержит подробные данные об использовании места на диске таблицами, расположенными в базе данных. Отличие этих двух отчетов заключается лишь в том что в отчете «By Top Tables» вывод происходит только для «верхних» (первых) 1000 таблиц.
В отчете представлена информация:
- Количество записей в таблице базы данных (Records)
- Размер зарезервированного пространства на диске (Reserved)
- Размер данных на диске (Data)
- Общий размер индексов таблицы на диске (Indexes)
- Размер не используемого пространства (Unused)
![Отчет "Использование дисковой памяти таблицей" (Disk Usage by Table)](https://ausevich.ru/wp-content/uploads/2017/09/StandardReportDiskUsageByTable.png)
Отчет «Использование дисковой памяти секцией» (Disk Usage by Partition)
Отчет содержит подробные данные об использовании места на диске индексом и секциями, расположенными в базе данных.
Хотел бы обратить Ваше внимание что в данном отчете неверно рассчитывается дисковое пространство по кластерному индексу. Для получения реально используемого дискового пространства кластерным индексом можно: из «объема, используемого всеми индексами таблицы» (указанном в отчете «Использование дисковой памяти таблицей») вычесть «объем всех не кластерных индексов» (по отчету «Использование дисковой памяти секцией»)
В отчете представлена информация:
- Число записей в индексе/секции (Records)
- Зарезервированное пространство на диске (Reserved)
- Используемое пространство на диске (Used)
![Отчет "Использование дисковой памяти секцией" (Disk Usage by Partition)](https://ausevich.ru/wp-content/uploads/2017/09/StandardReportDiskUsageByPartition.png)
Хранимые процедуры
Данные о размере базы данных и таблиц также можно получить с помощью хранимой процедуры sp_spaceused Management Studio.
Синтаксис:
sp_spaceused [[ @objname = ] ‘objname’ ]
[,[ @updateusage = ] ‘updateusage’ ]
В процедуре могут быть использованы 2 не обязательных параметра:
- @objname — Полное или неполное имя таблицы, индексированного представления или очереди. Если параметр не указан — результаты возвращаются для всей базы данных.
- @updateusage — Указывает на необходимость запустить процедуру обновления сведений
Примеры запросов по всей базе данных и по конкретной таблице приведены ниже:
1 2 3 4 5 6 7 8 |
-- Указываем использование конкретной базы данных USE [DevBase2] -- Информация по всей базе exec sp_spaceused -- Информация по конкретной таблице exec sp_spaceused N'_AccumRg17539' |
![Результат работы хранимой процедуры sp_spaceused](https://ausevich.ru/wp-content/uploads/2017/09/sp_spaceused.png)
В первом блоке отражен результат запроса по всей базе данных, во втором — по конкретной таблице.
Динамические административные представления
Помимо вышеприведенных способов, для получения необходимой информации можно обратиться к динамическим административным представлениям. На самом деле, как отчеты, так и хранимая процедура sp_spaceused сама их и использует. Способы получения информации с помощью динамических административных представлений будут рассмотрены в следующих статьях.
Примеры диагностируемых ошибок
Ошибка | Следствие |
---|---|
Использование остаточного регистра накопления для оборотных данных | Итоговая таблица регистра накопления не будет закрываться, что приведет к ее чрезмерному разрастанию |
Ошибки в алгоритме закрытия остаточного регистра | Аналогично предыдущей ошибке |
Отсутствует перерасчет итогов по регистрам | Рост таблицы итоговых записей за счет «нулевых» записей |
Использование избыточных индексов | Рост размера индексов таблицы |
Хранение массивных двоичных данных в таблицах БД | Рост размера таблицы |
Данный перечень не является полным, но одним из общих сигналов приведенных выше ошибок является чрезмерный рост тех или иных таблиц базы данных. Естественно, большой размер таблиц/индексов не является прямым указанием на наличие ошибок, в то же время следует «приглядеться» к такого рода сигналам и провести их анализ.
Для ошибок «Использование остаточного регистра накопления для оборотных данных» и «Ошибки в алгоритме закрытия остаточного регистра» рост таблиц итоговых записей является буквально прямым следствием, но, возможна ситуация, что таков набор данных (приход был, а расхода еще не было).
Для ошибки «Отсутствует перерасчет итогов по регистрам» также характерен рост таблицы итоговых записей, но при этом (особенно для регистра остатков) дополнительным условием является наличие «нулевых» записей в регистре итогов. Подробнее об этом можно прочитать в статье «Что такое итоги регистров накопления».
Для ошибки «Использование избыточных индексов» первоочередным, конечно же, является анализ необходимости такого индекса. Но никто не станет анализировать все индексы базы данных без причин, а вот наличие «тяжелых» индексов таблицы может стать одним из таких сигналов к действию.
Касательно «Хранения массивных двоичных данных в таблицах БД», я считаю это неприемлемым, данные такого рода должны храниться на диске независимо от базы данных с обеспечением доступа к ним из системы, а также с обеспечением отдельной процедуры резервного копирования.