В этой статье собраны несколько материалов, опубликованных в рассылке "MS SQL Server - дело тонкое…" и посвящённых проблемам мониторинга эффективности MS SQL Server, методам выяснения причин возникновения аппаратных проблем и их разрешения. Кроме того, Вашему вниманию будут предложены несколько полезных, практических рекомендаций и предостережений.
Если Ваш сервер баз данных чересчур интенсивно использует I/O, можно изменить значение параметра операционной
системы I/O Page Lock Limit, который может увеличить эффективную норму
чтения/записи данных операционной системой на жесткий диски.
Сначала, выполните эталонный тест
I/O для вашей обычной загрузки сервера. Затем, в regedit.exe откройте ключ:
HKLM\SYSTEM\CurrentControlSet\Control\SessionManager\MemoryManagement\IoPageLockLimit
Смысл Ваших действий состоит в пошаговом подборе значений этого ключа до наиболее оптимального, с точки зрения
изменений результатов эталонного тестирования, значения.
В этом ключе операционная система
считывает максимальное число байт, которые она можете использовать для операций
I/O. По умолчанию установлено значение 0, которому соответствует 512КБ.
Увеличивайте это значение по шагам, каждый раз прибавляя по 512КБ (например:
"512", "1024", и т.д.), и выполняйте после каждого
изменения эталонное тестирование вашей системы. Увеличивать этот параметр есть
смысл только до тех пор, пока вы наблюдаете увеличение пропускной способности
операций ввода – вывода, которое может проявляться в снижении временных затрат
на стандартные дисковые операции. Когда Вы перестанете наблюдать существенное
улучшение, возвратитесь в редактор реестра и уничтожьте последнее приращение.
Предостережение : Есть ограничение на максимальный размер значения этого ключа. Если Вы имеете 16 МБ ОЗУ, не устанавливайте IoPageLockLimit более 2048 байт; для 32МБ ОЗУ, не превышайте 4096 байт, и так далее.
Важное замечание:
Перечисленные Сергеем операции при не правильном или ошибочном применении могут привести к краху Вашей системы. Поэтому, позаботьтесь предварительно о наличии резервных копий баз и системы, сохраните рабочую конфигурацию NT, и промоделируйте возможные изменения на полигоне. Никогда не делайте более одного изменения конфигурации за один раз.
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.В предыдущих выпусках (см. КомпьютерПресс № 1, 3-5, 7, 9’2006) мы рассмотрели вопросы, касающиеся перехода на SQL Server 2005 путем миграции или обновления, а также основных сценариев использования SQL Server 2005. В настоящей статье речь пойдет о различных методах оптимизации запросов и о возможных подходах к решению задач, связанных с поиском проблем производительности.
SQL Server предоставляет сервисы, которые выполняются в динамической, постоянно изменяющейся среде. Поэтому регулярный мониторинг работы сервера позволяет определять проблемы еще на этапе их возникновения и оперативно принимать меры по их устранению. С накоплением статистики появляется понимание основных тенденций работы сервера. Регулярный сбор данных - даже в тех случаях, когда сервер работает без каких-либо проблем, - позволит создать так называемый базовый критерий производительности (server performance baseline), который может служить в качестве эталона при дальнейших замерах работы сервера.
Получив базовый эталон производительности, вы приобретаете возможность анализировать производительность запросов в различных сценариях. Делать замеры рекомендуется, как минимум, до, во время и после выполнения различных процедур оптимизации, что позволит убедиться в успешности предпринятых действий. Помимо этого регулярные стандартные замеры производительности (еженедельно, ежемесячно, ежеквартально и т.п.) обеспечат более четкую картину работы сервера.
Базовый эталон может использоваться для определения времени пиковой загрузки сервера и времени его простоя (peak и off-peak hours), времени отклика запросов и пакетов команд, времени выполнения процедур создания резервных копий и восстановления данных и т.п.
Производительность запросов
Производительность запросов следует рассматривать с двух точек зрения - ресурсов, применяемых для выполнения того или иного запроса (к ресурсам относятся объекты, к которым мы обращаемся в процессе выполнения запроса, заблокированные объекты и т.п.), и времени, затрачиваемого на выполнение запроса, - чем меньше время, необходимое для выполнения запроса, тем ниже вероятность того, что в процессе запроса мы заблокируем другие запросы и транзакции.
В состав SQL Server 2005 входят два основных средства для измерения производительности запросов - Performance Monitor и SQL Server Profiler. Помимо этого для получения информации о выполнении запросов можно использовать конструкции языка T-SQL из группы SET STATISTICS - SET STATISTICS IO, SET STATISTICS PROFILE и SET STATISTICS TIME - и динамические представления (Dynamic Management Views). Далее мы рассмотрим применение Performance Monitor и SQL Server Profiler более подробно.
Использование Performance Monitor
Утилита Performance Monitor применяется для анализа производительности аппаратных и программных ресурсов, включая память, использование сети, время работы процессора, а также информацию, относящуюся к работе SQL Server и других программных продуктов - Microsoft Message Queuing (MSMQ), Microsoft .NET Framework и Microsoft Exchange Server. В частности, можно применять Performance Monitor для мониторинга таких ресурсов SQL Server, как блокировки и транзакции.
Для добавления интересующих нас счетчиков необходимо выполнить следующие действия:
- В меню Start выбрать команду Run и в диалоговой панели Run ввести perfmon . В результате будет запущена консоль Performance Microsoft Management Console (MMC), отображающая графическое представление ряда счетчиков производительности.
- В окне System Monitor нажать правую кнопку на графике и выбрать команду Add Counters .
- В диалоговой панели Add Counters выбрать интересующий нас компьютер и объект для мониторинга. Объекты, относящиеся к SQL Server, имеют соответствующий префикс.
- Для выбора счетчиков необходимо выбрать либо опцию All counters , либо один или несколько счетчиков из раскрывающегося списка (рис. 1).
- После этого следует выбрать экземпляры базы данных или включить опцию All Instances .
- Нажатие кнопок Add и Close завершает добавление счетчиков.
После того как интересующие нас счетчики добавлены, мы можем либо просматривать их значения в виде графика, либо сохранять их в trace-файле (рис. 2).
В последнем случае необходимо раскрыть элемент Performance Logs and Alerts и щелкнуть по команде New Log Settings . В диалоговой панели New Log Settings необходимо задать имя протокола и нажать кнопку OK. Далее в диалоговой панели CounterLogName мы выбираем команду Add Counters . Добавление интересующих нас счетчиков происходит так же, как было описано выше. Обратите внимание на то, что большое количество счетчиков может повлиять на производительность системы. По завершении добавления счетчиков нажмите кнопку Close .
На вкладке General в разделе Sample data every можно задать частоту сэмплинга (снятия данных). Рекомендуется начать со средней частоты, например раз в 5 мин, а затем, при необходимости, уменьшить или увеличить ее. Обратите внимание на то, что чем короче интервал сэмплинга, тем больше требуется системных и дисковых ресурсов. Необходимо помнить, что интервалы короче, чем период квантования счетчика, также могут привести к неверному отображению данных. На вкладке Log Files можно сконфигурировать свойства файла-протокола, а на Schedule задается расписание мониторинга. Нажатие кнопки OK приводит к созданию протокола на начало сбора данных (рис. 3).
Для ручного завершения протоколирования нужно нажать правую кнопку на элементе Counter Logs и выбрать команду Stop.
Для просмотра собранных в файле протокола данных в Performance Monitor нужно выбрать элемент System Monitor , в панели задач - команду View Log Data , а в диалоговой панели System Monitor Properties на вкладке Source указать имя файла протокола.
Наиболее часто применяемые счетчики Performance Monitor, информацию из которых можно использовать для определения проблем, связанных с производительностью, разделены на следующие категории:
- доступ к данным - для аудита методов доступа к данным, применяемым самим SQL Server, используется объект SQLServer:Access Methods ;
- статистика - для мониторинга компиляции и рекомпиляции запросов применяется объект SQLServer:SQL Statistics . Он предоставляет информацию о том, как быстро и эффективно SQL Server обрабатывает запросы;
- транзакции - для определения числа транзакций в секунду используются счетчики, относящиеся к объектам SQLServer:Databases и SQLServer:Transactions ;
- блокировки - для аудита блокировок SQL Server, устанавливаемых на определенные типы ресурсов, применяется объект SQLServer:Locks (табл. 1).
Таблица 1. Счетчики SQL Server Profiler
|
Название счетчика |
Описание |
|
Подсчитывает число сканирований диапазонов (range scans) для индексов в секунду |
|
|
Подсчитывает число полных сканирований, выполненных за последнюю секунду |
|
|
Index Searches/sec |
Подсчитывает число поисков по индексу за последнюю секунду |
|
Table Lock Escalations/sec |
Подсчитывает число блокировок для таблицы |
|
Worktables Created/sec |
Подсчитывает число рабочих таблиц, созданных за последнюю секунду |
|
Batch Requests/sec |
Подсчитывает число пакетов команд Transact-SQL в секунду. Большое число пакетов означает хорошую пропускную способность |
|
SQL Compilations/sec |
Подсчитывает число компиляций запросов в секунду. Значение этого счетчика должно стать практически постоянным после выполнения пользователем основных действий |
|
SQL Re-Compilations/sec |
Подсчитывает число рекомпиляций запросов в секунду |
|
Объект SQLServer:Databases. Счетчик Transactions/sec |
Подсчитывает число транзакций, запущенных в базе данных за последнюю секунду |
|
Объект SQLServer:Transactions. Счетчик Longest Transaction Running Time |
Вычисляет число секунд с начала транзакции, которая была активна дольше, чем любая другая текущая транзакция. Если этот счетчик показывает очень длинную транзакцию, используйте системную хранимую процедуру sys.dm_tran_active_transactions() для получения информации о данной транзакции |
|
Объект SQLServer:Transactions. Счетчик Update conflict ratio |
Подсчитывает процент транзакций, применяющих изоляцию образов (snapshot isolation) для решения возникающих конфликтов при обновлении данных за последнюю секунду |
|
Average Wait Time (ms) |
Подсчитывает среднее время ожидания для каждого запроса на блокировку, вызвавшего ожидание |
|
Lock Requests/sec |
Подсчитывает число блокировок и преобразований блокировок в секунду |
|
Lock Wait Time (ms) |
Подсчитывает суммарное ожидание для блокировок за последнюю секунду |
|
Подсчитывает число запросов на блокировку в секунду, которые привели к ожиданию |
|
Вторая утилита, которой можно воспользоваться для измерения производительности запросов, - это SQL Server Profiler. Далее мы рассмотрим основные способы ее использования.
Использование SQL Server Profiler
Утилита SQL Server Profiler служит для измерения производительности отдельных запросов и запросов, входящих в состав хранимых процедур и пакетов команд на языке Transact-SQL. С ее помощью можно собирать информацию о производительности, включая время, затраченное на выполнение отдельной команды, время блокировки для выполнения команды, а также план выполнения (execution plan).
Для создания нового профиля необходимо выполнить ряд следующих шагов:
- Запустить SQL Server Profiler (Microsoft SQL Server 2005 => Performance Tools => SQL Server Profiler ).
- В меню File выбрать команду New Trace .
- В диалоговой панели Connect to Server выбрать необходимый сервер и нажать кнопку Connect .
- В диалоговой панели Trace Properties нужно задать имя профиля (Trace name) , а в списке Use the template выбрать один из доступных шаблонов или Blank , если шаблон не используется.
- Для сохранения результатов профилирования необходимо выбрать опцию Save to file для записи данных в файл и указать максимальный размер файла (значение по умолчанию - 5 Мбайт), опционально можно включить опцию Enable file rollover для автоматического создания нового файла по достижении указанного размера профиля. Вторая возможность - сохранение данных в таблице базы данных (Save to table) - опция Set maximum rows позволяет задать максимальное число записей.
- Для задания времени завершения трассировки можно использовать опцию Enable trace stop time (рис. 4).
Для задания событий и колонок данных для профиля требуется выполнить следующие шаги:
- В диалоговой панели Trace Properties перейти на вкладку Events Selection .
- Добавить или удалить события из профиля, используя таблицу классов событий.
- Для получения списка всех доступных событий включите опцию Show all events (рис. 5).
В табл. 2 перечислены наиболее часто используемые события SQL Server Profiler. Как и счетчики производительности, события SQL Server Profiler разделяются на ряд категорий, причем некоторые из них представляют интерес для решения наших задач.
Таблица 2. События SQL Server Profiler
|
Название события |
Описание |
|
Данное событие происходит по завершении выполнения вызова удаленной процедуры |
|
|
Данное событие осуществляется по завершении выполнения хранимой процедуры |
|
|
SP:StmtCompleted |
Данное событие происходит по завершении выполнения одной из команд языка Transact-SQL внутри хранимой процедуры |
|
SQL:StmtCompleted |
Данное событие осуществляется по завершении выполнения команды на языке Transact-SQL |
|
SQL:BatchCompleted |
Данное событие происходит по завершении выполнения пакета команд на языке Transact-SQL |
|
Данное событие выполняется, когда транзакция получает блокировку на какой-то ресурс |
|
|
Данное событие происходит, когда транзакция освобождает ранее заблокированный ресурс |
|
|
Данное событие осуществляется при превышении времени ожидания на получение блокировки, так как требуемый ресурс уже заблокирован другой транзакцией |
|
Как определить наличие блокировок
Каждый раз, когда в транзакции применяется какой-то ресурс (таблица, страница, индекс и т.п.), для него устанавливается блокировка. Если другая транзакция пытается обратиться к этому ресурсу и тип блокировки несовместим с уже установленной блокировкой, возникает новая блокировка.
Для определения наличия блокировок можно применять следующие способы:
- использовать утилиту SQL Server Management Studio Activity Monitor, которая показывает информацию о процессах, блокировках на уровне процесса и на уровне объекта. Для доступа к Activity Monitor в SQL Server Management Studio нужно выбрать элемент Management , а в нем - Activity Monitor и дважды щелкнуть по этому элементу. Activity Monitor позволяет просматривать:
Заблокированные объекты для каждого процесса - для определения запроса, приведшего к появлению блокировки, применяйте идентификатор процесса Server Process ID (SPID) на странице Process Info ,
Процессы, заставляющие другие процессы находиться в состоянии ожидания, - для выявления таких процессов воспользуйтесь колонкой Blocked By на странице Process Info (рис. 6);
- применять SQL Server Profiler для получения отчета о заблокированных процессах - в этом списке отображается информация о процессах, которые оставались заблокированными дольше указанного временно
Что Вы узнаете из этой статьи?
- Предназначение инструмента трассировки SQL Profiler
- Как отследить текст запроса к СУБД, в который транслируется запрос 1С
- Настройки фильтров трассировки
- Как выполнить персональную настройку SQL Profiler
Зачастую в работе возникает ситуация, когда запрос в 1С по каким-то причинам работает медленно, но анализ текста запроса не говорит нам о каких-либо проблемах.
В таком случае приходится изучать эту проблему на более низком уровне. Для этого нам нужно посмотреть текcт SQL-запроса и план запроса. Для этого можно использовать SQL Profiler.
SQL Profiler – предназначение
SQL Profiler – это программа, входящая в MS SQL Server, которая предназначена для просмотра всех событий, которые происходят в SQL-сервере. Иначе говоря, она нужна для записи трассировки.
В каких случаях данный инструмент может быть полезен 1С программисту? Прежде всего, можно получить текст запроса на языке SQL и посмотреть его план. Это также можно сделать и в технологическом журнале (ТЖ), но план запроса в ТЖ получается не таким удобным и требует наличия некоторых навыков и умений. К тому же в профайлере можно посмотреть не только текстовый, но и графический план выполнения запроса, что является более удобным.
Также профайлер позволяет узнать:
- запросы длиннее определенного времени
- запросы к определенной таблице
- ожидания на блокировках
- таймауты
- взаимоблокировки и т. д.
Анализ запросов с помощью SQL Profiler
Зачастую Profiler применяется именно для анализа запросов. И при этом нужно анализировать не все исполняемые запросы, а то, как определенный запрос на языке 1С транслируется в SQL, и обращать внимание на его план выполнения.
В частности, это бывает необходимо, чтобы понять, почему запрос выполняется медленно. Или при построении большого и сложного запроса необходимо удостовериться, что запрос на языке SQL не содержит соединений с подзапросом.
Для отслеживания запроса в трассировке выполняем следующие шаги:
1. Запускаем SQL Profiler: Пуск - Все программы - Microsoft SQL Server 2008 R2 - Средства обеспечения производительности - SQLProfiler .
2. Создаем новую трассировку: Файл – Создать трассировку (Ctrl+N).
3. Указываем сервер СУБД, на котором находится наша база данных и нажимаем Соединить :
Нам ничто не мешает выполнять трассировку сервера СУБД, находящегося на любом другом компьютере.
4. В появившемся окне Свойства трассировки переключаемся на закладку Выбор событий :
Так как нам нужны запросы и планы запросов, то необходимо включить соответствующие события. Для показа полного списка свойств и событий включаем флаги Показать все события и Показать все столбцы . Теперь необходимо выбрать только события, приведенные на рисунке ниже, остальные же – требуется отключить:
Описание этих событий:
- ShowplanStatisticsProfile– текстовый план выполнения запроса
- ShowplanXMLStatisticsProfile– графический план выполнения запроса
- RPC:Completed– текст запроса, если он выполняется как процедура (если выполняется запрос 1С с параметрами)
- SQL:BatchCompleted– текст запроса, если он выполняется как обычный запрос (если выполнялся запрос 1С без параметров)
6. На этом этапе необходима настройка фильтра для выбранных событий. Если фильтр не установлен, то мы будем видеть запросы для всех БД, расположенных на данном сервере СУБД. По кнопке Фильтры столбцов устанавливаем фильтр по имени базы данных:
Теперь мы видим в трассировке только запросы к БД «TestBase_8_2».
Также можно поставить фильтр и по другим полям, наиболее интересные из них:
- Duration (длительность)
- TextData (обычно это текст запроса)
- RowCounts (количество строк, возвращаемых запросом)
Допустим, нам необходимо «отловить» все запросы к таблице «_InfoRg4312» длительностью более 3-х секунд в базе данных «TestBase_8_2». Для этого необходимо:
a) Установить фильтр по базе данных (см. выше)
b) Установить фильтр по длительности (устанавливается в миллисекундах):
c) Установить фильтр по тексту запроса:
Для задания фильтра по тексту запроса используем маску. В случае необходимости отслеживать запросы, которые обращаются к нескольким таблицам, создается несколько элементов в разделе «Похоже на». Наложенные условия фильтров работают совместно.
7. Теперь запускаем трассировку с помощью кнопки Запустить в окне Свойства трассировки и наблюдаем события, попадающие под установленные фильтры, отображение которых было настроено.
Кнопки командной панели служат для управления трассировкой:
Назначение кнопок:
- Ластик – очищает окно трассировки
- Пуск – запускает трассировку
- Пауза – ставит трассировку на паузу, при нажатии на Пуск трассировка возобновляется
- Стоп – останавливает трассировку
8. Окно трассировки состоит из двух частей. В верхней части находятся события и их свойства, в нижней – информация, зависящая от типа событий. Для нашего примера здесь будет отображаться либо текст запроса, либо его план.
9. Запустим на выполнение запрос в консоли запросов 1С и посмотрим, как он отразится в профайлере:
По поведению трассировки видно, что запросов в итоге получилось несколько, и только один из них нам интересен. Остальные запросы – служебные.
10. Свойства событий дают возможность оценить:
- сколько секунд выполнялся запрос (Duration)
- сколько было логических чтений (Reads)
- сколько строк запрос вернул в результате (RowCounts) и т.д.
В нашем случае запрос выполнялся 2 миллисекунды, сделал 4 логических чтения и вернул 1 строку.
11. Если взглянуть на одно событие выше, то можно увидеть план запроса в графическом виде:
Из плана видно, что поиск осуществляется по индексу по цене, этот план нельзя назвать идеальным, так как индекс не является покрывающим, поля код и наименование получаются с помощью KeyLookup, что отнимает 50% времени.
Используя контекстное меню, полученный графический план запроса возможно сохранить в отдельный файл с расширением *.SQLPlan и открыть его в профайлере на другом компьютере или с помощью программы SQL Sentry Plan Explorer, которая является более продвинутой.
12. Если подняться еще выше, то мы увидим тот же план запроса, но уже в текстовом виде. Именно этот план отображается в ТЖ, ЦУП и прочих средствах контроля производительности 1С.
- В формат самого профайлера, то есть с расширением *.trc
- В формат xml
- Сделать из трассировки шаблон (См. следующий пункт)
- Cохранить полученную трассировку в виде таблицы базы данных. Это весьма удобный способ, когда, к примеру, нужно найти самый медленный запрос в трассировке или отфильтровать запросы по какому-либо параметру.
Затем выбираем базу данных на указанном сервере, указываем имя таблицы, куда будет сохранена трассировка. Можно использовать существующую таблицу, или дать ей новое имя, и тогда эта таблица будет создана автоматически.
Теперь возможно строить запросы любой сложности к нашей таблице: к примеру, искать наиболее долго выполняющиеся запросы.
Также нужно помнить, что Duration сохраняется в таблицу в миллионных долях секунды, и при выводе результата нужно переводить значение в миллисекунды. Также в таблице присутствует столбец RowNumber, показывающий номер данной строки в трассировке.
14. При частом использовании профайлера для анализа запросов постоянная настройка нужных событий и фильтров будет постоянно отнимать у вас много времени.
В данном случае нам помогут шаблоны трассировок, где мы настраиваем нужные нам фильтры и порядок колонок, а далее просто используем уже имеющийся шаблон при создании новой трассировки.
Для создания шаблона используем меню Файл – Шаблоны – Новый шаблон :
На первой закладке указываем тип сервера, имя шаблона и при необходимости ставим флаг для использования данного шаблона по умолчанию.
На второй закладке делаем выбор нужных событий и осуществляем настройку фильтров (как было показано выше).
При создании новой трассировки можем указать нужный шаблон, и тогда на второй закладке все фильтры и события заполнятся автоматически по созданному шаблону.
Бурмистров Андрей
При разработке прикладных модулей системы Lexema.ru периодически возникает потребность в анализе запросов к БД при работе экранных форм, запросов, отчётов, хранимых процедур и других объектов для диагностирования проблем. Для решения подобных задач предназначены инструменты профилирования SQL-запросов. Они позволяют:
- отслеживать события разных типов на сервере БД (выполнение запросов, хранимых процедур и т.п.)
- фильтровать события по различным критериям (имя БД, логин пользователя и т.п.)
- записывать последовательность действий в виде трассировки событий. В последствии трассировка может быть проанализирована, а также сохранена в файл или в БД.
- анализировать производительность (скорость выполнения) запросов для поиска и устранения "узких мест" системы
- и т.п.
В данной статье рассмотрены два инструмента:
- Lexema SQL Profiler, встроенный в моделлер приложения
- MS SQL Server Profiler, входящий в состав MS SQL Server
Lexema SQL Profiler
Данный инструмент предназначен для анализа запросов к серверу БД, инициируемых моделлером - программой для разработки прикладных конфигураций. С его помощью можно исследовать структуру БД прикладной логики и её объектов.
Для использования Lexema SQL Profiler запустите моделлер приложения. Нажмите на кнопку с изображением бочки в левом верхнем углу окна:
Для начала записи трассировки SQL-запросов моделлера к серверу БД нажмите кнопку "Запустить" на панели инструментов.
Выполните в моделлере действия, для которых требуется изучить запросы к БД. Например, после открытия списка моделей будет создана трассировка из нескольких запросов:
Таблица сверху содержит список событий (SQL-запросов), поле внизу - содержимое (SQL-код)
Поля таблицы:
- EventClass
- TextData
- Duration
- StartTime
- EndTime
- ApplicationName
- Reads
- Writes
- Transaction
К примеру, из трассировки событий при открытии списка моделей можно сделать следующие выводы: запрашиваются данные из трёх таблиц (L8_Model, L8_ModelProperty и L8_Namespace); самый длительный запрос производится к таблице L8_ModelProperty (242 мс).
MS SQL Server Profiler
MS SQL Server Profiler - это инструмент, входящий в состав пакета MS SQL Server, позволяющий перехватывать события сервера БД. События могут быть сохранены в файле трассировки или в БД для дальнейшего анализа или использования с целью повторения определенной серии шагов для воспроизведения проблемы для её диагностики. Типовые сценарии использования SQL Server Profiler:
- контроль производительности экземпляра SQL Server Database Engine
- отладка инструкций Transact-SQL и хранимых процедур
- анализ производительности путем выявления медленно работающих запросов
- выполнение стресс-тестирования и контроля качества посредством воспроизведения трассировок
- воспроизведение трассировки одного или нескольких пользователей
- проверка инструкций Transact-SQL и хранимых процедур на стадии разработки проекта в пошаговом режиме для гарантии правильного выполнения кода
- устранение проблем в SQL Server с помощью перехвата событий в производственной системе (production-версии) и воспроизведения их в отладочной (тестовой версии). Это очень полезная возможность, поскольку позволяет во время проверки или отладки продолжать использовать производственную систему.
- аудит и отслеживание действий, происходящих в экземпляре SQL Server. Эта возможность позволяет администратору безопасности просматривать любые события аудита, в частности успешные и неудачные попытки входа в систему и разрешений доступа к инструкциям и объектам
- сохранение результатов трассировки в формате XML, что обеспечивает стандартизованную иерархическую структуру хранения результатов трассировки. Это позволяет вносить изменения в существующие трассировки или создавать их вручную для последующего воспроизведения
- статистический анализ результатов трассировки, позволяющий производить группирование и анализ похожих классов событий. В результатах содержатся счетчики, полученные на основе группирования по одному столбцу
- предоставление возможности создания трассировки пользователям, не являющимся администраторами
- настройка шаблонов трассировки, которые затем могут быть использованы для последующих трассировок
Запуск и подключение к серверу
Запустить MS SQL Server Profiler можно из меню ОС Windows (меню "Пуск") или из меню программы MS SQL Server Management Studio (пункт Сервис - "SQL Server Приложение Profiler"). После запуска необходимо авторизоваться на сервере - ввести адрес сервера, имя учётной записи и пароль:
Настройка параметров трассировки
Затем перед началом трассировки необходимо задать её свойства:
- Имя трассировки - целесообразно задавать в том случае, если её планируется сохранить
- Использовать шаблон - определяет конфигурацию трассировки по умолчанию. А именно, он включает классы событий, которые нужно контролировать в SQL Server Profiler. Например, можно создать шаблон, указывающий используемые события, столбцы данных и фильтры.Шаблоны не выполняются, а сохраняются в файлах с расширением TDF.После сохранения шаблон управляет захватом данных, если запускается трассировка, основанная на этом шаблоне.
- Сохранить в файл с целью повторного открытия и анализа
- Сохранить в таблицу - в этому случае трассировка будет сохранена в БД и её можно будет анализировать средствами SQL
- Включить время остановки трассировки - необходимо в случае длительных наблюдений
Выбор типов событий и их атрибутов
Важным шагом настройки трассировки является выбор событий (необходимо перейти на соответствующую вкладку). Вкладка "Выбор событий" содержит сетку - таблицу, которая содержит каждый из классов событий, доступных для трассировки. На каждый класс событий в таблице приходится по одной строке. Классы событий могут незначительно различаться в зависимости от типа и версии сервера, к которому они подключены. Классы событий идентифицируются в столбце События сетки и группируются по категориям событий. В оставшихся столбцах перечислены столбцы данных, которые могут быть возвращены для каждого класса событий. Чтобы включить события в трассировку, установите флажок в столбце События.
По умолчанию данный список содержит только некоторые категории и типы событий в соответствии с выбранным шаблоном (см. выше). Также отображаются не все столбцы. Для более детальной настройки рекомендуется поставить галочки "Показать все события" и "Показать все столбцы".
Если трассировка производится для отслеживания запросов, производимых приложением Lexema.ru, достаточно будет отметить 3 типа события в 2х группах:
- Stored Procedures (хранимые процедуры)
- RPC:Completed - происходит при завершении удалённого вызова процедуры (RPC)
- SP:Completed - происходит при завершении хранимой процедуры
- TSQL - отслеживание выполнения инструкций TransactSQL, передаваемых клиентами на сервер БД
- SQL:BatchCompleted - возникает при завершении выполнения инструкции TransactSQL
Примечание : флажок в столбце Events может находится в трёх состояниях:
- галочка отсутствует - событие не отслеживается
- установлена чёрная галочка - выбраны все столбцы данных - для выбранного события будут собираться все возможные для него данные
- установлена серая галочка - выбраны только некоторые столбцы данных - для выбранного события будут собираться только некоторые данные в соответствии с отметками в столбцах
По умолчанию для некоторых событий выбраны не все столбцы (стоит серая галочка). Для выбора всех столбцов необходимо снять галочку и установить её заново. При этом будут установлены галочки для всех видимых столбцов данных.
Ниже перечислены другие полезные категории и типы событий:
- Security Audit
Настройка параметров фильтрации
Фильтры ограничивают накопление событий в трассировке. Если фильтр не установлен, то на выход трассировки возвращаются все события выбранных классов событий. Установка фильтра трассировки необязательна, однако фильтр минимизирует затраты ресурсов при трассировке. Фильтры для определений трассировки добавляются на вкладке "Выбор событий" в диалоговом окне "Свойства трассировки" или "Свойства шаблона трассировки".
При отслеживании событий, происходящих при использовании веб-интерфейса Lexema.ru конкретным пользователем, целесообразно установить фильтр "ApplicationName" похоже на <логин_пользователя>+&1, например, "PetrovAN&1", где PetrovAN - логин пользователя:
При такой настройке фильтра в трассировку попадут только события, генерируемые действиями указанного пользователя.
Отслеживание событий
Предположим, что после выставления настроек, описанных выше и запуска трассировки, пользователь с логином "airat" входит в систему и открывает реестр категорий доходов и расходов модуля "Домашняя бухгалтерия", а затем открывает один из документов (в качестве примера):
В результате в трассировке SQL Server Profiler будет отображён список событий:
Анализируя список событий трассировки можно увидеть, что одним из самых длительных запросов был следующий:
exec sp_executesql N "SELECT AS , AS , AS , AS , AS , AS , AS , AS , AS , AS , AS , AS , AS , AS , AS , AS , AS FROM " , N "@PrimaryKeyBoundary bigint,@TopCount bigint" , @ PrimaryKeyBoundary = NULL , @ TopCount = NULL
Судя по названию объекта (VTransactionCategory), это запрос на выборку списка категорий транзакций. Тип данного события - RPC:Completed (завершение выполнения удалённой процедуры).
Также в списке можно видеть событий типа SQL:BatchCompleted:
Это результат выполнения запроса (QuerySource) Lexema.ru.
Для поиска в тексте запросов, отслеженных в трассировке, необходимо нажать кнопку "Найти строку" (со значком бинокля) на панели инструментов или нажать комбинацию клавиш Ctrl+F:
Затем необходимо ввести искомый текст и выбрать столбец для поиска (текст запроса содержится в столбце TextData). После нажатия на кнопку "Следующий" курсор будет позиционирован на строку события, содержащего искомый текст.
В целях тестирования и отладки данные запросы могут быть выполнены повторно вручную. Для этого необходимо скопировать их текст, открыть SQL Server Management Studio, подключиться к соответствующему серверу, выбрать БД, создать запрос, вставить его текст и выполнить.