Пример мониторинга базы данных и использования дискового пространства.

Не задавались ли хотя бы однажды ваши заказчики или менеджеры вопросом — насколько в течение года выросли объемы используемых баз данных? Запланирована ли закупка дополнительных дисковых массивов, объем которых взят не «с потолка», а рассчитан на основе темпов роста баз данных за прошедшие 12 месяцев эксплуатации? На сколько хватит оставшегося свободного пространства на диске с учетом текущей скорости роста баз данных? Чтобы ответить на эти и другие вопросы, относящиеся к росту баз данных или использованию дискового пространства, необходимо проанализировать данные хронологически. Предлагаю вниманию читателей процедуру, с помощью которой можно автоматически собирать статистику использования дискового пространства для каждой базы данных. В дальнейшем собранная информация будет необходима для проведения очевидных расчетов, связанных с ростом потребления дисковых ресурсов.

Несколько месяцев тому назад я решил разработать специальный процесс для сбора информации об используемом дисковом пространстве для каждой развернутой в системе базы данных. Я хотел таким образом найти способ отслеживать расходование дисковых ресурсов с течением времени. Мне потребовалось отыскать информацию, относящуюся к выделению места на диске как для файлов с данными, так и для журналов транзакций. Я нашел те же самые сведения, которые отображаются в окне Database Details в программе Enterprise Manager, когда просматривается информация Space Allocated, но мне хотелось задействовать для получения этих сведений код T-SQL. С помощью SQL Server Profiler я обнаружил, что Enterprise Manager получает сведения о выделенном дисковом пространстве с помощью двух инструкций DBCC. Одна из них, SQLPERF, документирована, а другая, SHOWFILESTATS, — нет. Запустив вручную DBCC SHOWFILESTATS для каждой базы данных и сравнив результат с тем, что показывает Enterprise Manager, я понял, что с помощью этой команды смогу получить всю необходимую информацию об использованном дисковом пространстве по каждой из баз.

В SQL Server 2000 и SQL Server 7.0 названные инструкции DBCC применяются для заполнения соответствующих полей Space Allocated в Enterprise Manager. Инструкция DBCC SQLPERF (LOGSPACE) возвращает данные о файле транзакций — размер журнала транзакций в мегабайтах и в процентном соотношении с размером самой базы, и так для всех баз данных в системе. С помощью нехитрых математических преобразований можно без труда перевести проценты в мегабайты, и наоборот. Именно эта инструкция DBCC позволяет получить сведения о размере файла журнала транзакций, которые мне были необходимы.

Для получения недостающей статистики об используемых дисковых ресурсах я обратился к недокументированной инструкции DBCC SHOWFILESTATS, которая возвращает сведения об одной базе. Для одного физического файла базы данных инструкция выводит одну статистическую запись. Каждая такая запись состоит из шести полей: Fileid, FileGroup, TotalExtents, UsedExtents, Name и FileName. Поле TotalExtents используется для определения суммарного объема дискового пространства, выделенного для хранения данных в файле, а UsedExtents — для реально занятого данными. Сложив значения полей TotalExtents и UsedExtents всех файлов выбранной базы данных, конвертировав число в мегабайты, получим общий размер дискового пространства, выделенный для базы данных, и объем, реально занятый данными, соответственно. Именно эта информация и требовалась мне для построения хронологической статистики использования дискового пространства. На Экране 1 показан пример вывода DBCC SHOWFILESTATS для тестовой мастер-базы.

Я объединил две описанные инструкции DBCC в одной процедуре и получил способ вычисления размера дискового пространства, занимаемого базой данных. Процедура запускалась периодически, и собранные данные накапливались в специальной таблице. Процесс состоял из запуска задания SQL Server Agent, которое выполнялось в два этапа. Сначала исполнялась хранимая процедура usp_get_dbstats, которая генерировала сценарий T-SQL. Результирующий сценарий включал инструкцию DBCC SQLPERF (LOGSPACE) для получения данных обо всех файлах журналов транзакций, инструкцию DBCC SHOWFILESTATS для каждой базы данных и некоего дополнительного кода, с помощью которого данные DBCC преобразовывались в формат представления, необходимый для сохранения в таблице. На втором этапе сценарий на T-SQL, сгенерированный на первом этапе работы, исполнялся. После извлечения информации об использованном для каждой базы данных дисковом пространстве и форматировании результатов работы заполнялась специальная таблица. Данные этой таблицы могут помочь ответить на вопросы, сформулированные в начале статьи.

Описанный выше процесс получения дисковой статистики — это пример использования кода T-SQL для генерации нового кода T-SQL. Я применяю двухшаговый процесс для сведения к минимуму проблем при написании кода хранимой процедуры, в которой нужно будет выдавать инструкцию USE для запуска команды DBCC SHOWFILESTATS столько раз, сколько в системе имеется баз данных. А теперь рассмотрим процедуру сбора данных об используемом дисковом пространстве более подробно.

Хранимая процедура

Хранимая процедура, приведенная в Листинге 1, — это основная часть программы сбора статистики расходования дискового пространства. Процедура запрашивает системные таблицы и программным путем генерирует и выполняет инструкции PRINT для формирования T-SQL-сценария, который, в свою очередь, использует для работы упоминаемую выше связку DBCC-инструкций для извлечения сведений об использовании базами данных места на диске. Рассмотрим, секция за секцией, что именно происходит в процедуре.

Код, заключенный в блоке А, обеспечивает получение информации об использовании дискового пространства журналом транзакций. В этом блоке, как и в остальных блоках Листинга 1, динамически генерируются и исполняются команды PRINT, которые и формируют сценарий T-SQL для выполнения поставленной задачи.

Код в блоке А содержит набор инструкций T-SQL, с помощью которых создается временная таблица #tmplg, заполняемая выходными данными инструкции DBCC SQLPERF(LOGSPACE). Предложение INSERT INTO с оператором EXECUTE размещает вывод команды DBCC в таблицу #tmplg, в которой для каждой базы данных на сервере содержится одна-единственная запись (поля записи — DBName, LogSize, LogSpaceUsed, и Status). Описание каждого поля записи может быть найдено в SQL Server Books Online (BOL) в статье, озаглавленной «DBCC SQLPERF».

В блоке В содержится код, с помощью которого создается временная таблица tmp_stats. Каждая запись таблицы хранит данные об использовании дискового пространства для собственно данных и журнала транзакций. В последующих блоках эти сведения будут использованы. В блоке В также выполняются инструкции PRINT, и начатый в блоке А сценарий продолжает формироваться.

Код в блоке генерирует для каждой базы инструкцию DBCC SHOWFILESTATS. Большая часть кода блока В генерирует инструкции T-SQL, с помощью которых данные DBCC SHOWFILESTATS объединяются с записями таблицы #tmplg для каждой базы, в результате чего создается одна запись на базу, и в ней содержится статистика использования дискового пространства для данных и транзакций. И опять, команда PRINT дописывает в конец сценария новые данные, с помощью которых будут получены результаты DBCC SHOWFILESTATS.

Напоминаю, что DBCC SQLPERF создает отчет о параметрах журнала транзакций по всем базам системы, тогда как DBCC SHOWFILESTATS выдает отчет только для текущей базы данных. Таким образом, необходимо организовать исполнение инструкции DBCC SHOWFILESTATS для каждой базы на SQL-сервере.

Код в блоке С использует курсор, AllDatabases, для хранения списка всех баз в системе. Курсор позволяет хранимой процедуре перебрать все базы в списке в цикле WHILE для формирования статистики по каждой базе с помощью инструкции DBCC SHOWFILESTATS. В теле цикла хранимая процедура создает код для формирования временной таблицы, #tmp_sfs, в которой будет размещаться вывод DBCC SHOWFILESTATS, далее следует INSERT INTO. Как и раньше, INSERT INTO использует оператор EXECUTE для вставки результатов работы DBCC SHOWFILESTATS во временную таблицу. Несколько последних строк процедуры создают код, которой будет конвертировать поле LogSpaceUsed в таблице #tmplg из процентного представления в значение в мегабайтах. Далее таблица #tmp_stats заполняется статистическими данными об использовании дискового пространства для текущей базы данных и журнала транзакций.

Код в блоке D генерирует инструкции T-SQL для размещения статистики о базе и журнале транзакций в постоянной таблице, DBSTATS. Для этого применяется простая инструкция INSERT INTO, исходные данные берутся из временной таблицы #tmp_stats.

Листинг 3 демонстрирует пример того, как выглядел бы сценарий T-SQL, если бы хранимая процедура usp_get_dbstats была выполнена на сервере с небольшим числом баз данных. Представленные данные соответствуют системе, в которой имеются только стандартные базы данных после установки SQL-сервера (т. е. master, model, msdb, Northwind, Pubs и tempdb) плюс одна база данных пользователя. Причем большая часть кода в блоке D занимается сбором сведений о журнале транзакций, используя для этого команду DBCC SQLPERF(LOGSIZE). Семь разделов содержат практически один и тот же код (один раздел на каждую базу), в основе которого лежит использование инструкции DBCC SHOWFILESTATS для получения статистики о расходовании дискового пространства журналом транзакций.

Постоянная таблица

В алгоритме работы хранимой процедуры usp_get_dbstats предполагается, что статистика использования дискового пространства будет храниться в постоянной таблице DBSTATS. Поэтому перед тем, как запустить сценарий T-SQL, генерирующий процедуру usp_get_dbstats, нужно создать таблицу DBSTATS. Код, приведенный в Листинге 2, создает постоянную таблицу DBSTATS, в которой будет храниться история расходования дискового пространства базами данных системы. Новые записи добавляются в конец DBSTATS каждый раз при запуске команд, формируемых с помощью usp_get_dbstats.

В моем случае база данных DBA содержит таблицу DBSTATS и хранимую процедуру usp_get_dbstats. Если имеется некая база данных, в которой существует таблица, служащая для тех же целей, что и DBSTATS, в самом начале usp_get_dbstats (переменная @DBSTATS_DB) можно изменить базу данных, принятую по умолчанию, и использовать более подходящую для конкретной организации. Нужно иметь в виду, что если не менять имя базы данных по умолчанию, придется соответствующим образом изменить объявление @DBSTATS_DB, установив нужный размер имени базы.

Задание SQL Server Agent

Хранимую процедуру usp_get_dbstats можно запустить вручную, сгенерировать сценарий T-SQL, скопировать код сценария в Query Analyzer и получить текущую статистику из таблицы DBSTATS. Однако такой подход отнимает очень много времени в реальной ситуации с большим числом баз. Вместо этого следует воспользоваться помощью SQL Server Agent, как показано в примере Get DBSTATS на Экране 2.

Экран 2. Задание Get DBSTATS.

В задании Get DBSTATS SQL Server Agent прослеживается два шага. На первом, показанном на Экране 3, используется команда osql для запуска хранимой процедуры usp_get_dbstats. Использование osql на втором шаге выводит результаты работы usp_get_dbstats в файл для дальнейшего исполнения. Переключатель -о сообщает usp_get_dbstats о необходимости записать вывод в файл c: empget_dbstats.sql. Этот файл представляет собой сценарий T-SQL, который и будет выполняться в задании SQL Server Agent.

Экран 3. Первый шаг задания.

На втором шаге Get DBSTATS, показанном на Экране 4, выполняются инструкции, сгенерированные хранимой процедурой usp_get_ dbstats, извлекается и сохраняется необходимая информация об использовании дискового пространства. Экран 4 демонстрирует команду osql, выполняющую сценарий, созданный на первом шаге. Входной параметр -i передает работу созданного на первом шаге сценария T-SQL в процесс osql.

Экран 4. Второй шаг задания.

В своей организации я запланировал запуск задания SQL Server Agent один раз в неделю, поскольку меня устраивает мониторинг использования дискового пространства с такой частотой. В каждом конкретном случае частота запуска задания SQL Server Agent будет разной. Полученная информация позволяет выполнить самые различные виды анализа использования баз данных в организации. Можно строить графики потребления дисковых ресурсов по месяцам, по годам, подробно рассмотреть историю использования конкретной базы данных или оценить объем расходования дискового пространства после проведения миграции данных в том или ином проекте.

Расчет скорости роста базы

Если нет никакой информации об использовании дисковых ресурсов, предсказать среднюю скорость роста базы данных чрезвычайно трудно. Но после того, как будет поставлена на поток технология сбора данных об использовании дискового пространства, в распоряжении администратора окажутся все необходимые для таких расчетов данные. С помощью Microsoft Excel я построил диаграмму помесячного использования дискового пространства базами данных в своей организации.

На Экране 5 приведен пример такой диаграммы (по месяцам) для одного из рабочих серверов — SQLPROD1. График представляет объем дискового пространства, которое все базы данных SQLPROD1 использовали на протяжении последних семи месяцев. На графике отмечено несколько пиков. Теперь я могу без труда связать необычно быстрый рост баз данных и некоторые события, произошедшие в организации в то время, которые столь явно отразились на размере баз данных. На Экране 5 можно увидеть последствия добавления DB_TEST: используемое пространство на SQLPROD1 возросло почти на 3 Гбайт.

Хотя на графике представлена статистика помесячного использования дискового пространства с июля 2001 г., получить представление о средней скорости роста баз данных за отчетный или более длительный период наблюдений совсем нетрудно. Оценить ежемесячный прирост используемого дискового пространства можно по формуле:

MONTHLY_GROWTH_RATE =
 (SPACE_USED_END — SPACE_USED_BEGIN) /
 NUMBER_OF_MONTHS

Объем дискового пространства, занятого базами данных, 1 июля 2001 г. (SPACE_USED_BEGIN) составлял 6,5 Гбайт; 4 февраля 2002 г. (SPACE_USED_END) используемое дисковое пространство возросло до 7,66 Гбайт. Время наблюдений — немногим более семи месяцев. В соответствии с приведенной формулой ежемесячный прирост дискового пространства на сервере SQLPROD1 составил чуть более 0,16 Гбайт в месяц. Теперь, имея представление о средней скорости роста баз данных, я могу без труда предсказать дату, когда под базы данных будет отведено все место на диске, и, следовательно, я заблаговременно могу позаботиться о закупке необходимого оборудования.

В моем случае расчет скорости ежемесячного роста баз данных на сервере SQLPROD1 был бы невозможен без данных, накопленных с помощью описанной выше «самодельной» процедуры. Но такое решение, основанное на применении документированных и недокументированных инструкций DBCC, вполне меня устраивает. Кто-то может считать, что предложенных возможностей недостаточно, что нужна картина распределения дисковых ресурсов по каждой таблице в отдельности. Но чем бы ни пришлось воспользоваться — заказным программным обеспечением или подобной «самоделкой», — собранные данные будут очень ценным источником для анализа. Не учитывая историю работы системы, невозможно получить адекватное представление об используемых ресурсах, в данном случае — дисковом пространстве. Представление о скорости роста каждой базы данных в отдельности поможет четко спланировать стратегию закупок в будущем.

Грегори Ларсен — администратор SQL Server в Washington State Department of Health. С ним можно связаться по адресу: greg.Larsen@doh.wa.gov.