Программно сгенерить трассу профайлера. Ч. 1, 2

С оригиналом поста можно ознакомиться в блоге https://blogs.msdn.com/alexejs/.

Если кто зaбыл, SQL Profiler позволяет смотреть, чем занимается SQL Server, пока никто не видит. Какие запросы он обрабатывает, какие планы при этом генерит, какие транзакции открывает, какие закрывает, какие блокировки на что развешивает и т.д. На самом деле правильнее говорить о SQL Trace, и так нечто похожее, по-моему, называлось, дай бог памяти, во времена 6.5. Принципиальная разница произошла в семерке. В 6.5-м это был, грубо говоря, TDSный сниффер, отображавший SQLные команды, которые он перехватывал с клиента. Лет 15 назад водилась еще фришная тула SQLEye, которая действовала по такому же принципу. Сказать, что при этом происходило на сервере: какие еще процедуры вызвались в результате вызова вот этой процедуры, какие блокировки при этом развесились и пр., было нельзя , потому что это все были внешние инструменты по отношению к SQL Server. В 7.0 произошло разделение на SQL Trace и SQL Profiler. SQL Trace - внутренний компонент на уровне движка. Разные модули внутри SQL Server продуцируют разные события: процессор запросов, менеджер блокировок, кэш и т.д. SQL Trace аккуратно их принимает в свою помойку (global event sink), упорядочивает и отдает I/O-провайдеру. Существуют два I/O-провайдера: файловый и rowset. Первый задействуется для записи событий в бинарный trc-файл на сервере или сетевой шаре, его использует sp_ trace _create; второй используется, например, профайлером. Rowset-провайдер назвают еще клиентским провайдером. Это понятно – если результат не персистится, на том конце должен присутствовать клиент, постоянно отсасывающий результат. На случай, если провайдер не сумеет своевременно потребить события SQL Trace, они сохраняются в очередь во внутренние буфера. В BOL в описании к sys.dm_os_workers как раз на иллюстрации можно наблюдать TRACE QUEUE TASK. Это оно. По-умному оно называется background trace management thread и ответственно за то, чтобы очередировать ивенты в буфера, флушить файлового провайдера каждые сколько-то сек. (виднеется в sys.dm_os_wait_stats как SQLTRACE_BUFFER_FLUSH) и, наверно, за что-нибудь еще. Файловый провайдер устроен так, чтобы отразить все произошедшие события. Когда он не успевает их разгребать, в sys.dm_os_wait_stats вы увидите SQLTRACE_LOCK, т.е. будет плакать, биться об стенку, тормозить SQL Server, но ничего не потеряет. Rowset-провайдеру это по барабану. Когда приходит новое событие, а все буфера заняты, он начинает беззастенчиво херить непрочитанные события, освобождая буфера. По умолчанию каждый модуль молчит и попусту о своих событиях не рассказывает. В SQL Trace есть битовая карта с битиками на каждое событие, из которой модуль понимает, стоит ему дергаться или как. Как только появился клиент, заказавший трассу с таким-то набором событий, в битовой карте взводятся соответствующие флажки. Т.е. если нашелся хотя бы один клиент, подписавшийся на событие, соответствующий модуль начинает о нем сообщать по мере происхождения. Кол-во клиентов роли не играет. Раздать информацию о произошедшем событии всем заинтересованным в нем клиентам – дело SQL Trace. События, которые затем профайлер отображает как простое клиентское приложение стали тоже называть профайлерными, потому что людям свойственно обзывать вещи по видимой стороне явления. Не самый принципиальный вопрос с терминологией, называйте хоть горшком, просто имейте в виду эту разницу. При желании каждый может без особого труда написать свой профайлер; чтобы написать SQL Trace, нужно залезать глубоко в потроха на уровне сервера.

Одно из профайлерных событий мы недавно использовали для аудита выхода пользователя с SQL Server в качестве иллюстрации работы механизма Event Notification - https://blogs.msdn.com/alexejs/archive/2009/07/19/p20090719_5F00_1.aspx. Или, предположим, вы приобрели какое-то решение у независимого поставщика программного обеспечения. Исходников у вас нет; несмотря на это при помощи профайлера вы всегда можете видеть, что оно творит у вас на SQL Server с точностью до запроса. Профайлер позволяет идентифицировать проблемные места: медленные запросы, неиспользование индексов или статистики, сканы, дедлоки, избыточные компиляции и пр. Профайлер позволяет запоминать и перепроигрывать трассы. Допустим, можно сохранить трассу, перенести ее из девелоперской среды в тестовую и прокрутить там, чтобы понять, как оно себя почувствует. Полезная штука при тестировании и в консалтинговой практике, где заметную долю проектов составляют поиск и устранение тормозов. На этом я заканчиваю петь осанну профайлеру, про него можно прочитать в BOL (https://msdn.microsoft.com/ru-ru/library/ms187929.aspx), а еще лучше – скачать книжку "Mastering SQL Server Profiler", которая в бумажном варианте стоит тридцатник гринов, а в электронном доступна нахаляву, причем совершенно официально, дай Бог здоровья спонсорам. Взять ее можно, например, здесь - https://www.sqlservercentral.com/articles/books/65797/. Если пойдете ее скачивать, обратите внимание, что там по соседству лежит книжка "SQL Server Execution Plans" из той же серии The Art of High Performance SQL. Возьмите до кучи, пригодится. Еще на тему можно порекомендовать "Professional SQL Server 2005 Performance Tuning". Здесь, правда, пожлобились выкладывать ее всю в открытый доступ, но интересующая нас Chapter 5: Finding Problem Queries with SQL Profiler" отчасти есть на Google Books: https://books.google.ru/books?id=-M55F0pRy-EC. Еще можно порекомендовать книжку Kalen Delaney и др. "Inside Microsoft® SQL Server™ 2005: Query Tuning and Optimization". На MSDN есть две главы: Tracing and Profiling (https://msdn.microsoft.com/en-us/library/cc297235.aspx) и Plan Caching and Recompilation. Профайлер используется в куче мест типа System Center (https://www.microsoft.com/systemcenter/demos/sql/) или IBM Tivoli (https://publib.boulder.ibm.com/tividd/td/BSM/SC32-9084-00/en_US/HTML/bsmd247.htm)/. Короче, профайлер – вещь полезная. В других СУБД подобные вещи также имеются, например, PL/SQL Profiler, плюс их еще продают за деньги - https://www.embarcadero.com/products/profiler/. В SQL Server профайлер отдельных денег не стоит, т.к. входит во все платные редакции. Для бесплатных SQL Express 2005/2008 существует опенсорсный проект профайлера - https://sqlprofiler.googlepages.com/, причем, что особенно приятно, из России.

В этом посте я для иллюстрации 1) сначала создам простенькую трассу из интерфейса, 2) потом сделаем то же самое на Т-SQL, а потом 3) я презентую собственный хелпер-класс, который делает все то же, но избавляет от необходимости запоминать идентификаторы событий и колонок.

1. Открываем SQL Profiler (Start -> All Programs -> Microsoft SQL Server 2008 -> Performance Tools -> SQL Server Profiler). Говорим File -> New Trace. Соединяемся с желательным экземпляром SQL Server. На первом экране ничего не трогаем,

рис.1

переходим на закладку Events Selection. Отмечаем галку Show Events, отмечаем ивенты: в группе Errors and Warnings – Attention, Exception, Missing Column Statistics; в группе Sessions – Existing Connections; в группе Stored Procedures – RPC:Completed, SP:Completed, SP:StmtCompleted; в группе TSQL – SQL:BatchCompleted, SQL:StmtCompleted. В нижней части окна дается краткое описание, что означает событие, на котором вы стоите. Остальные события, которые она отметила автоматически в зависимости от выбранного темплейта, разотметьте.

Отметьте галку Show all columns. Аналогично событию в нижней части окна высвечивается подсказка про колонку, на которой стоит курсор. Поставьте в строчке напротив каждого выбранного события (там, где имеются чекбоксы) галки в колонках EventClass, TextData, Duration, CPU, Reads, Writes, NestLevel, SPID, ApplicationName, ObjectID, StartTime, EndTime, DatabaseName, HostName. Ручками, ручками. Ползая по каждой строчке и просматривая галки вдоль каждой колонки. Про то, что можно правой кнопкой кликнуть по заголовку колонки и сказать Select Column, тогда галки в ней появятся автоматом в строчке каждого выбранного события, я вам не скажу из вредности, потому что вы все равно досюда не дочитаете. Deselect Column, соответственно, очищает все галки в колонке. Очистите все колонки, не оговоренные в списке выше. Если чекбокса на пересечении события и колонки нет, значит, информация, за которую отвечает колонка, не поддерживается событием. Какие колонки какими событиями поддерживаются, написано в файле %ProgramFiles%\Microsoft SQL Server\100\Tools\Profiler\TraceDefinitions\Microsoft SQL Server TraceDefinition 10.0.1600.xml. Колонку SPID отметить / разотметить нельзя. Она отмечается автоматически, как только отмечена хотя бы одна колонка, и разотмечается, когда разотмечены все. Колонки Event Class в интерфейсе нет, она добавляется автоматически. Длительность (Duration) измеряется в микросекундах (fn_trace_gettable), но профайлер показывает ее в миллисекундах. Экономит на ширине колонки, зараза.

рис.2

Нажмите кнопку Organize Columns и при помощи кнопок Up и Down поставьте выбранные колонки в порядке, в котором они перечислены в списке выше.

рис.3

Нажмите кнопку Column Filters. Она позволяет учитывать не все подряд, а только избранные события, чьи значения в колонках удовлетворяют заданному критерию. Например, запросы, длительность выполнения которых превышала 10 мин. Или CPU потреблялось больше, чем на 50%. По умолчанию там уже торчит условие ApplicationName NotLike 'SQL Server Profiler%', чтобы профайлер не отслеживал собственную активность. Кликните на эту строчку так, чтобы в ней замигал курсор. Нажмите Enter. Добавьте еще одно условие: ApplicationName NotLike 'Report%' на тот случай, если у вас работает Reporting Service. Он способен быстро загрязнить трассу своими событиями.

рис.4

Нажмите кнопку Run. Трасса начнет выполняться, отслеживая выбранные события. Во, чего-то уже повалило.

рис.5

Это я знаю, что. Это трудится брокер и Event Notification на логофф, которую мы создали в предыдущем посте. А я думал, я ее отключил давно. Можно запаузить трассу и изменить ее свойства (список событий и пр.), после чего опять запустить.

Трассу можно сохранить следующим образом:

рис.6

Trace File – сохранение результатов работы трассы в trc-файл. Большая просьба не путать эту опцию, как недавно был тут один слушатель, с File I/O Provider. Нет, к нему она не имеет никакого отношения, это просто персистенция всего, что уже намониторил и насобирал профайлер, в файле того же бинарного формата, который используется файловым I/O провайдером SQL Trace.

Trace Template –сохранение определения трассы, т.е. выбранных событий, колонок и фильтров в tdf-файл. Системные tdfы лежат в %Program Files%\Microsoft SQL Server\100\Tools\Profiler\Templates\Microsoft SQL Server\100. Пользовательские, которые вы насоздаете и насохраняете, лежат в %userprofile%\AppData\Roaming\Microsoft\SQL Profiler\10.0\Templates\Microsoft SQL Server\100. Когда профайлер стартует, он читает определения трасс из этих двух мест, и показывает общий список в комбобоксе Use the template на рис.1.

Сохранение в Trace Table – это фикция. На сервере (SQL Trace) не существует API, чтобы трасса лилась в таблицу. Не путать с классом TraceTable из Microsoft.SqlServer.Management.Trace. Она может литься в trc-файл, из которого затем результаты можно прочитать при помощи функции fn_trace_gettable(), либо в rowset, ярким примером отображения которого является профайлер. Сохранение в таблицу – это персистенция накопленного в профайлере роусета, которую он делает как обычное клиентское приложение. Соответственно, при выборе этого пункта он спросит у вас сервер, базу, схему и таблицу, в которую сделает этот insert. Иногда спрашивают: откуда узнать структуру таблицы, которую требуется предварительно иметь, чтобы указать профайлеру? Это очевидно: select * into <таблица> from fn_trace_gettable('временный trc-файл', default) where 1 = 0. По тому же принципу работает Save to table при создании трассы на рис.1. Это тоже потребление роусет-провайдера. Единственно, у него хватает ума не спрашивать готовую таблицу, а создать ее самостоятельно:

рис.7

Здесь два момента я бы отметил, которые нужно иметь в виду. Первое – при перенаправлении результатов в таблицу трасса продолжает работать и после выхода из профайлера. Ее нужно останавливать процедурой sp_trace_setstatus @traceid, 0. Второе - не забываем, что роусет-провайдер может терять события. Когда мы находимся в профайлере, он об этом честно сообщает красной строкой. При сохранении результатов в таблицу не встречал, видимо, не везло. Или везло. Во всяком случае у нас всегда есть TRACEWRITE в sys.dm_os_wait_stats, по возрастанию которого можно предположить, что дело не в порядке. В серьезных случаях, когда потеря недопустима, профайлер использовать нельзя; только sp_trace_create.

Оставшиеся две опции относятся к сохранению трассы в виде XML-файла. Ну естественно, куда же нынче без XML? Save as Trace XML file генерирует XML, в котором сначала идет описание трассы (события, колонки, ...), а затем, собственно, ее результат, т.е. значения колонок для каждого встретившегося события.

рис.8

Save as Trace XML File For Replay – то же самое, что предыдущая опция, но сохраняются те события и колонки, которые необходимы для воспроизведения трассы. Они содержатся в темплейте трассы TSQL_Replay. Обратите внимание, что Save as Trace XML File For Replay может убрать несущественное, но не в силах добавить необходимое, если оно до момента сохранения не собиралось. Trace XML File For Replay не гарантирует, что сохраненный файл будет проигрываться, если заранее не озаботиться включением в трассу необходимых ему вещей. Самый простой способ, как я уже сказал, взять за основу шаблон TSQL_Replay. Проигрывать, разумеется, можно трассы, сохраненные не только в новомодном XML, но и в trc.

рис.9

 

Проигрывание делается из профайлерного меню Replay. Перед стартом будет спрошено о типе проигрывания. Существуют два типа проигрывания: Replay events in the order they were traced и Replay events using multiple threads. Первый тип означает, что все ивенты со всех спидов будут идти ровно в такой же последовательности, как при записи. Синхронизация обеспечивается благодаря колонке EventSequence. Это такой глобальный identity в масштабах трассы. Например, полезно, когда хотим понять, как произошли дедлоки. В этом типе воспроизведения также можно ставить брыкпойнты напротив полюбившихся событий, идти пошагово, проматывать до выделенной строчки (Run to Cursor), короче, основной функционал дебаггера. Второй тип означает, что внутри каждого спида последовательность событий будет сохранена, но сами спиды между собой, вообще говоря, коррелировать не обязаны. Работает понятно, шустрее; используется, в основном, при нагрузочном тестировании, когда с кучи соединений сыплется примерно одинаковая работа, никак не связанная с другими пользователями, а также при решении проблем внутри одного соединения, если остальные по каким-то причинам отфильтровать по спиду при записи не удалось. В принципе, можно пойти на закладку Advanced Replay Options, отметить Replay one SPID only и задать желаемый. Не надо ставить Number of replay threads = 1, если вы не выбрали тип проигрывания Replay events using multiple threads. Replay events in the order they were traced тоже может работать в многопотоке. Все спиды делятся примерно поровну между потоками, глобальная последовательность событий внутри и между спидами поддерживается в этом типе проигрывания независимо от кол-ва потоков.

рис.10

 

 

В 2005-м профайлер научился коррелировать свои трассы с перфмоном. Что логично, ибо и тот, и другой измерительные инструменты контроля производительности, и часто измеряемые в них параметры достаточно тесно взаимосвязаны. Корреляция означает приведение трассы профайлера и замеры перфмоновских каунтеров к одной временной оси, чтобы воочию судить, как себя вело то-то, когда здесь вот это вот так-то менялось. Единственный достойный сожаления момент – это нельзя посмотреть вживую, только в записи. Предполагается, что за некоторый отрезок времени у нас есть сохраненная в файл трасса и измерения счетчиков perfmon. Как сделать трассу за отрезок времени, мы знаем, а чтобы снять показания счетчиков, я предлагаю воспользоваться Способом 1 из поста "Доступ к счетчикам Perfmon из SQL Server. Способы 1 и 2". Там список каунтеров скидывался в файл:

typeperf -q > c:\temp\counters.txt

который затем редактировался. Интересующие каунтеры оставались, неинтересные удалялись. Имеет смысл, когда требуется много каунтеров. Я возьму для иллюстрации всего штук несколько, их проще набить руками. Вот содержание файла c:\Temp\counters.txt:

\Memory\Pages/sec

\Memory\Available MBytes

\Network Interface\Bytes Total/sec

\Physical Disc\Avg Disc Queue Length

\Physical Disc(*)\Disk Read Bytes/sec

\Physical Disc(*)\Disk Write Bytes/sec

\Process(sqlservr)\% Processor Time

\Process(sqlservr)\Pages Faults/sec

\Process(sqlservr)\Working Set

\Processor(*)\% Processor Time

\SQL Server:Buffer Manager\Buffer cache hit ratio

\SQL Server:Databases(*)\Data File(s) Size(KB)

\SQL Server:Databases(*)\Log File(s) Size(KB)

\SQL Server:Databases(*)\Transactions/sec

\SQL Server:SQL Statistics\Batch Requests/sec

\SQL Server:SQL Statistics\SQL Compilations/sec

\SQL Server:SQL Statistics\SQL Re-Compilations/sec

\System\Processor Queue Length

\System\Context Switches/sec

 

Делаем из него коллекцию для сбора данных:

C:\Users\Administrator>logman create counter -n SqlDataCollector -f csv -cf c:\Temp\counters.txt -o c:\Temp\SqlDataCollector.csv -si 00:00:10

The command completed successfully.

counter означает, что коллекция для сбора будет состоять из счетчиков Performance Monitor; -n SqlDataCollector – имя, которое мы даем этой коллекции; -f csv – формат файла, в который будут писаться результаты сбора; cf c:\Temp\counters.txt – файл, содержащий список счетчиков; -o c:\Temp\SqlDataCollector.csv – название выходного файла с результатами сбора; -si 00:00:10 – интервал сбора (10 сек.)

Можно проверить в Performance Monitor, что коллекция создалась успешно:

 

Пускаем ее:

C:\Users\Administrator>logman SqlDataCollector start

The command completed successfully.

Тут же запускаем созданную в этом пункте трассу профайлера (рис.5). Единственно, я, чтобы побольше насытить ее событиями, снял на рис.4 фильтр на Report Server. Черт с ним, пускай отмечается. Дадим им теперь минут несколько поработать вместе. Просто если у PerfMona и у трассы будут непересекающиеся диапазоны действия, профайлер откажется отображать счетчики PerfMona. Ну хватит, хорошенького понемножку. Останавливаем трассу, сохраняем ее (File -> Save As -> Trace File) в какой-нибудь trc. Останавливаем перфмоновский коллектор:

C:\Users\Administrator>logman SqlDataCollector stop

The command completed successfully.

Смотрим, что он насобирал. Открываем PerfMon, кликаем по графику правой кнопкой, говорим Properties, на закладке Source добавляем к Log Files файл результатов коллектора c:\temp\SqlDataCollector_000002.csv, как было оговорено при его создании (000001-й в моем случае был пробный пуск), при необходимости корректируем внизу границы Time Range. В отобразившемся графике выделяем все счетчики и говорим Scale Selected Counters. Красота какая.

 

Теперь посмотрим эту картинку в профайлере вместе с трассой. Идем в профайлер, открываем сохраненный trc-файл, говорим File -> Import Performance Data... Указываем файл с результатами коллектора, в данном случае c:\temp\SqlDataCollector_000002.csv. Отмечаем для отображения все содержащиеся в нем счетчики. Жмем ОК. Наступает ваще красота:

 

 

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

Еще одна полезная вещь в профайлере, которую стоит не забыть оговорить особо, - это генерация отдельного файла с планами запросов, которые можно затем посмотреть в графическом виде в SSMS. События типа Showplan Text, Showplan All и пр. присутствуют в классе событий Performance. SSMS умеет сохранять и отображать планы в XML-виде c расширением файла sqlplan. Отмечаем при создании или редактировании трассы среди событий класса Performance событие Showplan XML. В окне свойств трассы при этом появляется третья закладка – Events Extraction Settings. В ней можно задать, будут ли XMLи планов сохраняться отдельно и, если да, все вместе гуртом или каждый план в своем файле.

 

рис.11

Файл aaa.sqlplan остается пустым, пока не застопить трассу. Паузы недостаточно. После этого можно перейти в SSMS, сказать File -> Open, открыть aaa.sqlplan и рассматривать захваченные трассой планы запросов

рис.12

Если вы забыли сделать рис.11, не расстраивайтесь. Я тоже всегда забываю. Главное – не забыть включить событие Showplan XML. XMLи планов потом можно в любой момент выдрать из трассы, как сохраненной, так и запущенной.

рис.13

По умолчанию она норовит рассовать планы по разным файлам. Если вы хотите иметь их в одном, обратите внимание на комбобокс внизу диалога сохранения, в противном случае вместо aaa.sqlplan с, допустим, 50-ю планами запросов вы получите aaa.sqlplan, aaa_1.sqlplan, aaa_49.sqlplan.

рис.14

Extract Transact-SQL Events на рис.13 – идейно то же самое, только вместо планов будут сохранены тексты SQL-запросов.

Аналогично планам запросов можно захватывать информацию по дедлокам (рис.11, 13). Но про дедлоки лучше поговорим в отдельном посте как нибудь при случае.

В заключение 1-го пункта полезный совет для тех, кто, подобно мне, привык жать на паузу, разглядывая какое-нибудь событие, чтобы курсор то и дело не прыгал на строчку с новым. Профайлерное меню -> пункт Window -> убрать галку напротив Auto Scroll J.

2. Теперь проделаем те же действия средствами Т-SQL. Для создания трассы используется хранимая процедура sp_trace_create.

declare @traceid int

declare @maxfilesize bigint = 2

exec sp_trace_create @traceid output, @options = 2, @tracefile = N'c:\temp\TraceTest', @maxfilesize = @maxfilesize, @stoptime = null, @filecount = 5

select @traceid

------

3

Скрипт 1

Она создает трассу и возвращает ее @traceid, который затем используется для управления этой трассой. Значения параметров процедуры объясняются здесь: https://msdn.microsoft.com/en-us/library/ms190362.aspx. В данном случае мы создали трассу, которая будет писать результаты своих наблюдений в файл c:\temp\TraceTest.trc (параметр @tracefile). Когда файл заполнится до @maxfilesize = 2 (мегабайта), создастся новый файл c:\temp\TraceTest_1.trc, и запись событий пойдет в него. И т.д. @maxfilesize - параметр типа bigint. Поскольку sp_trace_create на самом деле расширенная хранимая процедура, она не умеет преобразовать intовую константу 2 в bigint, а выражение типа cast(2 as bigint) не воспринимается в качестве значения параметра ввиду дебильности языка T-SQL. Приходится извращаться с declare @maxfilesize bigint = 2. Параметр @filecount задает размер скользящего окна. @filecount = 5 означает, что будет поддерживаться не более 5-ти файлов, т.е. при создании 6-го 1-й, самый ранний, удалится. Такое поведение обуславливается параметром @options = 2 (TRACE_FILE_ROLLOVER). Можно еще взвести будильник на автоматическое окончание процесса трассировки в @stoptime, но мы этим не озабачивались. Пусть крутится, пока не надоест.

Набиваем в свежесозданную трассу ивенты и колонки (то, чем мы занимались в гуе на рис.2). Программно для этого служит хранимая процедура sp_trace_setevent (тоже расширенная). В кач-ве первого параметра она воспринимает идентификатор трассы, в качестве второго – идентификатор события, третьего – идентификатор колонки, четвертого – включаем это событие в трассу или, наоборот, отключаем. Идентификаторы событий и колонок приводятся в BOL на процедуру sp_trace_setevent (https://msdn.microsoft.com/en-us/library/ms186265.aspx). Например,

exec sp_trace_setevent @traceid = 3, @eventid = 16, @columnid = 15, @on = 1

включает @on = 1 в ранее созданную (Скрипт 1) трассу с @traceid = 3 событие Attention (@eventid = 16), колонку EndTime (@columnid = 15). И так для всех колонок события Attention, а затем для всех остальных событий и колонок.

exec sp_trace_setevent @traceid = 3, @eventid = 16, @columnid = 8, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 16, @columnid = 12, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 16, @columnid = 10, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 16, @columnid = 14, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 16, @columnid = 35, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 33, @columnid = 8, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 33, @columnid = 1, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 33, @columnid = 10, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 33, @columnid = 14, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 33, @columnid = 35, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 33, @columnid = 12, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 79, @columnid = 8, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 79, @columnid = 1, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 79, @columnid = 10, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 79, @columnid = 14, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 79, @columnid = 35, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 79, @columnid = 12, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 17, @columnid = 8, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 17, @columnid = 1, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 17, @columnid = 10, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 17, @columnid = 14, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 17, @columnid = 35, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 17, @columnid = 12, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 10, @columnid = 15, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 10, @columnid = 8, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 10, @columnid = 16, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 10, @columnid = 1, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 10, @columnid = 17, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 10, @columnid = 10, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 10, @columnid = 18, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 10, @columnid = 35, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 10, @columnid = 12, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 10, @columnid = 13, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 10, @columnid = 14, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 43, @columnid = 15, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 43, @columnid = 8, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 43, @columnid = 1, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 43, @columnid = 10, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 43, @columnid = 35, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 43, @columnid = 12, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 43, @columnid = 13, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 43, @columnid = 29, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 43, @columnid = 14, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 43, @columnid = 22, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 45, @columnid = 8, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 45, @columnid = 16, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 45, @columnid = 1, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 45, @columnid = 17, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 45, @columnid = 10, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 45, @columnid = 18, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 45, @columnid = 35, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 45, @columnid = 12, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 45, @columnid = 13, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 45, @columnid = 29, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 45, @columnid = 14, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 45, @columnid = 22, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 45, @columnid = 15, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 12, @columnid = 15, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 12, @columnid = 8, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 12, @columnid = 16, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 12, @columnid = 1, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 12, @columnid = 17, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 12, @columnid = 10, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 12, @columnid = 14, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 12, @columnid = 18, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 12, @columnid = 35, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 12, @columnid = 12, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 12, @columnid = 13, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 41, @columnid = 15, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 41, @columnid = 8, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 41, @columnid = 16, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 41, @columnid = 1, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 41, @columnid = 17, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 41, @columnid = 10, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 41, @columnid = 18, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 41, @columnid = 35, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 41, @columnid = 12, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 41, @columnid = 13, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 41, @columnid = 29, @on = 1

exec sp_trace_setevent @traceid = 3, @eventid = 41, @columnid = 14, @on = 1

 Скрипт 2

 

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

select * from sys.trace_events order by name

select * from sys.trace_columns order by name

Скрипт 3

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

рис.15

В отличие от Extract SQL Server Events скрипт получается правильный и даже работает, что позволяет существенно сэкономить время при написании Скрипта 2.

Остается установить условия фильтра (рис.4), что делается при помощи процедуры sp_trace_setfilter:

exec sp_trace_setfilter @traceid = 3, @columnid = 10, @logical_operator = 0, @comparison_operator = 7, @value = N'SQL Server Profiler%'

exec sp_trace_setfilter @traceid = 3, @columnid = 10, @logical_operator = 0, @comparison_operator = 7, @value = N'Report%'

Скрипт 4

Здесь @columnid – ровно тот же идентификатор колонки, что и в sp_trace_setevent. Как мы уже видели, он берется из sys.trace_columns. 10 - это колонка ApplicationName. Параметр @logical_operator принимает два значения 0 = and и 1 = или. @comparison_operator может иметь значения 0 (=), 1 (<>), 2 (>), 3 (<), 4 (>=), 5 (<=), 6 (like), 7 (not like). @value – выражение, к которому применяется оператор сравнения. Скрипт 4 означает, что условием фильтрации для трассы №3 будут два условия: ApplicationName not like N'SQL Server Profiler%' и ApplicationName not like N'Report%', соединенные оператором и.

Последний шаг – это нажатие на рис.2 кнопки Run. Выполняется при помощи процедуры exec sp_trace_setstatus.

exec sp_trace_setstatus @traceid = 3, @status = 1

Скрипт 5

 

@status = 1 означает Run, @status = 0 – Stop, @status = 2 – Delete. Удаление трассы тоже выполняется с помощью этой процедуры, процедуры sp_trace_delete в природе не существует. Перед удалением трассу полагается застопить.

Посмотреть, какие трассы определены на сервере, можно при помощи DMV sys.traces:

рис.16

либо по старинке при помощи функции ::fn_trace_getinfo(). Функция возвращает кое-какую информацию о трассе по ее traceid. Если в качестве параметра поставить 0, то обо всех имеющихся трассах:

select * from ::fn_trace_getinfo(0)

traceid property value

1 1 2

1 2 C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_108.trc

1 3 20

1 4 NULL

1 5 1

2 1 1

2 2 NULL

2 3 NULL

2 4 NULL

2 5 1

3 1 2

3 2 c:\temp\TraceTest_1.trc

3 3 2

3 4 NULL

3 5 1

Скрипт 6

 

Вид результата этой функции наводит на мысль о ее 2000-м происхождении, когда функции уже появились, а оператор PIVOT еще нет. Нынче это дело можно поправить:

with cte(TraceID, TraceOption, FileName, MaxSize_MB, StopTime, IsRunning)

as (select * from ::fn_trace_getinfo(0) pivot (min(value) for property in ([1], [2], [3], [4], [5])) pt)

select

TraceID,

case TraceOption when 2 then '2 = TRACE_FILE_ROLLOVER' when 4 then '4 = SHUTDOWN_ON_ERROR' when 6 then '6 = ROLLOVER_AND_SHUTDOWN' when 8 then '8 = PRODUCE_BLACKBOX' else TraceOption end,

FileName, MaxSize_MB, StopTime, IsRunning

from cte

---------------------------------------------------------------

TraceID (No column name) FileName MaxSize_MB StopTime IsRunning

1 2 = TRACE_FILE_ROLLOVER C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_108.trc 20 NULL 1

2 1 NULL  NULL  NULL  1

3 2 = TRACE_FILE_ROLLOVER c:\temp\TraceTest_1.trc 2 NULL  1Скрипт 7

Мы видим, что на данный момент имеются три трассы, все они работают. Первая трасса – системная, она запускается со стартом SQL Server по дефолту, и ее нельзя отключить с помощью sp_trace_setstatus. Только через exec sp_configure 'default trace enabled'. Подробнее про то, что это за трасса и для чего она нужна, можно прочитать в статье Kalen Delaney - https://www.sqlmag.com/Articles/ArticleID/48939/pg/1/1.html, либо скупое упоминание в BOL - https://msdn.microsoft.com/ru-ru/library/ms175513.aspx. Вторая трасса – это то, что было создано в профайлере в п.1. Ее TraceOption = 1. В документации на sp_trace_setevent такой опции не значится. Эмпирическим путем приходим к выводу, что она означает трассу, запущенную в профайлере. Третья трасса – это то, что мы создали только что. Можно проверить, какие события и колонки в ней собираются:

select f.eventid, e.name as EventName, e.category_id, cat.name as EventCategoryName, f.columnid, c.name as ColumnName

from ::fn_trace_geteventinfo(3) f

inner join sys.trace_events e on f.eventid = e.trace_event_id

inner join sys.trace_categories cat on e.category_id = cat.category_id

inner join sys.trace_columns c on f.columnid = c.trace_column_id

Скрипт 8

рис.17

Откуда-то возникла колонка 2 – BinaryData, которую я не заказывал. Остальное похоже на правду. Аналогично, можно посмотреть, какие условия фильтрации применены к трассе:

select f.columnid, c.name as ColumnName,

case f.logical_operator when 0 then '0 = AND' when 1 then '1 = OR' end LogicalOp,

cast(f.comparison_operator as varchar(2)) + ' = ' +

 case f.comparison_operator when 0 then 'Equal' when 1 then 'NotEqual' when 2 then 'GreaterThan' when 3 then 'LessThan' when 4 then 'GreaterOrEqual' when 5 then 'LessOrEqual' when 6 then 'Like' when 7 then 'NotLike' else '' end ComparisonOp,

value

from ::fn_trace_getfilterinfo(3) f

join sys.trace_columns c on f.columnid = c.trace_column_id

Скрипт 9

рис.18

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

select * from fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_107.trc', 1)

либо сказать ей общее наименование файла трассы, как при sp_trace_create, только с расширением, и количество файлов трасс от начала скользящего окна:

select * from fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log.trc', 5)

либо сказать общее наименование файла трассы и пусть читает все имеющееся скользящее окно:

select * from fn_trace_gettable('c:\temp\TraceTest.trc', default)

Скрипт 10

рис.19

Несмотря на то, что fn_trace_gettable выводит все колонки из sys.trace_columns, осмысленные величины присутствуют только в тех, что значились в Скрипте 8. Остальные константно NULLы. Не знаю, как вы читать, но я писать уже маленько замаялся.

Остановка и убиение трассы:

exec sp_trace_setstatus @traceid = 3, @status = 0

exec sp_trace_setstatus @traceid = 3, @status = 2

Скрипт 11

А п.3 допишу как-нибудь опосля.