SQL Profiler вирішує проблеми

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, який керує синхронізацією в рамках з'єднання, може набувати таких значень:

Параметру швидкості відтворення, Replay Rates, можна присвоїти одне з наступних значень:

Організація відтворення траси

Припустимо, необхідно відтворити трасу виконання підготовлених серверних операторів 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, або вирішувати проблеми підвищення продуктивності додатків - настав час застосувати свої знання на практиці.

При розробці прикладних модулів системи 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, підключитися до відповідного серверу, вибрати БД, створити запит, вставити його текст і виконати.

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

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

На вкладці «Загальні» необхідно вказати назву трасування. Вказати, куди будуть зберігатися дані знятої траси - на файл і / або в таблицю бази даних.

Великий інтерес представляє вкладка «Вибір подій»:

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

Отримайте 267 відеоуроків по 1С безкоштовно:

За замовчуванням трасування проходить по всім вказаних подій у всіх базах даних. Для того щоб накласти відбори на одержувані дані, необхідно натиснути кнопку «Фільтри стовпців ...»:

Наприклад, встановимо відбір за ідентифікатором інформаційної бази(Дізнатися ID бази можна за допомогою запиту SELECT DB_ID (N'ІмяБази ')).

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

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

Наприклад, я запускаю трасу на час проведення документа «Надходження товарів і послуг» з метою відстежити трудомісткі операції.

Після того як трасування отримана, необхідно її проаналізувати.

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

Для аналізу отриману трасування можна зберегти або в файл, або в таблицю. Ми збережемо в таблицю бази даних:

Профілювальник (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(Файл) потрібно вибрати NewTrace(Нова трасування) і підключитися до сервера SQL Server 2005, роботу якого ви будете відслідковувати. Під словом "трасування" мається на увазі сеанс збору інформації про роботу SQL Server 2005. Однак перед тим, як приступати до збору інформації, потрібно налаштувати параметри цього сеансу. Ця установка проводиться у вікні TraceProperties(Властивості трасування), яке відкривається автоматично перед початком сеансу трасування (рис. 11.1).

Мал. 11.1.Налаштування параметрів сеансу трасування

на вкладці General(Загальні) в списку Usethetemplate(Використовувати шаблон) ви можете вибрати найбільш підходящий шаблон для збору інформації в рамках вашого сеансу. В принципі, можна і не звертати увагу на налаштування шаблону, а вручну визначити параметри збору інформації (за допомогою сусідньої вкладки EventsSelection(Вибір подій)). Однак вказівка ​​правильного шаблону допоможе заощадити час і уникнути помилок. Тому на шаблонах зупинимося докладніше.

Шаблон - це збережені в спеціальному файлі з розширенням 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.

Як вже говорилося, зовсім необов'язково обмежуватися тільки набором готових шаблонів. Можна використовувати свої параметри сеансу трасування, налаштувавши їх на вкладці EventsSelection. У таблиці на цій вкладці ви повинні вибрати необхідні події (в рядках) і інформацію (в шпальтах), яка буде для них записуватися. Зверніть увагу, що за замовчуванням видно тільки невелика частина доступних рядків і стовпців. Щоб включити відображення всіх рядків і стовпців, потрібно встановити прапорці ShowAllEvents(Показати всі події) і ShowAllColumns(Показати всі стовпці).

Дуже часто буває так, що потрібно відстежувати тільки дії, що виконуються в певній базі даних, або певним додатком, або певним користувачем, або вибрати всі ці умови одночасно. Фільтри на збір інформації можна налаштувати, натиснувши кнопку ColumnFilters(Фільтри стовпців) на вкладці EventsSelection. Для кожного стовпця можна налаштувати запис тільки певних значень ( Like) Або заборона запису певних значень ( Not Like). За замовчуванням налаштований єдиний фільтр- Not Likeдля стовпця ApplicationName. Він змушує ігнорувати всі події додатки SQL Server Profiler, т. Е. Все події, що відносяться до самого процесу збору інформації трасування. Цей фільтр краще не видаляти, тому що в іншому випадку може виникнути позитивний Зворотній зв'язокз нескінченною записом інформації.

За допомогою ще однієї кнопки OrganizeColumns(Організувати стовпці), яка розташована на вкладці EventsSelection, Можна налаштувати порядок стовпців для відображення або записи в профілювальник. Зверніть увагу на розділ Group(Група) в цьому списку. Для тих стовпців, які в нього поміщені, буде автоматично вироблятися угруповання. Якщо ви помістіть в цей розділ тільки один стовпець, то при перегляді у вас з'явиться можливість використовувати дуже зручний режим AggregatedView(Агреговане уявлення) (коли інформація автоматично згрупована, наприклад, по базі даних, за додатком, імені користувача і т. П., І записи для потрібної бази даних, додатки або користувача можна розкривати і згортати).

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

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

q можна передати в якості джерела інформації Database Tuning Advisor;

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

q можна пред'явити розробникам на підтвердження своїх претензій до додатка.

Відзначимо деякі моменти, які пов'язані з протоколированием сеансу трасування в файл:

q 5 Мбайт, якими обмежується розмір файлу за замовчуванням, це дуже мало. При профілювання робочого сервера цей розмір набирається за хвилини. Правда, за замовчуванням встановлений прапорець Enablefilerollover(Включити зміну файлів), т. Е. Після заповнення одного файлу автоматично буде створений другий файл, до імені якого додасться номер 1, потім - 2 і т. П., Але працювати з великою кількістю файлів не завжди зручно. Якщо ви збираєте інформацію для передачі Database Tuning Advisor, то краще налаштувати граничний розмір файлу в 1 Гбайт (за допомогою параметра Setmaximumfilesize(Налаштувати максимальний розмірфайлу) на вкладці General). Запис трасування в файл найчастіше проводиться з робочої станціїадміністратора, тому місце на диску потрібно саме на робочої станції, а не на сервері;

q параметр Serverprocessestracedata(Сервер обробляє дані трасування) можна використовувати для збільшення надійності запису інформації трасування. За замовчуванням обробкою даних трасування займається SQL Server Profiler, і відбувається це на тому комп'ютері, на якому він запущений (не обов'язково на сервері). Якщо встановити цей прапорець, то обробкою інформації трасування буде займатися сервер. Це гарантує, що вся інформація трасування буде зібрана (при знятому прапорці в моменти пікового навантаження сервера частина інформації може бути пропущена), але збільшить навантаження на сервер.

Інший варіант запису інформації трасування - запис в таблицю SQL Server. Таблиця з потрібним набором стовпців буде створена автоматично. Ви можете лише налаштувати максимальну кількість записів в цій таблиці. Зверніть увагу, що на цій вкладці максимальну кількість записів вказується в тисячах.

Останній параметр на вкладці General- EnableTracestoptime(Включити час зупинки трасування). Ви можете вказати час, коли трасування буде відключена автоматично. Зазвичай має сенс відключати трасування перед початком якихось службових операцій, які з точки зору протоколювання вас не цікавлять ( резервне копіювання, Масове завантаження даних, процесинг кубів OLAP і т. П.).

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

Мал. 11.2.Перегляд інформації в ході сеансу трасування

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

q якщо на вкладці OrganizeColumnsу властивостях шаблону ви вибрали стовпці для угруповання, то можна згрупувати за цими стовпцями записи в вікні перегляду. Для цієї мети в меню View(Вид) передбачена команда GroupedView(Згруповані подання);

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

Мал. 11.3.режим відображення AggregatedView

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

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

· Визначити сеанс трасування, в ході якого обов'язково повинна записуватися інформація для стовпців StartTimeі EndTime;

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

· Відкрити зібрану інформацію з файлу трасування в профілювальник, а потім скористатися командою ImportPerformanceData(Імпортувати дані продуктивності) з меню 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
• Як відстежити текст запиту до СУБД, в який транслюється запит 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
• Зробити з трасування шаблон (Див. Наступний пункт)
• Збережемо отриману трасування у вигляді таблиці бази даних. це вельми зручний спосіб, Коли, наприклад, потрібно знайти найповільніший запит в трасуванні або відфільтрувати запити по якомусь параметру.

Потім вибираємо базу даних на зазначеному сервері, вказуємо ім'я таблиці, куди буде збережена трасування. Можна використовувати існуючу таблицю, або дати їй нове ім'я, і ​​тоді ця таблиця буде створена автоматично.

Тепер можливо будувати запити будь-якої складності до нашої таблиці: наприклад, шукати найбільш довго виконуються запити.

Також потрібно пам'ятати, що Duration зберігається в таблицю в мільйонних частках секунди, і при виведенні результату потрібно переводити значення в мілісекунди. Також в таблиці присутній стовпець RowNumber, що показує номер цього рядка в трасуванні.

14. При частому використанні профайлера для аналізу запитів постійна настройка потрібних подій і фільтрів буде постійно відбирати у вас багато часу.

В даному випадку нам допоможуть шаблони трассіровок, де ми налаштовуємо потрібні нам фільтри і порядок колонок, а далі просто використовуємо вже наявний шаблон при створенні нової трасування.

Для створення шаблону використовуємо меню Файл - Шаблони - Новий шаблон:

На першій закладці вказуємо тип сервера, ім'я шаблону і при необхідності ставимо прапор для використання даного шаблону за замовчуванням.

На другій закладці робимо вибір потрібних подій і здійснюємо настройку фільтрів (як було показано вище).

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

Бурмістров Андрій