SQL Profiler решает проблемы. Использование SQL Server Profiler

19.06.2020

Сегодня мы будем замерять производительность нашего приложения с помощью Visual Studio Profiling Tool .

Visual Studio Profiling Tool позволяет разработчикам измерять, оценивать производительность приложения и кода. Эти инструменты полностью встроены в IDE, чтобы предоставить разработчику беспрерывный контроль.
В этом руководстве мы по шагам профилируем приложение PeopleTrax используя Sampling и Instrumentation методы профилирования, чтобы выявить проблемы в производительности приложения.

Много картинок.

Подготовка

Для работы с этим руководством вам потребуется:

Microsoft Visual Studio 2010
Средние знания языка C#
Копия тестового приложения PeopleTrax, скачать можно с MSDN Code Gallery

Методы профилирования

Чуть-чуть отступим от главной темы статьи и рассмотрим возможные методы профилирования. Эту главу можно пропустить, используемые методы профилирования будут кратко описаны перед использованием.

Sampling

Sampling — собирает статистические данные о работе приложения (во время профилирования). Этот метод легковесный и поэтому, в результате его работы очень маленькая погрешность в полученных данных.

Каждый определенный интервал времени собирается информация о стеке вызовов (call stack). На основе этих данные производится подсчет производительности. Используется для первоначального профилирования и для определения проблем связанных с использование процессора.

Instrumentation

Instrumentation — собирает детализированную информацию о времени работы каждой вызванной функции. Используется для замера производительности операций ввода/вывода.

Метод внедряет свой код в двоичный файл, который фиксирует информацию о синхронизации (времени) для каждой функции в файл, и для каждой функции которые вызываются в этой.

Отчет содержит 4 значения для предоставления затраченного времени:

Elapsed Inclusive - общее время, затраченное на выполнение функции
Application Inclusive - время, затраченное на выполнение функции, за исключением времени обращений к операционной системе.
Elapsed Exclusive - время, затраченное на выполнение кода в теле. Время, которое тратят функции, вызванные целевой функцией.
Application Exclusive - время, затраченное на выполнение кода в теле. Исключается время, которое тратится выполнения вызовов операционной системы и время, затраченное на выполнение функций, вызванные целевой функцией.

Concurrency

Concurrency – собирает информацию о многопоточных приложения (как отлаживать многопоточные приложения см. «Руководство по отладке многопоточных приложений в Visual Studio 2010»). Метод собирает подробную информацию о стеке вызовов, каждый раз, когда конкурирующие потоки вынуждены ждать доступа к ресурсу.

.NET Memory

.NET Memory - профайлер собирает информацию о типе, размере, а также количество объектов, которые были созданы в распределении или были уничтожены сборщиком мусора. Профилирование памяти почти не влияет на производительность приложения в целом.

Tier Interaction

Tier Interaction – добавляет информацию в файл для профилирования о синхронных вызовах ADO.NET между страницей ASP.NET или другими приложениями и SQL сервера. Данные включают число и время вызовов, а также максимальное и минимальное время.

На этом рассмотрение методов профилирование закончим и продолжим учиться профилировать приложения.

Профилирование Sampling методом

Sampling это метод профилирования, который периодически опрашивает рассматриваемый процесс, чтобы определить активную функцию. В результате показывает количество раз, когда функция была в начале call stack во время тестирования.

Профилирование

Открываем тестовый проект PeopleTrax . Устанавливаем конфигурацию в Release (в Debug версию встраивается дополнительная информация для отладки приложения, и она плохо скажется на точности результатов профилирования).

В меню Analyze нажимаем на Launch Performance Wizard .

На этом шаге нужно выбрать метод профилирования. Выбираем CPU Sampling (recommended) и нажимаем Next.

Выбираем какое приложение мы будем профилировать, это PeopleTrax и кнопка Next. В следующем нажимаем Finish и автоматически запустится профайлер и наше приложение. На экране мы видим программу PeopleTrax. Нажимаем кнопку Get People , ждем завершения работы и Export Data . Закрываем блокнот и программу и профайлер сгенерирует отчет.

Профайлер сгенерировал отчет (*.vsp)

Анализ отчета Sampling метода

В Summary отображается график использования процессора в течение всего времени профилирования. Список Hot Path показывает ветки вызовов, которые проявили наибольшую активность. А в списке Functions Doing Most Individual Work (название которого говорит само за себя) – функции, которые занимали бо льшее время процесса в теле этих функций.

Посмотрев на список Hot Path видим что метод PeopleNS.People.GetNames занимает почти последнее место в ветке вызовов. Его то и можно изучить внимательнее на предмет улучшения производительности. Нажимаем на PeopleNS.People.GetNames и перед нами открывается Function Details .

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

Function Code View показывает код метода, когда он доступен и подсвечивает наиболее «дорогие» строки в выбранном методе. Когда выбран метод GetNames видно, что он читает строки из ресурсов приложения используя StringReader , добавляя каждую строку в ArrayList . Нет очевидных способов улучшить эту часть.

Так как PeopleNS.People.GetPeople единственный, кто вызывает GetNames – нажимаем GetPeople . Этот метод возвращает ArrayList объектов PersonInformationNS.PersonInformation с именами людей и компаний, возвращенными методом GetNames . Тем не менее, GetNames вызывается дважды каждый раз, когда создается PersonInformation . (Это и показано желтым и красным выделением). Очевидно, что можно легко оптимизировать метод, создавая списки только один раз вначале метода.

Альтернативная версия GetPeople также есть в коде и мы ее сейчас включим. Для этого нужно определить OPTIMIZED_GETPEOPLE как Conditional compilation symbol в окне свойств проекта People и PeopleTrax . И да, если захотите повторить мои опыты, то нужно исправить ошибку в проекте. В оптимизированном конструкторе класса не правильно написано имя ресурсов: нужно PeopleNS.Resources вместе PeopleNS.Resource. Если это не изменить, все валится со страшными ошибками.

Оптимизированный метод заменит старый при следующей сборке.

Перезапускаем профилирование в текущей сессии нажав Launch with Profiling в окне Performance Explorer . Нажимаем на Get People и Export Data . Закрываем блокнот и программу а профайлер сгенерирует новый отчет.

Чтобы сравнить два отчета – выбираем оба и ПКМ Compare Performance Reports . Колонка дельты показывает разницу в производительности версии Baseline с более поздней Comparison . Выбираем Inclusive Samples % и Apply.

Как видно выигрыш в производительности заметен невооруженным глазом

Профилирование методом Instrumentation

Этот метод полезен при профилировании операций ввода вывода, запись на диск и при обмене данными по сети. Этот метод предоставляет больше информации чем предыдущий, но он несет с собой больше накладных расходов. Бинарники полученные после вставки дополнительного кода получаются больше обычных, и не предназначены для развертывания.

В этот раз мы сосредоточим наш анализ на экспорте данных, в котором список людей записывается в файл блокнота.

Профилирование

В Performance Explorer выбираем Instrumentation и нажмаем Start Profiling. Нажимаем Get People. После загрузки людей ждем 10 секунд и нажмаем Export Data. Закрываем блокнот и программу. Профилировщик сгенерирует отчет.

Анализ

Профилировщик покажет такую картинку:

Мы не получили ту информацию, которую хотели. Отфильтруем данные. Мы специально ждали 10 секунд, чтобы просто отфильтровать ненужные сейчас данные профилирования. Отмечаем с 13-й до конца и нажимаем Filter by selection . Уже другой результат:

Hot Path показывает, что метод Concat занимает много времени (он также первый в списке Functions With Most Individual Work). Нажимаем на Concat , чтобы посмотреть детально информацию о методе.

Видно, что PeopleTrax.Form1.ExportData – единственный метод, который вызывает Concat . Нажимаем PeopleTrax.Form1.ExportData в вызывающих методах (Function calling this function ).

Анализируем метод в окне кода. Обратите внимание, что нет прямого вызова Concat. Вместе этого есть использование операнда += , который компилятор заменяет на методы System.String.Concat . Как уже почти все знают, что любые изменения в строках в.NET приводят к уничтожению старой версии строки и созданию измененной строки. К счастью в.NET есть класс StringBuilder который и предназначен для такой работы.

В проекте уже есть оптимизированный метод с использованием StringBuilder . В проекте PeopleTrax добавляем переменную компиляции OPTIMIZED_EXPORTDATA . Сохраняем и снова запускаем профайлер и сравниваем отчеты. Сразу видно (да и логически понятно) что мы оптимизировали вызовы Concat (с 6000 до 0 раз).

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

16.05.2000 Ицик Бен-Ган

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

В статье «Поймай событие», опубликованной в предыдущем номере журнала, описывалась архитектура системы трассировки 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. Управлять воспроизведением можно при помощи опций, приведенных в таблице 1. Они могут быть представлены или пунктами меню 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 – номер описателя очереди. Такое соглашение, определяющее правило для присвоения имен файлам с данными трассировки, позволяет одновременно снимать несколько трасс, не позволяя одной из них заблокировать файл для записи результатов только для себя.

Для проверки работы триггера измените свойства файла:

ALTER DATABASE testdb MODIFY FILE (NAME = `testdb_dat`, MAXSIZE = 30MB)

Вы получите сообщение о том, что свойства файла были изменены:

File properties changed:
Statement: ALTER DATABASE testdb MODIFY FILE (NAME = `testdb_dat`,
MAXSIZE = 30MB)
NT User Name: Gandalf
Application Name: MS SQL Query Analyzer
SQL User Name: NA
Time: 2000-11-22 14:15:28

Всегда очень трудно выяснить, какие события привели к созданию тупиковой блокировки. Однако в 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. Находясь в первом соединении, выполните:

BEGIN TRANSACTION UPDATE t1 SET col1 = 1

В соединении 2 запустите следующую транзакцию:

BEGIN TRANSACTION
UPDATE t2 SET col1 = 1
SELECT * FROM t1
COMMIT TRANSACTION

Наконец, в соединении 1 выполните операторы:

SELECT * FROM t2
COMMIT TRANSACTION

Остановите трассировку и откройте файл с ее результатами. Найдите события 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, или решать проблемы повышения производительности приложений – настало время применить свои знания на практике.

Ицик Бен-Ган [email protected] имеет сертификаты MCDBA, MCSE+I, MCSD, MCT и SQL Server MVP. Является старшим преподавателем на курсах по SQL Server в колледже Hi-Tech в Израиле и председателем израильской группы пользователей SQL Server.

Профилировщик (profiler) SQL Server 2005, отслеживание запросов приложений, шаблоны трассировки, группировка информации о запросах

Одно из самых полезных средств мониторинга активности пользователей - это профилировщик (Profiler ). При помощи этого программного средства можно узнать, какие команды в настоящее время выполняет сервер SQL Server . Необходимость в применении профилировщика возникает очень часто. Вот несколько стандартных ситуаций, когда без него обойтись бывает очень сложно:

q вы хотите проанализировать работу приложения и посмотреть, какие команды оно выполняет на сервере. Эта информация может пригодиться:

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

· чтобы выяснить, насколько оптимальные с точки зрения производительности запросы передает на сервер приложение. На практике при использовании профилировщика часто можно выявить совсем неоптимальные запросы, например, когда фильтрация или сортировка данных выполняется на клиенте;

· чтобы понять, при выполнении какой команды Transact -SQL из приложения на сервере возникает ошибка;

q для сбора информации о пользовательской активности в течение продолжительного промежутка времени (например, можно собрать все запросы, которые передавались на сервер определенным приложением в течение рабочего дня). Затем собранную информацию можно проанализировать вручную или передать программе Database Tuning Advisor для проведения автоматизированного анализа;

q для проведения мониторинга работы сервера в режиме реального времени. Например, если работа сервера вдруг замедлилась, в окне профилировщика можно просмотреть, какие команды в данный момент на нем выполняются.

В SQL Server 2005 у профилировщика появилось много нового:

q появилась профилировка событий Integration Services . Теперь вы можете при помощи профилировщика отслеживать ход выполнения новых пакетов DTS ;

q появилась возможность при записи информации выполнения команды записывать также показания счетчиков из Системного монитора;

q в профилировщик добавлено много новых событий и источников информации, которые могут выбираться для записи в файл трассировки. Определение того, что нужно записывать в файл трассировки, теперь можно сохранять в формате XML ;

q в формате XML теперь можно сохранять и результаты трассировки (возможность записи в форматы ANSI , OEM , UNICODE также сохранена);

q в формате XML можно сохранять даже планы выполнения команд Transact -SQL , перехваченных профилировщиком. Затем эти планы можно открыть в SQL Server Management Studio для дальнейшего анализа;

q появилась возможность группировать события прямо в окне профилировщика. С помощью группировки, например, можно очень просто посчитать, сколько раз в течение дня на сервере выполнялась та или иная команда Transact -SQL .

Работа с профилировщиком выглядит очень просто. Это приложение можно запустить из меню Пуск | Программы | Microsoft SQL Server 2005 | Performance Tools | SQL Server Profiler . Для того чтобы начать работу, в открывшемся окне профилировщика в меню File (Файл) нужно выбрать New Trace (Новая трассировка) и подключиться к серверу SQL Server 2005, работу которого вы будете отслеживать. Под словом "трассировка" подразумевается сеанс сбора информации о работе SQL Server 2005. Однако перед тем, как приступать к сбору информации, нужно настроить параметры этого сеанса. Эта настройка производится в окне Trace Properties (Свойства трассировки), которое открывается автоматически перед началом сеанса трассировки (рис. 11.1).

Рис. 11.1. Настройка параметров сеанса трассировки

На вкладке General (Общие) в списке Use the template (Использовать шаблон) вы можете выбрать наиболее подходящий шаблон для сбора информации в рамках вашего сеанса. В принципе, можно и не обращать внимание на настройки шаблона, а вручную определить параметры сбора информации (при помощи соседней вкладки Events Selection (Выбор событий)). Однако указание правильного шаблона поможет сэкономить время и избежать ошибок. Поэтому на шаблонах остановимся подробнее.

Шаблон - это сохраненные в специальном файле с расширением tdf настройки сеанса трассировки. Работа с шаблонами (добавление новых, изменение существующих, импорт и экспорт отчетов в другие каталоги) производится при помощи меню File | Templates (Файл| Шаблоны) в SQL Server Profiler . Изначально в вашем распоряжении есть восемь шаблонов:

q Standard (default ) - как понятно из названия, этот шаблон подходит для большинства ситуаций и поэтому выбирается по умолчанию. Он позволяет отслеживать все запускаемые на выполнение хранимые процедуры и команды Transact -SQL ;

q SP _ Counts - собирается информация о запускаемых на выполнение хранимых процедурах и функциях. При этом информация в окне профилировщика сортируется (в терминологии профилировщика - группируется) по именам хранимых процедур;

q TSQL - собирается информация о всех командах Transact -SQL , запускаемых на выполнение на сервере. Кроме кода команд, записывается также информация об идентификаторах пользовательских процессов и времени запуска. Обычно этот шаблон используется для мониторинга команд, передаваемых на сервер приложением;

q TSQL _ Duration - почти то же самое, что и предыдущий шаблон, но вместо записи информации о времени запуска команды Transact -SQL записывается время, которое потребовалось на ее выполнение. Обычно этот шаблон используется для мониторинга производительности работы сервера "вручную";

q TSQL _ Grouped - кроме информации о коде команды Transact -SQL и времени ее запуска, записывается информация о имени приложения, учетной записи пользователя в операционной системе и логине пользователя, который был использован для подключения. При этом записи группируются по логину. Обычно этот шаблон используется в тех ситуациях, когда вы хотите отследить активность конкретного приложения;

q TSQL _ Replay - будет записываться максимально подробная информация о выполняемых командах Transact -SQL . Потом эту информацию можно использовать для того, чтобы с максимальной точностью воспроизвести нагрузку на сервер. Обычно этот шаблон применяется для записи набора команд, который будет потом использоваться для тестирования разных настроек сервера с точки зрения производительности;

q TSQL _ SPs - кроме записи информации о начале запуска всей хранимой процедуры (событие SP:Starting ), этот вариант трассировки записывает также информацию о выполнении каждой команды данной хранимой процедуры (событие SP:StmtStarting ). Такой шаблон обычно используется для мониторинга работы сложных хранимых процедур;

q Tuning - этот шаблон предназначен для записи информации, наиболее подходящей для передачи Database Tuning Advisor . Про работу с этим средством автоматизированного анализа и оптимизации производительности будет рассказано в разд. 11.5.5 .

Как уже говорилось, совсем необязательно ограничиваться только набором готовых шаблонов. Можно использовать свои параметры сеанса трассировки, настроив их на вкладке Events Selection . В таблице на этой вкладке вы должны выбрать требуемые события (в строках) и информацию (в столбцах), которая будет для них записываться. Обратите внимание, что по умолчанию видна только небольшая часть доступных строк и столбцов. Чтобы включить отображение всех строк и столбцов, нужно установить флажки Show All Events (Показать все события) и Show All Columns (Показать все столбцы).

Очень часто бывает так, что нужно отслеживать только действия, выполняемые в определенной базе данных, или определенным приложением, или определенным пользователем, или выбрать все эти условия одновременно. Фильтры на сбор информации можно настроить, нажав кнопку Column Filters (Фильтры столбцов) на вкладке Events Selection . Для каждого столбца можно настроить запись только определенных значений (Like ) или запрет записи определенных значений (Not Like ). По умолчанию настроен единственный фильтр- Not Like для столбца ApplicationName . Он заставляет игнорировать все события приложения SQL Server Profiler , т. е. все события, относящиеся к самому процессу сбора информации трассировки. Этот фильтр лучше не удалять, потому что в противном случае может возникнуть положительная обратная связь с бесконечной записью информации.

При помощи еще одной кнопки Organize Columns (Организовать столбцы), которая расположена на вкладке Events Selection , можно настроить порядок столбцов для отображения или записи в профилировщике. Обратите внимание на раздел Group (Группа) в этом списке. Для тех столбцов, которые в него помещены, будет автоматически производиться группировка. Если вы поместите в этот раздел только один столбец, то при просмотре у вас появится возможность использовать очень удобный режим Aggregated View (Агрегированное представление) (когда информация автоматически сгруппирована, например, по базе данных, по приложению, имени пользователя и т. п., и записи для нужной базы данных, приложения или пользователя можно раскрывать и сворачивать).

После того как выбран нужный шаблон или настроен свой собственный набор событий для протоколирования, вам остается вернуться на вкладку General и настроить несколько дополнительных параметров сеанса трассировки.

Информация трассировки может быть запротоколирована в файл. Этот файл можно использовать в разных ситуациях:

q можно передать в качестве источника информации Database Tuning Advisor ;

q можно "проиграть" повторно в профилировщике, повторив все записанные команды, например, для оценки производительности при разных настройках сервера;

q можно предъявить разработчикам в подтверждение своих претензий к приложению.

Отметим некоторые моменты, которые связаны с протоколированием сеанса трассировки в файл:

q 5 Мбайт, которыми ограничивается размер файла по умолчанию, это очень мало. При профилировке рабочего сервера этот размер набирается за минуты. Правда, по умолчанию установлен флажок Enable file rollover (Включить смену файлов), т. е. после заполнения одного файла автоматически будет создан второй файл, к имени которого добавится номер 1, потом - 2 и т. п., но работать с большим количеством файлов не всегда удобно. Если вы собираете информацию для передачи Database Tuning Advisor , то лучше настроить предельный размер файла в 1 Гбайт (при помощи параметра Set maximum file size (Настроить максимальный размер файла) на вкладке General ). Запись трассировки в файл чаще всего производится с рабочей станции администратора, поэтому место на диске потребуется именно на рабочей станции, а не на сервере;

q параметр Server processes trace data (Сервер обрабатывает данные трассировки) можно использовать для увеличения надежности записи информации трассировки. По умолчанию обработкой данных трассировки занимается SQL Server Profiler , и происходит это на том компьютере, на котором он запущен (не обязательно на сервере). Если установить этот флажок, то обработкой информации трассировки будет заниматься сервер. Это гарантирует, что вся информация трассировки будет собрана (при снятом флажке в моменты пиковой нагрузки сервера часть информации может быть пропущена), но увеличит нагрузку на сервер.

Другой вариант записи информации трассировки - запись в таблицу SQL Server . Таблица с нужным набором столбцов будет создана автоматически. Вы можете лишь настроить максимальное количество записей в этой таблице. Обратите внимание, что на этой вкладке максимальное количество записей указывается в тысячах.

Последний параметр на вкладке General - Enable Trace stop time (Включить время остановки трассировки). Вы можете указать время, когда трассировка будет отключена автоматически. Обычно имеет смысл отключать трассировку перед началом каких-то служебных операций, которые с точки зрения протоколирования вас не интересуют (резервное копирование, массовая загрузка данных, процессинг кубов OLAP и т. п.).

После того как все параметры трассировки будут настроены, можно нажать на кнопку Run (Запустить) на вкладке General и приступить к трассировке (рис. 11.2).

Рис. 11.2. Просмотр информации в ходе сеанса трассировки

Работа в окне просмотра информации трассировки достаточно очевидна: в верхней части показываются события, которые происходят на сервере, а в нижней части для них приводится подробная информация (например, код команд SQL ). Отметим некоторые возможности, доступные в этом окне:

q если на вкладке Organize Columns в свойствах шаблона вы выбрали столбцы для группировки, то можно сгруппировать по этим столбцам записи в окне просмотра. Для этой цели в меню View (Вид) предусмотрена команда Grouped View (Сгруппированное представление);

q если на той же вкладке в свойствах шаблона в список Group был помещен только один столбец, то можно использовать еще более удобный режим отображения Aggregated View (рис. 11.3). Этот режим включается при помощи команды Aggregated View из того же меню View и позволяет превратить значения из выбранного вами столбца в узлы дерева, которые можно сворачивать и разворачивать. Кроме того, для каждого из этих узлов автоматически подсчитывается количество событий.

Рис. 11.3. Режим отображения Aggregated View

q в профилировщике можно отобразить не только те события, которые были пойманы только что, но также сохраненные файлы и таблицы трассировки. Кроме того, вы можете открывать обычные скрипты SQL Server с командами Transact -SQL . Информация из этих файлов или таблиц может быть использована для того, чтобы повторить запротоколированные операции. Для этой цели предназначены команды меню Replay (Повторить);

q в профилировщике SQL Server 2005 появилась новая возможность - связывание информации трассировки с показателями счетчиков производительности Системного монитора. Для того чтобы воспользоваться этой возможностью, нужно:

· определить сеанс трассировки, в ходе которого обязательно должна записываться информация для столбцов StartTime и EndTime ;

· запустить сеанс трассировки с записью информации в файл или таблицу. Одновременно с ним собрать в файл протокол показаний счетчиков Performance Monitor ;

· открыть собранную информацию из файла трассировки в профилировщике, а затем воспользоваться командой Import Performance Data (Импортировать данные производительности) из меню File .

В SQL Server 2005 предусмотрен заменитель для профилировщика. Это хранимые процедуры трассировки. Их функциональные возможности практически идентичны возможностям профилировщика. Например, вы можете также выбрать события для трассировки и записать их в текстовый файл. Главное отличие заключается в том, что все настройки придется производить из кода Transact -SQL .

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

q sp_trace_create - позволяет настроить параметры сеанса трассировки;

q sp_trace_setevent - позволяет выбрать для созданного сеанса трассировки требуемые события;

q sp_trace_setfilter - позволяет настроить фильтр для сбора информации трассировки;

q sp_trace_setstatus - позволяет запустить трассировку, остановить ее или удалить созданное хранимой процедурой sp_trace_create текущее определение сеанса;

q sp_trace_generateevent - позволяет сгенерировать пользовательское событие, которое будет перехвачено в ходе трассировки.

SQL Profiler - программное средство, используемое для трассировки сервера SQL Server. "Трассировка" - сеанс сбора информации о работе SQL Server 2008

Основное назначение:

SQL Profiler используется администраторами для:

· анализа работы приложения;

· определения оптимальности запросов, направляемых на сервер;

· выявления команд Transact-SQL, при выполнении которых возникает ошибка;

· сбора информации о пользовательской активности в течение продолжительного промежутка времени;

· проведения мониторинга работы сервера в режиме реального времени.

Новые возможности:

a. профилировка Analysis Services;

b. профилировка событий Integration Services;

c. возможность при записи информации выполнения команды записывать показания счетчиков из Performance Monitor;

d. в Profiler добавлено множество новых событий и источников информации, которые могут выбираться для записи в файл трассировки;

f. возможность группировать события в окне профилировщика.

Работа с SQL Server Profiler

1. Запустить SQL Server Profiler - из меню Пускà Программыà SQL Server 2008à Performance Toolsà SQL Server Profiler .

2. В открывшемся окне в меню File выбрать New Trace и подключиться к серверу SQL Server 2008, работу которого мы будем отслеживать.

3. Настроить параметры сеанса в окне Trace Properties , которое открывается автоматически переда началом сеанса трассировки (см. рис. 8.1).

Рис. 8.1. Настройка параметров сеанса трассировки

a. На вкладке General выбрать в списке Use the template наиболее подходящий шаблон. Выбор шаблона производится при помощи меню File à Templates в SQL Server Profiler. Изначально в вашем распоряжении - восемь шаблонов:

1). Standard (default) - шаблон по умолчанию, позволяющий отслеживать все запускаемые на выполнение хранимые процедуры и команды Transact-SQL;

2). SP_Counts - сбор информации о запускаемых на выполнение хранимых процедурах и функциях с сортировкой по именам;

3). TSQL - сбор информации о всех командах Transact-SQL, запускаемых на выполнение на сервере, с указанием идентификатора пользовательских процессов и времени запуска;

4). TSQL_Duration - аналогичен предыдущему шаблону, но вместо информации о времени запуска команды TSQL записывается время, которое потребовалось на ее выполнение;

5). TSQL_Grouped - помимо информации о коде команды Transact-SQL и времени ее запуска, записывается также информация о имени приложения, учетной записи пользователя в ОС и учетной записи пользователя, которая была использована для подключения;

6). TSQL_Replay - запись максимально подробной информации о выполняемых командах Transact-SQL;

7). TSQL_SPs - помимо записи информации о начале запуска хранимой процедуры (SP:Starting) регистрируется информация о выполнении каждой из команд хранимой процедуры (SP:StmtStarting);

8). Tuning - используется для сбора информации необходимой Database Tuning Advisor.

b. На вкладке General если необходимо указать место сохраннения трассировочной информации:

1). Информация трассировки может быть запротоколирована в файл (по умолчанию размером 5 Мбайт):

· параметр Enable File Rollover определяет, будет ли при заполнении одного файла автоматически создаваться следующий. Имя следующего файла будет таким же, как и имя предыдущего, но к его имени будет добавлен номер (1, 2, 3 и т.п.)

· параметр Server processes trace data можно использовать для увеличения надежности записи информации трассировки. После установки этого флажка обработкой информации трассировки будет заниматься сервер.

2). Информация трассировки может быть сохранена в таблице SQL Server. Таблица с нужным набором столбцов будет создана автоматически.

3). С помощью параметра Enable Trace Stop Time можно указать время, когда трассировка будет отключена автоматически.

c. На вкладке Events Selection определить параметры сбора информации. В таблице на этой вкладке вы должны выбрать требуемые события (в строках) и информацию (в столбцах), которая будет для них записываться. Для отображения всех строк и столбцов, нужно установить флажки Show All Events и Show All Columns .

1). с помощью кнопки Column Filters (Фильтры столбцов) настроить фильтры на сбор необходимой информации (отслеживать действия, выполняемые в определенной БД, или определенным приложением, или определенным пользователем) – Like или Not Like ;

2). при помощи кнопки Organize Columns (Организовать столбцы) настроить порядок столбцов для отображения или записи в профилировщике с возможностью группировки данных - раздел Group.

4. После задания всех параметров трассировки нажать на кнопку Run (Запустить) (см. рис. 8.2)

Рис. 8.2. Просмотр информации в ходе сеанса трассировки

В верхней части окна отображаются события, происходящие на сервере, а в нижней части - приводится подробная информация по каждому событию (например, код команд SQL).

Возможности, доступные в окне трассировки:

1. Если на вкладке Organize Columns в свойствах шаблона вы выбрали столбцы для группировки, вы можете сгруппировать по этим столбцам записи в окне просмотра. Для этой цели в меню View предусмотрена команда Grouped View ;

2. Если в список Group был помещен только один столбец, то у вас появляется возможность использовать режим отображения Aggregated View (см. рис. 8.3). Этот режим включается при помощи команды Aggregated View из того же меню View .

Рис. 8.3. Режим отображения Aggregated View

3. Иы можете открывать в профилировщике события, сохраненные в файлах и таблицах трассировки. Также существует возможность повторять запротоколированные операции, с помощью меню Replay ;

4. Вы можете связывать информацию трассировки с показателями счетчиков производительности System Monitor. Для этого:

· определить сеанс трассировки, в ходе которого обязательно должна записываться информация для столбцов StartTime и EndTime ;

· запустить сеанс трассировки с записью информации в файл или таблицу. Одновременно с ним собрать в файл протокол показаний счетчиков Performance Monitor ;

· открыть собранную информацию из файла трассировки в профилировщике, а затем воспользоваться командой Import Performance Data из меню File .

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

Для того чтобы отследить действия, выполняющиеся в данный момент, необходимо запустить MS SQL Profiler, создать новую трассу и настроить анализ показателей:

На вкладке «Общие» необходимо указать название трассировки. Указать, куда будут сохраняться данные снятой трассы — на файл и/или в таблицу базы данных.

Большой интерес представляет вкладка «Выбор событий»:

На данной странице указываются события, которые необходимо отслеживать. На этом примере укажем данные, необходимые для отслеживания планов запросов.

Получите 267 видеоуроков по 1С бесплатно:

По умолчанию трассировка проходит по всем указанным событиям во всех базах данных. Для того чтобы наложить отборы на получаемые данные, необходимо нажать кнопку «Фильтры столбцов …»:

Например, установим отбор по идентификатору информационной базы (Узнать ID базы можно с помощью запроса SELECT DB_ID(N’ИмяБазы’)).

Запуск трассировки в Profiler для 1С

После того как все настройки сделаны, осталось запустить отслеживание, для этого необходимо нажать «Запустить» (RUN). С этого момента в трассировку начнут попадать все действия, указанные в фильтре:

Например, я запускаю трассу на время проведения документа «Поступления товаров и услуг» с целью отследить самые трудозатратные операции.

После того как трассировка получена, необходимо её проанализировать.

Анализ данных из Profiler

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