Представляем Poormon

Монитор «для бедных» — так ли это плохо?

Существует множество программ мониторинга работы SQL Server. Однако независимо от эффективности каждой такой программы, за них приходится платить, и немало. Программное обеспечение, предназначенное для мониторинга только одного SQL Server, может стоить несколько тысяч долларов. Порой для его работы требуются программные агенты, их необходимо устанавливать на производственный сервер, при этом от 3 до 5% производительности сервера уходит на обеспечение работы агента. Добавим к сказанному, что в большинстве случаев предлагаемая функциональность программ мониторинга в полном объеме не нужна. К примеру, в детском медицинском центре, где я работаю, группа DBA занимается обслуживанием восьми серверов SQL Server и более 250 баз данных. Информация о пациентах должна быть доступна круглосуточно в течение всего года, и мы, администраторы серверов, обязаны были найти способ обеспечить доступ к информации о состоянии здоровья пациентов из любой точки сети. Избранный нами метод оказался очень удобным как для получения текущей информации, так и для доступа к архивным данным, хотя мы и не используем дорогостоящие пакеты мониторинга. Наше решение подходит для SQL Server, Win.NET Server и Microsoft Office XP. Так, для своего офисного компьютера я установил активный канал (active channel) для поддержки связи с Web-сайтом медицинского центра (реализованного на базе SQL Server). Технология Microsoft Active Channel встроена в Internet Explorer (IE), с ее помощью осуществляется пересылка данных браузера на рабочее место клиента без какого бы то ни было участия самого клиента. Поскольку технология Active Channel интегрирована в Active Windows Desktop, можно просматривать содержимое Web-страниц, например в виде фона рабочего стола или хранителя экранов. Таким образом, если задействовать технологию Active Desktop, то интересующий Web-сайт может выполнять роль хранителя экранов. Приходя утром в офис, я могу, даже не регистрируясь в системе, увидеть результаты ночного резервирования баз данных, прочитать сообщения о проведенных за прошедшую ночь операциях проверки целостности Database Consistency Checker (DBCC), увидеть, какие базы растут быстрее остальных, и просмотреть различные диаграммы, относящиеся к производительности системы.

Давайте посмотрим, как можно было бы реализовать технологию, с помощью которой наша группа DBA просматривает через графики Web-страниц текущие и исторические данные замеров производительности SQL Server (см. Экран 1). Сначала я покажу, как осуществляется мониторинг вычислительной среды с помощью программы System Monitor — начиная от оценки производительности самой операционной системы Win.NET Server и заканчивая сервером приложений SQL Server. Затем рассмотрим, как визуализировать данные динамически обновляемых Web-страниц с помощью страниц доступа к данным (data access page) Microsoft Access. Чтобы повторить технические приемы, описанные в настоящей статье, потребуется SQL Server 2000, Office XP с установленной программой Access, IIS 5.0 или 4.0 в качестве Web-сервера, Win.NET Server или Windows XP, а также IE 4.01 и более поздние версии IE. Кроме того, понадобится Active Directory (AD) или домен Windows NT 4.0, чтобы можно было воспользоваться учетными записями домена для доверительных подключений или аутентификации Windows. Для простоты изложения будем считать, что все клиентские компьютеры входят в состав домена.

Прежде чем приступать к анализу производительности SQL Server, необходимо на Win.NET Server создать журналы счетчиков System Monitor, в которых, собственно, и будут накапливаться данные о производительности. Затем нужно скопировать их на каждый SQL Server в сети. Если сервер Win.NET Server пока не установлен (что касается меня, то я использую Release Candidate 1, RC1), для выполнения этапов в части приложения System Monitor можно воспользоваться операционной системой XP. И Win.NET Server, и XP позволяют хранить данные System Monitor в специально созданном уникальном формате на серверах SQL, тогда как Windows 2000 и Windows NT поддерживают только файловое представление журналов производительности. Windows 2000 при использовании System Monitor записывает данные в файл или в двоичном виде, или в текстовом. NT может хранить данные только в двоичном виде. Позже я вкратце опишу используемый формат представления данных по слежению за производительностью и покажу, какие можно использовать приемы для преобразования данных в таблицах производительности и оптимизации поиска данных с помощью хранимых процедур. Далее я расскажу о том, как данные в страницах доступа Access используются для построения диаграмм. Создав собственную страницу доступа, можно будет сохранить ее на Web-сервере и просматривать данные в формате Web.

Обращаю внимание читателей на то, что, если необходимо недорогое решение, следует использовать Microsoft Data Engine (MSDE) для записи данных производительности на SQL Server. Нет никаких ограничений на число установок MSDE, коль скоро вы уже приобрели SQL Server или офисное программное обеспечение, в состав которого входит MSDE. Хотя Enterprise Manager не является частью MSDE, тем не менее для подключения к локальной или удаленной установке MSDE можно им воспользоваться. MSDE может задействовать максимум до двух процессоров, а размер баз данных MSDE составляет не более 2 Гбайт. MSDE поддерживает планирование задач и репликацию, функции, которые могут применяться для большого числа SQL Server (правда, в режиме single-use). И, наконец, на момент написания статьи версию Win.NET Server RC1 можно было свободно использовать в течение 360 дней. Поэтому программное обеспечение, о создании которого идет речь, до поры до времени фактически является бесплатным.

Настройка System Monitor

Прежде всего, для сохранения замеров производительности в базе данных SQL Server нужно сгенерировать в среде Win.NET Server или на XP журналы System Monitor. В обеих операционных системах для этого используется приложение Administrative Tools из Control Panel, в Data Sources (ODBC) необходимо создать системное имя источника данных, Data Source Name (DSN) — для подключения базы данных, в которой будут храниться сведения о производительности. Для этой цели в нашем случае была создана база данных DBADMIN на удаленном сервере SQL Server с именем SQL2KDEV1.

Итак, необходимо сформировать базу данных DBADMIN на SQL Server; SQL Server и база данных могут быть как локальными, так и удаленными. Затем следует создать DSN, которое будет указывать на базу данных DBADMIN, и использует System Monitor, работающий на Win.NET Server или на XP. При этом нужно применять доверительное подключение к базе данных DBADMIN, для которой пользователь имеет право собственника базы (DBO). В нашем случае мы создали DSN, SQLPerfData, на сервере Win.NET Server (сетевое имя — WINNET). Доверительное подключение к DBADMIN на сервере SQL2KDEV1 выполнялось с помощью учетной записи тестового домена Qadministrator. Это, конечно, не лучший вариант, но речь идет только о тестировании. Необходимо убедиться, что учетная запись пользователя имеет право DBO для базы DBADMIN. Для созданного DSN нужно указать, что база данных DBADMIN используется по умолчанию. Следует протестировать имя источника данных и убедиться, что все работает нормально.

Теперь требуется настроить подключение System Monitor к базе данных DBADMIN. Для этого нужно щелкнуть меню Start и открыть окно Run на Win.NET Server или станции XP, а затем запустить программу perfmon.exe — System Monitor. Далее следует открыть Performance Logs and Alerts и контекстное меню Counter Logs, выбрать New Log Settings, в открывшемся окне набрать имя журнала, StorePerfSQL, и нажать ОК. На вкладке General необходимо щелкнуть Add Counters для добавления счетчиков анализируемого SQL Server 2000 (сервер должен быть установлен на Windows 2000). В Таблице 1 приводится пример набора счетчиков, мониторинг которых можно выполнить.

Теперь нужно установить интервал для сбора данных. При тестировании можно оставить значение по умолчанию, 15 сек, или установить меньшее значение. Однако в промышленной эксплуатации интервал следует увеличить до 1 и более минут для снижения сетевого трафика и сокращения числа запросов к серверу.

Далее следует настроить System Monitor на использование доверительного подключения при обращении к SQL Server. Предположим, что в поле Run As указано имя учетной записи тестового домена, Qadministrator. Поскольку этот пользователь — объект домена Q, но для него отсутствует имя регистрации на SQL Server, сервер задействует доверительную аутентификацию Windows для проверки учетной записи. Требуется щелкнуть Set Password и ввести соответствующий пароль пользователя домена.

Настройка вкладки General завершена, теперь необходимо указать место хранения собранных данных. На вкладке Log Files нужно выбрать тип файла SQL Database Log, щелкнуть Configure, в списке System DSN выбрать DSN StorePerfSQL и нажать ОК. Затем следует перейти на вкладку Schedule, выбрать Apply для запуска процедуры сбора данных по производительности и нажать ОК.

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

Верификация структур и данных

Проверить правильность настройки сбора данных просто. Во-первых, нужно убедиться, что значок Logging в System Monitor на станции сбора данных — зеленого цвета. Если после запуска System Monitor окрашен в красный цвет, следует внимательно прочитать соответствующее сообщение в журнале Application Event Log. Во-вторых, если настройка сбора данных выполнена корректно, можно прочитать данные в таблицах CounterData, CounterDetails и DisplayToID в базе DBADMIN. Если таблицы в базе данных есть, это уже означает, что System Monitor обращается к базе. В частности, можно обратиться к таблице CounterData и прочитать в колонке CounterDateTime данные о времени сбора последних сведений о производительности анализируемой системы. Это можно сделать, например, таким образом:

SELECT TOP 10 *

FROM CounterData

ORDER BY CounterDateTime DESC

Если окажется, что данные не накапливаются, хотя таблицы CounterData, CounterDetails и DisplayToID существуют, нужно удалить таблицы, и System Monitor должен построить их заново. Перед удалением таблиц следует создать их резервную копию. Microsoft не поддерживает модификацию системных таблиц; если они будут изменены, программа System Monitor может перестать накапливать данные.

Страницы доступа к данным и медленные запросы

Если посмотреть на представление данных о производительности системы в базе DBADMIN на сервере SQL, окажется, что оно значительно отличается от того, которое мы привыкли видеть в формате файла с разделением полей запятыми — Comma Separated Value (CSV). CSV-файл содержит заголовки колонок, представляющие собой названия счетчиков, с указанием времени получения данных, а также собственно данные.

Чтобы с помощью этих данных построить диаграмму, их нужно соответствующим образом обработать. Например, мы заметили, что не в состоянии создавать эффективные запросы, которые могли бы напрямую извлекать данные из таблиц CounterData и CounterDetails; все происходило очень медленно, и по ряду причин такое положение нас не устраивало, в частности, из-за отсутствия индексов. О том, как мы пытались решать эту проблему, рассказано во врезке «Повышение производительности запросов». Поле CounterDateTime содержало неиндексируемый символ char(24), и это вызывало трудности при извлечении данных по заданному диапазону дат.

Кроме того, чтобы использовать значение CounterDateTime как поле даты и времени, сначала необходимо убрать скрытый ASCII-символ в конце CounterDateTime, а уже после этого конвертировать тип данных datetime — иначе при преобразовании произойдет ошибка. Сначала мы создали представление для выполнения необходимых манипуляций с данными. Однако выяснилось, что время работы созданного представления данных слишком велико из-за отсутствия индексов в исходных таблицах, преобразований datetime и объема возвращаемой информации. Для повышения скорости извлечения данных мы хотим задействовать System Monitor для создания индексов по внешним ключам, проиндексировать поля, которые могли бы использоваться в критерии WHERE, и хранить сведения о дате и времени в формате datetime, а не в character. Но все это могло бы привести к заметному снижению скорости добавления данных в таблицы. И, наконец, если планируется обращаться за консультациями в Microsoft, изменять таблицы регистрации счетчиков производительности не рекомендуется. Для преодоления всех перечисленных трудностей была создана хранимая процедура ConvertCounterData (см. Листинг 1). Процедура переносит данные DBADMIN в другую таблицу, MyCounterData, в более рациональном формате. В Листинге 2 приводится код создания таблицы MyCounterData.

Листинг 2. Создание таблицы MyCounterData.

Процедура ConvertCounterData строит производную таблицу для размещения значений счетчиков производительности в колонках. Затем эти данные объединяются с помощью оператора GROUP BY. Во время объединения выполняется преобразование данных типа float в numeric (18,2) с округлением до двух знаков после запятой — это облегчает просмотр данных. Обращаю внимание читателей на переменные hour, minute и second, которые мы использовали при создании страниц доступа к данным и для просмотра на диаграммах особенностей поведения системы в указанном диапазоне часов, минут и секунд. Типичная страница доступа использует таблицы данных со значениями переменных hour, minute и second, но эти таблицы для трехмерного графического представления спроектированных у нас страниц доступа бесполезны. В обычной странице доступа к данным значения переменных hour, minute и second ежедневно добавляются. Поэтому график производительности системы за два дня будет охватывать 48 ч по оси Х, за три дня — 72 ч, включая минуты и секунды, а хотелось бы, чтобы все дни были представлены по единой 24-часовой шкале. Можно попробовать обойти эту навязанную особенность страниц доступа, добавив переменные hour, minute и second, как показано в Листинге 2. Кроме того, в процедуре используется переменная RecordIndex для фильтрации только самых последних данных в MyCounterData из CounterData. Естественно, читателей могут заинтересовать счетчики, которые мы не использовали. В этом случае нужно внести соответствующие изменения в процедуру ConvertCounterData и приспособить формат таблицы MyCounterData к своим требованиям.

Если внимательно посмотреть на код хранимой процедуры, станет очевидно, что сначала в таблице CounterData происходит усечение значений CounterDateTime, а уже затем значения приводятся к формату datetime. Выше уже отмечалось, что в значениях поля CounterDateTime по умолчанию использовался символ char(24), и это было крайне неудобно, особенно с учетом того, что Microsoft описывает CounterDateTime как поле для хранения значений в формате Universal Time Coordinate (UTC). Далее System Monitor зачем-то прописывает в поле CounterDateTime скрытый ASCII-символ, из-за которого невозможно непосредственно преобразовать значение поля в формат datetime — сначала приходится выделить 23 байт значения поля, удаляя посторонний символ. Для удаления символа используется функция LEFT(), как показано в Листинге 1. При написании собственной версии процедуры ConvertCounterData необходимо убедиться, что значения CounterID соответствуют правильным именам счетчиков — за образец можно взять приведенный в статье пример. Во врезке «30-секундный тайм-аут» рассказано еще об одной ошибке, возникающей при создании страниц доступа к данным.

После создания ConvertCounterData следует воспользоваться SQL Server Agent Job и составить расписание запуска этой процедуры каждые 5 мин. Чтобы убедиться, что записи CounterData и CounterDetails нормально конвертируются, нужно запустить процедуру вручную. Делать это можно с любой частотой — повторно импортироваться обработанные данные не будут. После самого первого — ручного — запуска процедуры необходимо внимательно просмотреть отчет о проделанной работе и убедиться, что все прошло без сбоев. Если послать в таблицы MyCounterData, CounterData и CounterDetails один и тот же запрос, будет видно, что извлечение данных из MyCounterData выполняется быстрее — данные в ней объединены, консолидированы и проиндексированы.

Начав сохранять данные в таблице MyCounterData, вы обнаружите, что помимо ускорения обработки появилось еще одно преимущество: при каких-либо неполадках с System Monitor и, как следствие, разрушении или создании оригинальных таблиц (CounterData и CounterDetails) заново, в таблице MyCounterData по-прежнему будут сохраняться накопленные ранее данные. System Monitor напрямую с этой таблицей не работает.

Создание страницы доступа к данным

Итак, мы создали базу DBADMIN для хранения данных о производительности системы; создали DSN для подключения к DBADMIN; настроили System Monitor для записи показателей производительности в базу данных DBADMIN. Программа System Monitor создает системные таблицы в DBADMIN и начинает записывать в них соответствующие данные, однако процесс извлечения нужной информации из таблиц протекает слишком медленно. Для ускорения работы написана хранимая процедура, ConvertCounterData, данные системных таблиц System Monitor конвертированы в таблицу MyCounterData, работа с которой выполняется значительно быстрее. В планировщике было установлено, что запуск процедуры ConvertCounterData осуществляется каждые 5 мин. Значительная часть общего объема работ выполнена.

Теперь нужно создать страницу доступа к данным, это уже относительно просто. Сначала следует открыть Access, выбрать New в меню File и щелкнуть Blank Data Access Page в окне New File. Затем в диалоговом окне Select Data Source требуется щелкнуть New Source. Убедившись, что в списке Data Connection Wizard выбрано Microsoft SQL Server, нужно нажать Next. В окне Data Connection Wizard следует набрать в текстовом поле Server name имя своего сервера SQL

(в нашем случае это был сервер SQL2KDEV1). Необходимо убедиться, что флажок Use Windows 2000 Security установлен, и щелкнуть Next. В окне Data Connection Wizard-Choose Data нужно выбрать базу данных DBADMIN из списка доступных баз и щелкнуть Next. Затем в окне Data Connection Wizard Finish при выбранном имени файла Office DataSource Connection (.odc) надо нажать Finish.

Теперь построим диаграмму для некоторых собранных данных — SQL Server Cache Hit Ratio, — чтобы продемонстрировать, как в принципе можно манипулировать данными System Monitor. В окне Select Data Source для выбранного .odc-файла следует щелкнуть Open. Каркас новой страницы доступа к данным готов для дальнейшего конструирования.

Теперь требуется спланировать поле проектирования — шесть квадратов по горизонтали и три по вертикали. Необходимо вывести на экран панель инструментов и щелкнуть по Office Chart, затем перетащить диаграмму Office из верхнего левого в нижний правый край, перекрывая всю площадь проектирования. Далее нужно дважды щелкнуть Office Chart Object и выбрать вариант Data from a database table or query на вкладке Data Source в окне Commands and Options.

Далее следует щелкнуть Edit на вкладке Data Details и в окне Select Data Source выбрать созданный ранее .odc-файл, после чего нажать Open. В секции Use data from на вкладке Data Details надо указать вариант Data member, table, view, or cube name, а затем выбрать таблицу MyCounterData для завершения необходимых настроек на вкладке Data Details.

Далее, чтобы упростить просмотр Web-страницы в трех измерениях, нужно щелкнуть вкладку Type, выбрать тип 3D Area и конкретный вид диаграммы внизу слева, как показано на Экране 2. Щелкните вкладку 3D View. Работая в режиме Projection, следует щелкнуть Orthographic. Теперь нужно открыть контекстное меню диаграммы и выбрать Field List для отображения Chart Field List (при создании страницы доступа без использования диаграмм также требуется Field List, но это не одно и то же).

Экран 2. Выбор трехмерной диаграммы.

Из Chart Field List следует перетащить счетчик Cache Hit Ratio в секцию Drop Data Fields Here на диаграмме, открыть контекстное меню Sum of Cache Hit Ratio, выбрать Auto Calc and Average и перетащить Hour в секцию Drop Category Fields Here. То же самое нужно проделать с Minute. И, наконец, необходимо открыть CounterDateTime по месяцам и перетащить Days в секцию Drop Series Fields Here.

Теперь требуется избавиться от лишних надписей по оси Х. Следует открыть контекстное меню оси Х и выбрать Commands and Options, затем щелкнуть вкладку Axis и выбрать None из списка Major Tick Marks. Для завершения оформления страницы доступа к данным нужно щелкнуть текстовое поле заголовка диаграммы и набрать System Monitor Performance Data. Страницу можно сохранить на Web-сайте IIS 4.0 (или более поздней версии) и посмотреть, что получилось, с помощью IE. Например, мы сохранили свою страницу как файл wSQLPerfData.htm в каталог C:inetpubswwwroot Web-сервера, затем обратились к странице данных по адресу: http:///wSQLPerfData.htm. Страница доступа к данным позволяет извлекать сведения с дискретностью дни, часы или минуты (см. Экран 1). Эта страница дает возможность вычислить дополнительные параметры, в том числе усреднять, суммировать и т. д. При обращении на страницу система выводит сообщение:

This page accesses data on another domain.

Do you want to allow this?

Чтобы такое сообщение не появлялось каждый раз при формировании изображения, нужно добавить сервер IIS к доверенным сайтам в IE.

Чтобы с помощью IE просматривать содержимое диаграмм на рабочей станции, необходимо установить компонент Office Web Components (OWC). Office XP делает это по умолчанию. Если клиент Office XP не установлен, следует загрузить недостающий компонент с сайта Microsoft http://office.microsoft.com/downloads/default.aspx из раздела клиентского программного обеспечения. Версия OWC Read-Only Access сейчас предоставляется бесплатно.

Наши специалисты вот уже более восьми месяцев используют описанную выше технологию для мониторинга SQL Server и представления результатов работы системы через Web. В настоящее время мы уже располагаем неким базисом, на основе которого можно проводить анализ в случае возникновения проблем с производительностью. Поскольку мы регулярно просматриваем диаграммы загрузки системы, нам известно, что соотношение попаданий в кэш у нас отличное, загрузка процессора — в пределах разумного, жесткие диски в основном нагружаются во время утреннего резервного копирования, а активность пользователей выше всего между 9:00 AM и 5:00 PM. Само слово «бесплатно» радует слух наших менеджеров, особенно когда приходится сравнивать стоимость нашего решения и стоимость коммерческих программ на базе SQL Server и Web. Web-диаграммы помогают нашим DBA-специалистам из подразделений Oracle и DB2 заниматься в мое отсутствие мониторингом систем. Так что я рекомендую всем реализовать подобный мониторинг «для бедных» и думаю, что результат не разочарует самого взыскательного клиента.

Повышение производительности запросов

Как уже отмечалось в основной статье, когда мои коллеги подготавливали данные для графического представления, отрабатывались некоторые предварительные запросы в таблицах CounterData и CounterDetails. Результаты оказались довольно интересными. Во-первых, выяснилось, что процесс извлечения данных непосредственно из таблиц идет очень медленно. Тогда мы добавили вычисляемое поле и индекс для CounterData — запросы стали выполняться заметно быстрее, если поле CounterDateTime содержало индекс по datetime вместо неиндексируемого поля char(24). Но когда структура таблицы CounterData была изменена — добавлен индекс и вычисляемое поле, — System Monitor перестал записывать в нее данные. Оказалось, что System Monitor пытается строить таблицы заново, когда выясняется, что структура таблицы изменена. Мы также попробовали создать триггер INSTEAD OF для перенаправления потока данных в другую таблицу. Однако SQL Server продолжал «перемалывать» большие массивы данных и игнорировал триггер. Мы снова подумали о модификации таблиц, но рассчитывать на помощь Microsoft не могли, поскольку системные таблицы были изменены; так что менять их структуру не рекомендуется.

В Microsoft Platform Software Development Kit (SDK) в рубрике Performance Monitor (http://msdn.microsoft.com/library/default.asp?url=/library/enus/perfmon/base/performance_data.asp) приводится описание полей таблицы CounterData (см. Таблицу А).

Однако представленное Microsoft описание CounterDateTime не вполне очевидно. Если внимательно изучить таблицы CounterData и CounterDetails, заполняемые System Monitor, станет ясно, что имена счетчиков хранятся в CounterDetails, значения — в CounterData, для каждого счетчика отводится одна колонка и за один раз записывается одна строка данных. Например, при получении значений 12 счетчиков за 2 мин в CounterDetails у нас набралось 12 записей с именами счетчиков, тогда как в CounterData оказалось 24 записи за каждую минуту работы System Monitor. Один из способов приведения данных к виду, более удобному для дальнейшего использования, состоит в построении сводной таблицы, в которой в одной колонке будет содержаться время и дата получения значения счетчика, а в каждой последующей колонке — значение конкретного счетчика. Кстати, именно такой формат использует для CSV-файла System Monitor.

30-секундный тайм-аут

Кто-то из читателей, следуя описанным в основной статье техническим приемам, может попытаться упростить процедуру подключения к таблицам, генерируемым программой System Monitor, и с помощью страниц доступа к данным Microsoft Access напрямую посылать запросы к таблицам. Однако выясняется, что запросы к неиндексируемым данным выполняются чрезвычайно медленно. Более того, складывается впечатление, что в страницах доступа Microsoft Access имеется ошибка; нам не удалось добраться до свойства Connect Timeout и пришлось использовать значение тайм-аута по умолчанию — 30 сек. Поэтому, если для просмотра данных производительности System Monitor применяются страницы доступа, придется предварительно выполнить некоторые преобразования данных, чтобы избежать ограничения в 30 сек. В качестве альтернативы, можно создать репозитарий на SQL Server, и уже на нем хранить данные System Monitor и распоряжаться ими так, как вам заблагорассудится.

Марк Соломон — администратор баз данных SQL Server в детской клинике в Цинциннати. Имеет сертификаты MCSE+I и MCDBA. С ним можно связаться по адресу: flotsam7jetsam@hotmail.com.