чем определяется размер базы данных
Как узнать размер базы данных в Microsoft SQL Server?
Иногда у разработчиков или администраторов Microsoft SQL Server возникает необходимость узнать размер базы данных, поэтому сегодня, специально для начинающих, я покажу несколько возможностей получения данной информации.
Надеюсь, Вы понимаете, что все действия, которые мы будем рассматривать в данном материале, требуют определенных знаний в части Microsoft SQL Server и языка T-SQL, поэтому если Вы новичок, то я Вам рекомендую предварительно или после прочтения данной статьи и в случае возникновения тех ли иных вопросов, ознакомиться со следующими материалами:
Итак, давайте начинать, причин, по которым Вам может потребоваться узнать размер базы данных, много, также и способов это сделать тоже много. Начнем мы с самого простого, который показывает общий размер базы данных, причем все действия Вы будете делать с помощью мышки.
Примечание! В качестве СУБД в примерах ниже будет использована версия Microsoft SQL Server 2016 Express. На данном экземпляре SQL сервер создана база данных TestBase, размер которой мы и будем узнавать.
Узнаем размер базы данных с помощью SQL Server Management Studio
Данный способ подразумевает использование графического инструмента SQL Server Management Studio и заключается он в открытие свойств конкретной базы данных, где как раз и есть информация о ее размере.
Для того чтобы узнать размер базы данных с помощью Management Studio, открываем ее и в «Обозревателе объектов» выбираем нужную базу данных, кликаем по ней правой кнопкой мыши и выбираем «Свойства».
В итоге у Вас откроется окно «Свойства базы данных», в котором будет отображено много информации. На вкладке «Общие» в разделе «База данных» есть свойство «Размер» — это как раз размер базы данных.
Данный способ, может быть, будет не удобен, если Вам, например, необходимо узнать размер каждого файла, который относится к базе данных (например, отдельно файл с данными и журнал транзакции) или у Вас несколько баз данных и Вам необходимо получить сведения сразу по всем.
Узнаем размер базы данных с использованием системной процедуры sp_helpdb
Следующий способ, можно сказать классический, подразумевает использованием системной хранимой процедуры sp_helpdb, которая может показать информацию как по одной базе данных, для этого в качестве параметра передаем имя базы данных, так и по всем, т.е. запускаем без параметров. В случае если мы указали конкретную базу данных, то SQL сервер нам вернет дополнительный результирующий набор, в котором мы увидим информацию обо всех файлах, относящихся к данной БД, включая их размер.
Как видите, нам вернулось два результирующих набора, первый общий, второй для каждого файла. Этот способ может быть для некоторых тоже не удобен, за счет того, что здесь мы используем процедуру. Поэтому переходим к следующему способу, который, как мне кажется, на текущий момент самый универсальный.
Определяем размер базы данных с помощью системного представления sys.database_files
Данный способ заключается в использовании системного представления sys.database_files, которое также возвращает много полезной информации в разрезе файлов базы данных.
Например, давайте с помощью данного представления определим общий размер базы данных в мегабайтах, а также размер каждого файла в текущей БД.
Размер базы данных в SQL сервере отображается в страницах по 8 КБ, поэтому для того чтобы посчитать размер БД в мегабайтах мы должны число, которое нам возвращает SQL сервер, не только поделить на 1024, но и умножить на 8, в примере выше использована сокращенная запись данной операции (1024/8=128), которая обычно используется в официальной документации.
В случае необходимости получить информацию о размере всех баз данных находящейся на текущем экземпляре SQL сервер, можно использовать представление sys.master_files, которое возвращает сведения обо всех файлах баз данных, расположенных на SQL Server.
Как Вы заметили, все три способа вернули нам одинаковый результат, так и должно быть, какой из способов Вам будет удобней использовать в той или иной ситуации, решать, конечно же, Вам, а у меня на этом все, удачи!
Получение информации о размере базы данных, таблиц и индексов на диске (СУБД MS SQL Server)
Рост размера информационной базы является закономерным явлением ее эксплуатации, но, в некоторых случаях, данный процесс свидетельствует об ошибках в архитектуре системы. Среда SQL Server Management Studio предоставляет возможность легко получить информацию о занимаемом БД месте на диске, в том числе: сводную информацию; в разрезе таблиц базы данных; индексов таблиц. Анализ необычных (для системы в целом) данных может выявить ошибки архитектуры и/или ошибки выполнения регламентных операций. Способы получить такую информацию о размере данных на диске будут рассмотрены в данной статье.
«Стандартные отчеты» в пользовательском интерфейсе Management Studio
SQL Server Management Studio предоставляет минимальный необходимый набор стандартных отчетов для получения информации о размере базы данных/ее файлов/таблиц/индексов в режиме пользовательского интерфейса.
Доступ к этим отчетам может быть выполнен через «Обозреватель объектов» (Object explorer) → Правый клик мыши по базе данных → «Отчеты» (Reports) → «Стандартный отчет» (Standard reports)
Стандартные отчеты по использованию дискового пространства
Отчет «Занято места на диске» (Disk Usage)
Отчет содержит общие сведения об использовании места на диске базой данных.
В отчете представлена информация следующего рода:
Отчеты «Использование дисковой памяти таблицей» (Disk Usage by Table), «Использование дисковой памяти верхними таблицами» (Disk Usage by Top Tables)
Отчет содержит подробные данные об использовании места на диске таблицами, расположенными в базе данных. Отличие этих двух отчетов заключается лишь в том что в отчете «By Top Tables» вывод происходит только для «верхних» (первых) 1000 таблиц.
В отчете представлена информация:
Отчет «Использование дисковой памяти секцией» (Disk Usage by Partition)
Отчет содержит подробные данные об использовании места на диске индексом и секциями, расположенными в базе данных.
Хотел бы обратить Ваше внимание что в данном отчете неверно рассчитывается дисковое пространство по кластерному индексу. Для получения реально используемого дискового пространства кластерным индексом можно: из «объема, используемого всеми индексами таблицы» (указанном в отчете «Использование дисковой памяти таблицей») вычесть «объем всех не кластерных индексов» (по отчету «Использование дисковой памяти секцией»)
В отчете представлена информация:
Хранимые процедуры
Данные о размере базы данных и таблиц также можно получить с помощью хранимой процедуры sp_spaceused Management Studio.
Синтаксис:
sp_spaceused [[ @objname = ] ‘objname’ ]
[,[ @updateusage = ] ‘updateusage’ ]
В процедуре могут быть использованы 2 не обязательных параметра:
Примеры запросов по всей базе данных и по конкретной таблице приведены ниже:
Создание физической модели базы данных. Подготовка скрипта для создания базы данных
Оценка размера базы данных
Размер базы данных во многих практических ситуациях является критическим параметром ее эксплуатации. Проектировщик БД должен уметь оценивать как размер БД, так и возможный ее рост во времени.
Отправной точкой решения задачи оценки размера БД является вычисление размера каждой таблицы БД. Чтобы это сделать, необходимо вычислить размер каждой колонки в таблице и на основании этих данных оценить размер самой таблицы. Далее следует оценить размер всех индексов, представлений и других объектов.
Поскольку ОС распределяет память под файлы в страницах, то при вычислении размера базы данных необходимо оценить число страниц, которое она занимает. Для ОС типа Windows размер страницы составляет 1024 байта.
Формулы для оценки размера БД
С целью упрощения вычислений размера базы данных, в настоящем разделе мы будем проводить вычисления на примере СУБД SQLBASE. Размер базы данных может быть оценен по формуле
Перед тем как вычислить размер таблицы, необходимо вычислить размеры всех ее колонок.
Как правило, в определении таблицы задаются максимальные размеры полей указанного типа для данной предметной области. Например, предполагается, что колонка адреса компании не будет занимать более 50 символов. С другой стороны, на практике, на реальных данных средний размер колонки адреса компании может составлять 30 символов. Расчет размера базы данных целесообразно проводить исходя из среднего размера колонок таблиц.
Типичные размеры колонок заданного типа приведены в таблице 13.2 ниже.
| Тип данных | Размер колонки |
|---|---|
| Character | Число символов в строке |
| Number | [(NumberOfDigits + 2)/ + 1 байт |
| Date | 5 байт |
| DateTime | 12 байт |
| Long varchar | 12 байт плюс число сраниц для хранения данных |
Размер строки таблицы определяется как сумма размеров всех ее колонок по формуле
Вычисление размера индекса. Для каждого созданного B-Tree индекса его размер оценивается следующим образом: вычисляется размер индексного ключа, оценивается число строк в таблице, затем оценивается число страниц, которое занимает индекс. Расчет выполняется по формулам, приведенным в таблице 13.5.
Вычисление размера заголовка представления. Для каждого представления существует фиксированная часть заголовка и переменная часть заголовка, которая зависит от его сложности. Формулы для расчета размера заголовка представления приведены в таблице 13.6.
| Параметр | Формула |
|---|---|
| Fixed_Overhead | = 12 * 1024 |
| Variable_Overhead | = 150 * число_таблиц + 170 * число_колонок |
| Variable_Overhead_all_Views |  Variable_Overhead для всех представлений |
| Total_View_overhead_in_Page | = [(Fixed_Overhead + Variable_Overhead + Variable_Overhead_all_Views)/1024] |
Тест 7. Базы данных
а) электронные таблицы
в) текстовые редакторы
г) музыкальные редакторы
2. Чем определяется размер базы данных?
а) Количеством полей
б) Количеством записей
4. База данных «Ученики» выглядит следующим образом:
Фамилия Класс Адрес Школа Оценка Иванов 10А Уфа » 345 5
Каково количество полей в этой базе данных?
а) совокупность взаимосвязанных данных
б) устройство для расширения памяти
в) неотъемлемая часть программы Norton Commander
г) набор чисел, символов
а) внешний вид программной среды, служащий для обеспечения диалога с пользователем
б) связь текстового редактора с устройством печати
в) совокупность файлов, содержащихся в одном каталоге
г) устройство хранения графической информации
8. Какое высказывание верно?
а) СУБД — средство обработки организованной определенным образом информации
б) Модем — устройство ввода информации
в) С помощью СУБД нельзя получить нужную информацию
г) Norton Commander — текстовый редактор
а) поле включает в себя несколько записей
б) запись включает в себя несколько полей
в) каждое поле базы данных имеет свой размер
г) база данных имеет жесткую структуру
11. Сколько в базе данных записей?
Компьютер ОП Винчестер Pentium 16 800 Мб 386DX 4 300 Мб 486DX 8 500 Мб Pentium Pro 32 2 Гб
а) добавить/удалить запись
б) отредактировать строку
в) поменять местами записи
г) добавить/удалить столбец
г) количеством строк
14. Какую строку будет занимать запись Pentium после проведения сортировки по возрастанию в поле ОП?
Компьютер ОП Винчестер Pentium 16 800 Мб 386DX 4 300 Мб 486DX 8 500 Мб Pentium Pro 32 2 Гб
Ответы. 1 — б, 2 — б, 3 — г, 4 — в, 5 — а, 6 — а, 7 — в, 8 — а, 9 — а, 10 — а, 11 — г, 12 — г, 13 — в, 14 — в.
Определение размера базы данных
Для расчета размера базы данных используется команда Tools/Volumetric (рисунок 3.3.9.1). В полях Initial, Max, Grow By задаются начальное, максимальное и прирост количества строк за один месяц соответственно. Эти параметры можно указать на странице Volumerics окна редактора таблицы. В поле Physical Object указывается объект (рабочая область, группа файлов), в котором будет храниться база данных. В колонах Avg Width и Pct NULL указывается средний размер ширины поля и ожидаемый процент строк с пустым значением (NULL) текущего поля соответственно. Переключатели позволяют учесть/игнорировать индексы, созданные на первичных (PK), внешних (FK), альтернативных (AK) ключах или инверсионных входах (IE).
На странице Report отображаются результаты расчета размера базы данных (рисунок 3.3.9.2).
На странице Parameters задаются дополнительные параметры: коэффициент накладных расходов на хранение таблиц (TableFactor); увеличение расходов на хранение индекса/строки в мегабайтах/байтах (Index Factor/RowOverhead); коэффициенты (BlobFactor) и размеры (BlobBlockSize) Blob‑колонок, хранящихся вне таблицы; размер одного символа в байтах (BytesPerChar); процент увеличения размера Log‑файла.
 |
Рисунок 3.3.9.1. Страница задания основных параметров таблиц базы данных
 |
Рисунок 3.3.9.2. Страница с результатами расчета размера базы данных
Прямое и обратное проектирование
Процесс генерации физической схемы базы данных из логической модели базы данных (прямое проектирование) реализуется командой Tools/Forward Engineer/Schema Generation или кнопкой Forward Engineer (рисунок 3.3.10.1). Для примера создадим физическую схему базы данных «Заказы клиентов» с СУБД Access 2000 из модели с таблицами «Клиент», «Заказ» и представления «Клиент_Заказ» (рисунок 3.3.3.3).
Создадим пустую базу данных «Заказы клиентов» c СУБД Accеss 2000 и кнопкой Select target server выберем СУБД Access 2000.
 |
Рисунок 3.3.10.1. Страница выбора объектов генерации
На страницах Options и Summary выбираются объекты генерации и отображаются все выбранные объекты соответственно (рисунок 3.3.10.1). Каждый набор объектов может быть именован (поле Option Set, кнопки New, Rename, Delete) для повторного использования.
Кнопки Preview, Print, Report позволяют просмотреть, распечатать и сохранить SQL‑скрипт (группа команд SQL) соответственно.
Используя кнопку Filter, можно выбрать для генерации нужные таблицы и представления. В нашем примере выберем таблицы «Клиент», «Заказ» и представления «Клиент_Заказ» (рисунок 3.3.10.2).
Кнопкой Generate запустим процесс генерации. Возникает диалог установки связи с базой данных (рисунок 3.3.10.3). Кнопкой Connect выполним SQL‑скрипт, создающий физическую схему базы данных.
 |
Рисунок 3.3.10.2. Окно выбора таблиц и представлений
Рисунок 3.3.10.3. Окно установки связи с базой данных
Обратное проектирование путем генерации модели базы данных из физической схемы существующей базы данных реализуется командой Tools/Reverse Engineer или кнопкой Reverse Engineer.
Покажем процесс обратного проектирования на базе данных «Кадры» с СУБД SQL Server 7.0.
Нажмем кнопку Reverse Engineer (рисунок 3.3.10.4). Выберем СУБД SQL Server 7.0.
Нажмем кнопку Next. Появится окно опций обратного проектирования (рисунок 3.3.10.5). Рассмотрим его опции.
Reverse Engineer From – источник обратного проектирования базы данных или SQL‑скрипт.
Items to Reverse Engineer – выбор объектов для генерации.
Reverse Engineer – включение в модель системных объектов (System Objects) и установка фильтра на выбираемые таблицы по их владельцам.
Case Conversion – задание опций конвертации регистров при создании имен модели.
 |
Рисунок 3.3.10.4. Окно выбора шаблона диаграммы и СУБД
 |
Рисунок 3.3.10.5. Окно задания опций обратного проектирования
Infer – порядок создания первичных ключей и связей, если они не прописаны явно. Поднятые флажки Primary Keys и Relstions задают генерацию первичных ключей на основе анализа индексов, а связей – на основе имен колонок первичного ключа или индексов.
Import View Base Tables – установка связей между представлениями и таблицами.
После нажатия кнопки Next выводится окно установки связи с базой данных (рисунок 3.3.10.6).
 |
Нажмем кнопку Connect, и начинается процесс обратного проектирования. В результате формируется новая модель, которую можно откорректировать далее.
Рисунок 3.3.10.6. Окно установки связи с базой данных
Синхронизация модели с базой данных реализуется командой Tools/Complete Compare или кнопкой Complete Compare. Процесс синхронизации похож на процесс обратного проектирования, но дополнительно можно сравнивать с другой моделью ERWin.
В левой части окна сравнения (рисунок 3.3.10.7) показывается состояние модели, справа – схема (системный каталог) базы данных.
Следующие кнопки устанавливают опции синхронизации объектов.
Кнопкой Export/Import можно экспортировать/импортировать объект из модели/БД в БД/модель. Игнорирование различий между моделью и БД осуществляется кнопкой Ignore.
Удаление объекта из БД выполняется кнопкой Delete.
Кнопка Match позволяет связать одинаковые по смыслу объекты (например, таблицы), но имеющие разные имена. Для этого нужно щелкнуть по кнопке, затем – по объекту в модели и, наконец, по объекту, БД. Для отмены связывания используется кнопка UnMatch.
Колонка индикаторов между списками объектов модели и БД показывает установленные опции синхронизации между парами соответствующих объектов.
Кнопки Report и Preview позволяют сформировать отчет о синхронизации и просмотреть сформированный SQL‑скрипт.
 |
Рисунок 3.3.10.7. Окно сравнения модели и схемы базы данных
 |
После нажатия кнопки Next выводится окно для задания дополнительных опций синхронизации (рисунок 3.3.10.8).
Рисунок 3.3.10.8. Окно задания дополнительных опций синхронизации
Рассмотрим основные группы дополнительных опций синхронизации (рисунок 3.3.10.5).
Case Conversion of Logical Names – задание регистров имен создаваемых в модели объектов.
If Table to Import Exists in Model – задание опции генерации схемы для существующего объекта в модели: использовать существующую таблицу (Use Existing Table) или создать дубль (Create Duplicate Tabled).
Infer – аналогичны одноименным опциям обратного проектирования.
Кнопка Start Import запускает процесс импорта объектов в модель из БД, SQL‑скрипта, диаграмм из репозитория ModelPart или из файла ER1/ERX.
Создание отчетов в ERWin
Отчеты создаются мастером отчетов Data Browser. Сформированные отчеты могут далее быть обработаны генератором отчетов RPTWin (п. 4) или Excel (п. 2.2.8).
Мастер отчетов вызывается кнопкой Data Browser (рисунок 3.4.1). В окне All Reports выводится дерево отчетов и наборов. Если выбрать отчет и нажать кнопку Execute report (или произвести двойной щелчок по имени отчета), то в правой части окна появится результирующий набор данных (рисунок 3.4.1).
Рисунок 3.4.1. Окно установки Data Browser
Изображение папки, таблицы, карандаша, листа бумаги и очков перед элементом дерева означает тип элемента: папку, отчет, редактируемый отчет, результирующий набор и представление соответственно (рисунок 3.4.1).
В левом нижнем углу отображается комментарий к отчету.