| ||||||||||||||||
 | ||||||||||||||||
| ||||||||||||||||
 | ||||||||||||||||
| ||||||||||||||||
|
|
SQL Profiler решает проблемы Как воссоздание картины преступления помогает найти преступника, так и трассировка работы базы данных позволяет выявить узкие места и устранить их. В статье «Поймай событие», опубликованной в предыдущем номере журнала, описывалась архитектура системы трассировки SQL Server 7.0 и было показано, как графически задать трассу в SQL Profiler. На этот раз речь пойдет о том, как при помощи SQL Profiler воссоздавать трассы, и как через расширенные хранимые процедуры трассировки определять автоматический старт. Основываясь на таком мощном фундаменте, вы сможете со знанием дела применять SQL Profiler и хранимые процедуры для самых разнообразных исследований, начиная с долго выполняющихся запросов и кончая сложными тупиковыми блокировками. Предварительная подготовка к воспроизведению трассы При помощи SQL Profiler можно повторять прохождение сохраненных трасс для отладки проблемных приложений, создавать для тестовых испытаний сценарии с различными ситуациями из реальной жизни, настраивать базы данных и многое другое. Если требуется повторно пройтись по трассе, придется выполнить некоторую подготовительную работу. Прежде всего, необходимо определить трассу для отслеживания определенных событий и столбцов данных помимо тех, что вас интересуют. Фиксация этих дополнительных событий и столбцов гарантирует, что все действия будут повторяться в точности так, как они происходили ранее. Во-вторых, следует сохранить результаты трассировки в файле, таблице или сценарии SQL. При любом повторном прогоне требуется фиксировать события Connect, Disconnect, ExistingConnection, а также RPC:Starting и SQL:BatchStarting. Кроме того, при воспроизведении курсоров API серверной части (то есть курсоров сервера, которые управляются функциями курсора API) необходимо фиксировать события CursorExecute, CursorOpen и CursorPrepare. Для воспроизведения подготовленных операторов SQL серверной стороны следует добавить еще события Exec Prepared SQL и Prepare SQL. При воспроизведении потребуются столбцы, которые будут содержать следующие данные: имя приложения, двоичную информацию, идентификатор соединения или идентификатор процесса сервера (SPID), идентификатор базы данных, класс события, подкласс события, имя хост-узла, цифровую информацию, имя сервера, имя пользователя SQL, время начала прогона и текстовую информацию. Важно отметить, что при повторном прогоне фиксируемые события не имитируются, они происходят заново. Поэтому следует учесть, что в процессе первоначальной трассировки вы, вероятнее всего, изменили свою базу данных. К примеру, при воспроизведении трассы, включающей оператор INSERT, в таблице может появиться дубликат ключа. Чтобы избежать подобных проблем, следует вернуть базу данных в исходное состояние, если трасса воспроизводится на исходном сервере (то есть на том сервере, на котором проводилась первоначальная трассировка). Если повторный прогон будет проводиться на другом сервере, важно убедиться в том, что база данных на нем находится в том же состоянии, в котором была база данных на исходном сервере. При этом обязательно следует использовать те же самые имена пользователей, их полномочия, идентификаторы баз данных, какие применялись на исходном сервере. Использование тех же самых идентификаторов требует особого умения и опыта, особенно потому, что корпорация Microsoft не поощряет обращения напрямую к системной таблице sysdatabases, что необходимо для изменения идентификаторов баз данных. Можно обеспечивать совпадение идентификаторов баз данных другим способом. Для этого следует скопировать файлы пользовательской базы данных с исходного сервера на тот, где будет воспроизводиться трасса, а затем восстановить на нем резервную копию базы данных master с исходного сервера. Альтернативный способ заключается в том, чтобы восстановить на сервере, выбранном для прогона, взятую с исходного сервера резервную копию пользовательской базы данных, а затем восстановить там же резервную копию базы данных master. В обоих случаях на сервере, где воспроизводится трасса, файлы базы данных будут размещаться в тех же директориях, что и на исходном сервере, а системные таблицы базы данных master будут содержать исходные идентификаторы базы данных. Чтобы полностью избавиться от этих проблем, нужно просто убрать из трассировки столбец с идентификатором базы данных и установить в качестве заданной используемую по умолчанию базу данных для каждого пользователя, который фиксируется в процессе трассировки. Можно также управлять уровнем синхронизации сценария и скоростью воспроизведения. Выберите пункт Settings из меню Replay, чтобы войти в диалоговое окно Replay SQL Server. Параметр Synchronization Level, который управляет синхронизацией в рамках соединения, может принимать следующие значения: Полная синхронизация (Full synchronization). Это значение используется по умолчанию. При этом все события, происходившие в одном соединении, воспроизводятся в исходном порядке. Частичная синхронизация (Partial synchronization). При этом значении события в одном соединении могут начинаться раньше событий, уже зафиксированных в других соединениях.
Без синхронизации (No synchronization). При этом значении параметра события могут наступать сразу по окончании предыдущего события в том же соединении, то есть без всякой синхронизации в рамках соединения. Параметру скорости воспроизведения, Replay Rates, можно присвоить одно из следующих значений: Как можно быстрее (As fast as possible). Это значение применяется по умолчанию.В данном случае следующее событие начинается сразу же по завершении предыдущего. Сохранять интервал между событиями (Maintain interval between events). Это значение сохраняет первоначальный интервал времени между моментами наступления событий. Выдерживать отношение ко времени старта (Maintain relationship to start time). При этом значении события происходят в те же моменты времени относительно начала воспроизведения трассы, что и при исходной трассировке. Организация воспроизведения трассы Предположим, необходимо воспроизвести трасу выполнения подготовленных серверных операторов SQL, которые представляют собой операторы Transact-SQL (T-SQL), посылаемые пользователем на сервер через ADO, OLE DB или ODBC. SQL Server 7.0 выполняет серверные подготовленные операторы SQL при помощи псевдохранимых процедур sp_prepare и sp_execute, которые вызывает клиентское приложение. Вызов sp_prepare заставляет SQL Server готовить операторы T_SQL к исполнению, компилируя их и помещая в кэш планы исполнения. При вызове sp_execute SQL Server выполняет заранее помещенные в кэш планы и, возможно, делает это неоднократно. Каждый вызов хранимой процедуры порождает события RPC:BatchStarting, Prepare SQL и Exec Prepared SQL. Именно по этой причине указанные события необходимо включить в определение трассы. SQL Profiler содержит несколько примеров определений трасс, которые можно применять в качестве шаблонов. В том числе и пример номер 6, «T-SQL for Replay», относящийся к повторному прогону трассы. Этот пример целесообразно использовать для задания выходных данных трассировки, генерируемых при воспроизведении. Чтобы открыть сохраненные выходные данные трассировки для воспроизведения, выберите пункт Open из меню File и выделите для хранения информации, собираемой в ходе трассировки, файл, таблицу или сценарий SQL. Они могут быть представлены или пунктами меню Replay или кнопками на панели инструментов. Применение расширенных хранимых процедур Некоторые функции трассировки из SQL Profiler недоступны . К их числу относятся запуск трассировки по расписанию, запуск при наступлении определенного события или при начале работы SQL Server. Кроме того, из SQL Profiler нельзя задать отправку результатов трассировки в журнал приложений Windows NT или Windows 2000. Для выполнения этих функций и для обеспечения большей свободы программного управления трассами можно воспользоваться набором расширенных хранимых процедур под общим называнием xp_trace*. Рассмотрим принципы использования этих хранимых процедур на примере запуска трассировки sp_start_mytrace и хранимой процедуры остановки трассировки sp_stop_mytrace. Первая хранимая процедура, sp_start_mytrace, определяет трассировочные события, столбцы данных, фильтры и создает очередь для хранения фиксируемых событий. Затем она извлекает события из очереди и помещает их в системный файл. Процедура sp_start_mytrace общается с очередью событий и отслеживает ее состояние посредством описателя целого типа queue handle, который процедура создает в процессе построения очереди. Процедура sp_stop_mytrace использует этот описатель, когда надо закончить ведение очереди. Отслеживать состояние описателя очереди - нелегкая задача. Хотя существует множество методик получения его значения, самым простым и функциональным способом является создание таблицы, в которую будут записываться данные обо всех трассах и их очередях, а также о времени начала трассировки, идентификаторе пользователя, включившего трассировку и об имени компьютера, с которого она была запущена. В листинге 1 приведены операторы, создающие такую таблицу, которая называется activetraces. Чтобы увидеть, какие трассировки снимаются в настоящий момент, достаточно просмотреть эту таблицу. Чтобы остановить трассировку, надо просто запросить из таблицы соответствующий описатель очереди. Хранимая процедура для запуска трассировки Давайте пройдемся по этим двум хранимым процедурам, чтобы увидеть, каким образом выполняется запуск и остановка трассировки. У хранимой процедуры, начинающей трассировку, имеется четыре необязательных входных параметра. Первые два, @spid_filter и @dbid_filter, позволяют ограничить собираемые во время трассировки сведения только теми, которые относятся к конкретному процессу сервера (определяемому по его идентификатору, SPID) и заданной базе данных. Если эти параметры не будут заданы, то в ходе трассировки будут собираться данные обо всех процессах и базах данных. Параметр @email_address позволяет назначить адрес электронной почты, по которому будет направляться подробная информация о ходе трассировки. Если этот параметр не указать, то процедура sp_start_mytrace будет выводить информацию только на экран. Если же он задан, но адрес указан неверно, то хранимая процедура выдаст сообщение об ошибке и завершится. Последний параметр, @filename, предназначен для указания имени файла, в который будет направляться собираемая во время трассировки информация. В случае, когда этот параметр не определен, сведения по умолчанию будут помещаться в файл c:\mytraceN.trc, где N – номер описателя очереди. Такое соглашение, определяющее правило для присвоения имен файлам с данными трассировки, позволяет одновременно снимать несколько трасс, не позволяя одной из них заблокировать файл для записи результатов только для себя. Процедура sp_start_mytrace сначала вызывает хранимую процедуру xp_trace_addnewqueue для создания очереди событий. Первые четыре параметра этой процедуры (максимальный размер очереди max_items, таймер timeout, верхний thread_boost и нижний thread_reduce пороги) используют значения, установленные по умолчанию. Назначение этих параметров описано в статье «Поймай событие: трассировка при помощи SQL Profiler», опубликованной во втором номере нашего журнала. Параметр data_columns представляет собой битовую маску для столбцов данных, которые надо фиксировать. Чтобы сгенерировать эту маску, следует выполнить битовую операцию ИЛИ (|) для целых значений, представляющих каждый столбец. Выходной параметр queue_handle содержит целое число, равное размеру очереди. Он предназначен для использования в дальнейшем. Затем хранимая процедура sp_start_mytrace вызывает расширенную хранимую процедуру seteventclassrequired для каждого события, которое должно отражаться в трассе. У вызванной процедуры имеется три параметра. Первый параметр, queue_handle, является описателем очереди, полученным из хранимой процедуры xp_trace_addnewqueue. Параметр event_class представляет собой целое число, соответствующее событию, которое следует фиксировать. (Для получения полного списка классов событий и их имен запускается хранимая процедура xp_trace_geteventnames. В приводимом примере использованы классы событий, принятые в SQL Profiler по умолчанию.) Параметру Isrequired отвечает двоичная переменная, определяющая, включать данное событие в трассу или нет . После отбора событий, которые необходимо фиксировать, следует установить фильтры для событий. По умолчанию при определении трассы отбрасываются все события, которые генерирует SQL Profiler. Но если вам хочется узнать больше о трассировочных расширенных хранимых процедурах SQL Server 7.0, которые SQL Profiler применяет для трассировки, можно попробовать поменять исключающий фильтр SQL Profiler на включающий. Запустите эту трассировку, а затем любую другую трассировку по вашему выбору. Вы увидите, какие расширенные хранимые процедуры использует SQL Profiler для ведения второй трассы. Каждой категории фильтра соответствует своя расширенная хранимая процедура, но все они следуют образцу xp_trace_set*filter. Процедура sp_start_mytrace использует хранимую процедуру xp_trace_setappfilter для исключения из трассы всех событий, порожденных работой SQL Profiler. Если пользователь запросил фильтры для сбора данных только об указанном процессе (SPID) или об определенной базе данных, то sp_start_mytrace обращается соответственно к процедурам xp_trace_setspidfilter или xp_trace_setdbfilter. После этого sp_start_mytrace определяет вариант вывода очереди (то есть место для записи результатов трассировки). Хотя можно указать несколько выходных пунктов назначения, все они должны быть разных типов. К примеру, можно одновременно выводить данные в файл и в таблицу, но нельзя одновременно осуществлять вывод в два файла. Для задания выходного пункта назначения sp_start_mytrace вызывает процедуру xp_trace_setqueuedestination. Чтобы выбрать несколько вариантов вывода, следует соответственное число раз повторить вызов расширенной хранимой процедуры. У процедуры setqueuedestination используются следующие параметры, не считая описателя очереди queue_handle: Вариант вывода (Destination): 2 - файл, 3 – журнал приложения, 4 - таблица, 5 – передающий сервер; Значение (Value): включено (1), отключено (0) Сервер (Server): имя зарегистрированного сервера, если нужно послать результаты на другой сервер или NULL, в противном случае Объект (Object): имя таблицы или файла (при этом вариант вывода должен быть равен соответственно 1 или 3). Если такой объект уже существует, то процедура просто поместит в него результаты, если же этого объекта еще нет, процедура создаст его.Процедура sp_start_mytrace посылает информацию, собранную в ходе трассировки, в расположенный на этом же сервере файл, заданный параметром @filename. Трассировка начинается Теперь, когда трасса определена, хранимая процедура sp_start_mytrace добавляет строку в таблицу activetraces, чтобы следить за данной трассировкой. Чтобы убедиться, что описатель очереди рассматриваемой трассы и другие данные не потеряны, процедура sp_start_mytrace формирует сообщение с подробной информацией о трассе и выводит его на экран, а также посылает на сервер при помощи расширенной хранимой процедуры xp_sendmail. Затем sp_start_mytrace вызывает расширенную процедуру xp_trace_startconsumer, единственным параметром которой служит описатель очереди, чтобы начать посылать информацию о происходящих событиях в заданную выходную очередь. В листинге 3 проведена вторая хранимая процедура, sp_stop_mytrace, которая прекращает трассировку, используя описатель очереди. Sp_stop_mytrace обращается к таблице activetraces за описателем очереди, а затем вызывает хранимую процедуру xp_trace_destroyqueue для полученного описателя очереди, чтобы остановить трассировку. Завершая свою работу, sp_stop_mytrace удаляет из таблицы activetraces строку, соответствующую остановленной трассировке. Тестирование хранимых процедур Для проведения тестирования этих хранимых процедур нужно выполнить следующие команды:
Вы должны будете получить печатное сообщение (и сообщение по электронной почте, если задан соответствующий параметр), извещающее о запуске трассировки с перечислением основных параметров. К ним относятся: состояние описателя очереди; время запуска трассировки; имя компьютера, с которого она была запущена; идентификатор инициировавшего ее пользователя; местоположение файла с результатами трассировки и имя хранимой процедуры для остановки трассировки.
Теперь переключитесь на базу данных Pubs и запустите несколько запросов, обращающихся к ее таблицам. Убедитесь в том, что вы используете то же самое соединение, по которому вызывали хранимую процедуру. Это важно, потому что в нашем тесте мы при помощи системной функции @@SPID выясняли значение SPID для текущего соединения и затем использовали это значение в качестве фильтра трассировки. Когда это будет сделано, запустите следующий оператор, подставив в него вместо 14 то значение, которое получили в печатном сообщении и в сообщении электронной почты:
Теперь можно в SQL Profiler открыть сгенерированный файл и просмотреть его. Автоматический запуск Рассмотренные выше хранимые процедуры можно применять вручную для запуска и завершения трассировки, но можно делать это автоматически, как при начале работы SQL Server, так и при наступлении некоторого заранее заданного события. Чтобы инициировать автоматический запуск, надо сначала сохранить определение трассировки. Это делается при помощи расширенной хранимой процедуры xp_trace_savequeuedefinition. Теперь расширенная хранимая процедура xp_trace_setqueueautostart будет запускать трассировку в соответствии с указанными условиями. Процедуре savequeuedefinition помимо описателя очереди требуется еще два параметра. Параметр queue_name служит для идентификации очереди. (Процедура savequeuedefinition сохранит определение трассы в ключе регистра HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft \MSSQLServer\SQLServerProfiler\Server\Queues\[queue_name]. Вы загружаете определение трассы позднее при помощи расширенной хранимой процедуры xp_trace_loadqueuedefinition.) В соответствии с параметром is_shared это определение может быть предназначено для коллективного использования (1) или частного (0). Для успешной работы расширенной хранимой процедуры setqueueautostart также необходимо задать имя очереди и булеву величину, которая определяет, разрешен автоматический запуск (1) или нет (0). В листинге 4 приведена остальная часть процедуры, отвечающей за автоматический запуск трассировки (обратите внимание на то, что в приведенном коде описанные в начале данного раздела шаги пропущены). Для отключения автоматического запуска следует выполнить команду
И вечный бой… Теперь, когда вы разобрались в функциях трассировки SQL Profiler, вы сможете применять их для исследования множества ситуаций в базах данных. Возьмем, к примеру, запросы, которые выполняются слишком долго. Чтобы выяснить, в чем причина дополнительных задержек, определите трассу, отметив относящиеся к делу события (набор событий будет отличаться при запуске запросов из хранимых процедур или в пакетном режиме). Обычно для этого требуется отслеживать события SQL:StmtCompleted и SP:StmtCompleted. Сгруппируйте запросы либо по времени выполнения, либо по длительности использования CPU в зависимости от того, что именно вам нужно выяснить: почему запросы выполняются медленнее, чем хотелось бы, или почему они используют слишком много ресурсов CPU. Чтобы не затрачивать ресурсы трассировки на быстро выполняемые запросы, можно установить фильтр трассировки, указав минимальную длительность выполнения запроса, или же минимальное время использования CPU в миллисекундах. SQL Profiler может применяться и для анализа тех процессов, которые происходят «за кулисами» SQL Server Enterprise Manager. Предположим, вы хотите посмотреть, что делает Enterprise Manager, когда вы добавляете файл `file5` к группе файлов `fg5` в базе данных testdb. Сначала нужно открыть диалоговое окно со свойствами базы данных testdb и добавить файл. Но прежде чем нажать OK, запустите трассировку в SQL Profiler, используя события и столбцы данных по умолчанию, и установив для идентификатора базы данных специальный фильтр со значением 1 (master). Запустите трассировку; нажмите OK в Enterprise Manager, затем остановите трассировку. SQL Profiler зафиксирует операторы, подобные следующим :
SQL Profiler может помочь решить и такие проблемы, как введение автоматического уведомления при изменении параметра автоматического приращения. Это нелегкая задача, так как поместить триггер на системную таблицу нельзя . Один из способов получения такой информации – применить SQL Profiler для определения трассы при следующих событиях, столбцах данных и фильтрах: События: TSQL: SQL:StmtCompleted и SP:StmtCompleted. Столбцы данных: EventClass, TextData, NTUserName, ApplicationName, SQLUserName и StartTime. Фильтры: специальный фильтр, значение которого равно идентификатору базы данных, по которой желательно проводить фильтрацию; и фильтр для текста, позволяющий отобрать сообщения, содержащие %alter%database%modify%file%.Теперь можно создать выходную таблицу, структура которой задана оператором, приведенным в Листинге 5. Отметим, что в данном случае необходимо построить таблицу самостоятельно, а не пользоваться таблицей, которую может создать SQL Profiler. Это обусловлено необходимостью в дальнейшем извлекать информацию из столбца TextData, находясь в триггере. Дело в том, что в таблицах, построенных при помощи SQL Profiler, столбец TextData имеет тип ntext, который из триггера недоступен . Чтобы сделать столбец таблицы доступным для чтения из триггера, необходимо определить его тип как nvarchar. Затем следует определить триггер, который будет извещать вас об изменении параметра автоматического приращения. Создание триггера описано в листинге 6. Для проверки работы триггера измените свойства файла:
Вы получите сообщение о том, что свойства файла были изменены:
Всегда очень трудно выяснить, какие события привели к созданию тупиковой блокировки. Однако в SQL Profiler предусмотрены специальные события, которые могут заметно облегчить проведение «расследования». К примеру, можно отслеживать с помощью трассировки появление события Lock:Deadlock. Наступление этого события говорит о том, что возникла тупиковая ситуация. При этом пользователю сообщаются идентификатор процесса сервера (SPID), идентификатор заблокированной транзакции, время наступления блокировки, имя приложения и идентификатор пользователя. Чрезвычайно удобным оказывается событие Lock: Deadlock Chain, которое генерируется каждый раз при блокировке: оно позволяет выяснить идентификаторы процесса (SPID) и транзакции. Можно записать идентификаторы транзакций, вовлеченных в тупиковую блокировку, затем сгруппировать результаты трассировки по идентификаторам транзакций и анализировать только эти транзакции. При другом подходе результаты трассировки отсылаются в таблицу. Затем с помощью запросов можно провести в ней фильтрацию по номеру SPID или идентификатору транзакции. Чтобы сгенерировать ситуацию возникновения тупиковой блокировки, создайте две таблицы, t1 и t2, в каждой из которых должен быть только один столбец целого типа. Введите в каждую таблицу одну строку, содержащую значение 1. Задайте трассу, в которой будет фиксироваться следующий набор событий: Lock:Deadlock, Lock: Deadlock Chain, и соответствующие им события начала и окончания выполнения операторов (RPC, SP, SQL). Выбор должен производиться в зависимости от предполагаемого источника блокировки. В нашем примере понадобятся только события SQL: StmtStarting и SQL:StmtCompleted. Помимо столбцов данных, назначаемых по умолчанию, добавьте столбец для фиксации идентификатора транзакции и столбцы по вашему усмотрению. Установите фильтр трассы, соответствующий идентификатору той базы данных, с которой вы работаете. После этого откройте два соединения с сервером из Query Analyzer. Находясь в первом соединении, выполните:
В соединении 2 запустите следующую транзакцию:
Наконец, в соединении 1 выполните операторы:
Остановите трассировку и откройте файл с ее результатами. Найдите события Lock:Deadlock Chain events, и запишите номера вовлеченных транзакций. Сгруппируйте выходные данные по идентификаторам транзакций и раскройте соответствующие транзакции. Выходные данные будут выглядеть аналогично приведенным на экране 1. В состав SQL Server Enterprise Manager входит специальный мастер, который может помочь установить трассы, в том числе и те, что применяются для поиска причин появления тупиковых блокировок. Чтобы для определения трассы воспользоваться мастером создания трасс Create Trace Wizard, следует войти в Enterprise Manager, выбрать в меню Tools пункт Wizards, затем открыть категорию Management и выбрать Create Trace Wizard. Заключительное замечание Предоставляемые SQL Profiler возможности повторять трассы вместе с имеющимися в SQL Server 7.0 расширенными хранимыми процедурами для трассировки позволяют выполнять отладку работы баз данных. Неважно, что именно предстоит делать – просто контролировать состояние окружения SQL Server, или решать проблемы повышения производительности приложений – настало время применить свои знания на практике. Ицик Бен-Ган имеет сертификаты MCDBA, MCSE+I, MCSD, MCT и SQL Server MVP. Является старшим преподавателем на курсах по SQL Server в колледже Hi-Tech в Израиле и председателем израильской группы пользователей SQL Server. |
|
|
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|