Рост размера информационной базы является закономерным явлением ее эксплуатации, но, в некоторых случаях, данный процесс свидетельствует об ошибках в архитектуре системы. Среда 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 by Table), «Использование дисковой памяти верхними таблицами» (Disk Usage by Top Tables)
Отчет содержит подробные данные об использовании места на диске таблицами, расположенными в базе данных. Отличие этих двух отчетов заключается лишь в том что в отчете «By Top Tables» вывод происходит только для «верхних» (первых) 1000 таблиц.
В отчете представлена информация:
- Количество записей в таблице базы данных (Records)
- Размер зарезервированного пространства на диске (Reserved)
- Размер данных на диске (Data)
- Общий размер индексов таблицы на диске (Indexes)
- Размер не используемого пространства (Unused)
Отчет «Использование дисковой памяти секцией» (Disk Usage by Partition)
Отчет содержит подробные данные об использовании места на диске индексом и секциями, расположенными в базе данных.
Хотел бы обратить Ваше внимание что в данном отчете неверно рассчитывается дисковое пространство по кластерному индексу. Для получения реально используемого дискового пространства кластерным индексом можно: из «объема, используемого всеми индексами таблицы» (указанном в отчете «Использование дисковой памяти таблицей») вычесть «объем всех не кластерных индексов» (по отчету «Использование дисковой памяти секцией»)
В отчете представлена информация:
- Число записей в индексе/секции (Records)
- Зарезервированное пространство на диске (Reserved)
- Используемое пространство на диске (Used)
Хранимые процедуры
Данные о размере базы данных и таблиц также можно получить с помощью хранимой процедуры 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 сама их и использует. Способы получения информации с помощью динамических административных представлений будут рассмотрены в следующих статьях.
Примеры диагностируемых ошибок
| Ошибка | Следствие |
|---|---|
| Использование остаточного регистра накопления для оборотных данных | Итоговая таблица регистра накопления не будет закрываться, что приведет к ее чрезмерному разрастанию |
| Ошибки в алгоритме закрытия остаточного регистра | Аналогично предыдущей ошибке |
| Отсутствует перерасчет итогов по регистрам | Рост таблицы итоговых записей за счет «нулевых» записей |
| Использование избыточных индексов | Рост размера индексов таблицы |
| Хранение массивных двоичных данных в таблицах БД | Рост размера таблицы |
Данный перечень не является полным, но одним из общих сигналов приведенных выше ошибок является чрезмерный рост тех или иных таблиц базы данных. Естественно, большой размер таблиц/индексов не является прямым указанием на наличие ошибок, в то же время следует «приглядеться» к такого рода сигналам и провести их анализ.
Для ошибок «Использование остаточного регистра накопления для оборотных данных» и «Ошибки в алгоритме закрытия остаточного регистра» рост таблиц итоговых записей является буквально прямым следствием, но, возможна ситуация, что таков набор данных (приход был, а расхода еще не было).
Для ошибки «Отсутствует перерасчет итогов по регистрам» также характерен рост таблицы итоговых записей, но при этом (особенно для регистра остатков) дополнительным условием является наличие «нулевых» записей в регистре итогов. Подробнее об этом можно прочитать в статье «Что такое итоги регистров накопления».
Для ошибки «Использование избыточных индексов» первоочередным, конечно же, является анализ необходимости такого индекса. Но никто не станет анализировать все индексы базы данных без причин, а вот наличие «тяжелых» индексов таблицы может стать одним из таких сигналов к действию.
Касательно «Хранения массивных двоичных данных в таблицах БД», я считаю это неприемлемым, данные такого рода должны храниться на диске независимо от базы данных с обеспечением доступа к ним из системы, а также с обеспечением отдельной процедуры резервного копирования.