Создание набора коллекций трассировки SQL с помощью SQL Server Profiler
Область применения: 
SQL Server
В SQL Server можно использовать возможности трассировки на стороне сервера sql Server Profiler для экспорта определения трассировки, который можно использовать для создания набора коллекций, использующего универсальный тип сборщика трассировки SQL. Этот процесс состоит из двух частей.
- Создайте и экспортируйте трассировку SQL Server Profiler.
- Создание скрипта нового набора сбора на основе экспортированной трассировки.
Сценарий следующих процедур включает сбор данных о любой хранимой процедуре, требующей для выполнения 80 миллисекунд и более. Для выполнения этих процедур необходимо уметь следующее.
- Используйте SQL Server Profiler для создания и настройки трассировки.
- Используйте СРЕДУ SQL Server Management Studio для открытия, редактирования и выполнения запроса.
Создание и экспорт трассировки приложения SQL Server Profiler
В SQL Server Management Studio откройте SQL Server Profiler. (В Меню "Сервис" выберите SQL Server Profiler.)
В диалоговом окне Подключение к серверу нажмите кнопку Отмена.
В данном случае убедитесь, что настроено отображение значений продолжительности в миллисекундах (по умолчанию). Для этого выполните следующие шаги.
В меню Tools (Сервис) выберите пункт Options (Параметры).
Убедитесь, что в области Параметры отображения флажок «Показывать значения в столбце "Продолжительность" в микросекундах» не установлен.
Нажмите кнопку "ОК", чтобы закрыть диалоговое окно "Общие параметры ".
В меню "Файл" выберите "Создать трассировку".
В диалоговом окне "Подключение к серверу" выберите сервер, к которому требуется подключиться, и нажмите кнопку "Подключиться".
отображается диалоговое окно Свойства трассировки .
На вкладке Общие сведения выполнить следующее:
В поле Имя трассировки введите требуемое имя трассировки. В этом примере используется имя
SPgt140.В списке Использовать шаблонвыберите шаблон для трассировки. В этом примере выберите TSQL_SPs.
На вкладке Выбор событий выполните следующие действия.
Определите события для трассировки. Например, снимите все флажки в столбце События , кроме ExistingConnection и SP:Completed.
В правом нижнем углу установите флажок Показать все столбцы .
Выберите строку SP:Completed.
Прокрутите строку до столбца Продолжительность и установите флажок Продолжительность .
В правом нижнем углу выберите фильтры столбцов , чтобы открыть диалоговое окно "Изменить фильтр ". В окне Изменение фильтра выполните следующие действия.
В списке фильтров выберите "Длительность".
В окне логического оператора разверните узел "Больше" или "Равно ", введите
80значение и нажмите кнопку "ОК".
Нажмите кнопку "Запустить" , чтобы запустить трассировку.
На панели инструментов выберите "Остановить выбранную трассировку" или "Приостановить выбранную трассировку".
В меню "Файл" наведите указатель на "Экспорт", наведите указатель на определение трассировки скрипта и выберите для набора коллекции трассировки SQL.
В диалоговом окне Сохранить как введите имя определения трассировки в поле Имя файла и сохраните файл в требуемом расположении. В этом примере имя файла совпадает с именем трассировки (
SPgt140).Нажмите кнопку "ОК " при получении сообщения о том, что файл был успешно сохранен, а затем закройте SQL Server Profiler.
Создание скрипта нового набора элементов сбора из трассировки приложения SQL Server Profiler
В СРЕДЕ SQL Server Management Studio в меню "Файл " наведите указатель мыши на "Открыть", а затем выберите "Файл".
В диалоговом окне "Открыть файл" найдите и откройте файл, созданный в предыдущей процедуре (
SPgt140).Сохраненная информация о трассировке открывается в окне «Запрос» и объединяется в скрипт, который можно запустить для создания новых наборов элементов сбора.
Прокрутите скрипт и выполните следующие замены, отмеченные в текстовом комментарии скрипта.
Замените строку Имя набора сбора SQLTrace именем, которое будет использоваться для набора элементов сбора. В данном примере имя набора элементов сбора — SPROC_CollectionSet.
Замените строку Имя элемента сбора SQLTrace именем, которое будет использоваться для элемента сбора. В данном примере имя элемента сбора — SPROC_Collection_Item.
Выберите "Выполнить" , чтобы запустить запрос и создать набор коллекций.
В обозревателе объектов проверьте успешность создания набора элементов сбора. Для этого выполните следующие шаги.
Щелкните правой кнопкой мыши "Управление" и выберите " Обновить".
Разверните узел Управление, затем Сбор данных.
Набор элементов сбора SPROC_CollectionSet появится на одном уровне с узлом System Data Collection Sets . По умолчанию этот набор элементов сбора отключен.
С помощью обозревателя объектов измените свойства набора SPROC_CollectionSet, например режим сбора и расписание передачи. Остальные действия аналогичны тем, что выполнялись бы с наборами сбора системных данных, поставляемыми в составе поставщика данных.
Примеры
Следующий образец кода содержит скрипт, созданный в результате выполнения шагов, описанных в предыдущих процедурах.
/*************************************************************/
-- SQL Trace collection set generated from SQL Server Profiler
-- Date: 11/19/2022 12:55:31 AM
/*************************************************************/
USE msdb;
GO
BEGIN TRANSACTION
BEGIN TRY
-- Define collection set
-- ***
-- *** Replace 'SqlTrace Collection Set Name Here' in the
-- *** following script with the name you want
-- *** to use for the collection set.
-- ***
DECLARE @collection_set_id INT;
EXEC [dbo].[sp_syscollector_create_collection_set] @name = N'SPROC_CollectionSet',
@schedule_name = N'CollectorSchedule_Every_15min',
@collection_mode = 0, -- cached mode needed for Trace collections
@logging_level = 0, -- minimum logging
@days_until_expiration = 5,
@description = N'Collection set generated by SQL Server Profiler',
@collection_set_id = @collection_set_id OUTPUT;
SELECT @collection_set_id;
-- Define input and output variables for the collection item.
DECLARE @trace_definition XML;
DECLARE @collection_item_id INT;
-- Define the trace parameters as an XML variable
SELECT @trace_definition = convert(XML, N'<ns:SqlTraceCollector xmlns:ns"DataCollectorType" use_default="0">
<Events>
<EventType name="Sessions">
<Event id="17" name="ExistingConnection" columnslist="1,2,14,26,3,35,12" />
</EventType>
<EventType name="Stored Procedures">
<Event id="43" name="SP:Completed" columnslist="1,2,26,34,3,35,12,13,14,22" />
</EventType>
</Events>
<Filters>
<Filter columnid="13" columnname="Duration" logical_operator="AND" comparison_operator="GE" value="80000L" />
</Filters>
</ns:SqlTraceCollector>
');
-- Retrieve the collector type GUID for the trace collector type.
DECLARE @collector_type_GUID UNIQUEIDENTIFIER;
SELECT @collector_type_GUID = collector_type_uid
FROM [dbo].[syscollector_collector_types]
WHERE name = N'Generic SQL Trace Collector Type';
-- Create the trace collection item.
-- ***
-- *** Replace 'SqlTrace Collection Item Name Here' in
-- *** the following script with the name you want to
-- *** use for the collection item.
-- ***
EXEC [dbo].[sp_syscollector_create_collection_item] @collection_set_id = @collection_set_id,
@collector_type_uid = @collector_type_GUID,
@name = N'SPROC_Collection_Item',
@frequency = 900, -- specified the frequency for checking to see if trace is still running
@parameters = @trace_definition,
@collection_item_id = @collection_item_id OUTPUT;
SELECT @collection_item_id;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorNumber INT;
DECLARE @ErrorLine INT;
DECLARE @ErrorProcedure NVARCHAR(200);
SELECT @ErrorLine = ERROR_LINE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorNumber = ERROR_NUMBER(),
@ErrorMessage = ERROR_MESSAGE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
RAISERROR (
14684,
@ErrorSeverity,
1,
@ErrorNumber,
@ErrorSeverity,
@ErrorState,
@ErrorProcedure,
@ErrorLine,
@ErrorMessage
);
END CATCH;
GO