Поделиться через

  • Статья

Как использовать приложение SQL Server Profiler для создания набора сбора SQL-трассировки

В SQL Server 2008 можно использовать возможности серверной трассировки приложения Приложение SQL Server Profiler, чтобы экспортировать определение трассировки для создания набора сбора, использующего общий тип сборщика «Универсальная трассировка SQL». Этот процесс состоит из двух частей.

  1. Создание и экспорт трассировки приложения Приложение SQL Server Profiler.

  2. Создание сценария нового набора сбора на основе экспортированной трассировки.

Сценарий следующих процедур включает сбор данных о любой хранимой процедуре, требующей для выполнения 80 миллисекунд и более. Для выполнения этих процедур необходимо уметь следующее.

  • Использовать приложение Приложение SQL Server Profiler для создания и настройки трассировки.

  • Использовать среду SQL Server Management Studio для открытия, изменения и выполнения отчета.

Создание и экспорт трассировки приложения SQL Server Profiler

  1. В среде SQL Server Management Studio откройте приложение Приложение SQL Server Profiler. (В меню Сервис выберите SQL Server Profiler).

  2. В диалоговом окне Соединения с сервером нажмите кнопку Отмена.

  3. В этом сценарии убедитесь, что значения продолжительности настроены для отображения в миллисекундах (по умолчанию). Для этого выполните следующие шаги.

    1. В меню Сервис выберите Параметры.

    2. В области Параметры отображения убедитесь, что снят флажок Показывать значения в столбце «Продолжительность» в микросекундах (только для SQL Server 2005 и более поздних версий).

    3. Нажмите кнопку ОК, чтобы закрыть диалоговое окно Общие параметры.

  4. В меню Файл выберите пункт Создать трассировку.

  5. В диалоговом окне Соединение с сервером выберите нужный сервер и нажмите кнопку Соединить.

    Появится диалоговое окно Свойства трассировки.

  6. На вкладке Общие выполните следующие действия.

    1. В поле Имя трассировки введите нужное имя трассировки. В этом примере используется имя SPgt80.

    2. В списке Использовать шаблон выберите нужный шаблон для трассировки. В этом примере выберите TSQL_SPs.

  7. На вкладке Выбор событий сделайте следующее.

    1. Определите события, которые будут использоваться для трассировки. В этом примере снимите все флажки в столбце События за исключением ExistingConnection и SP:Completed.

    2. В правом нижнем углу установите флажок Показать все столбцы.

    3. Щелкните строку SP:Completed.

    4. Прокрутите строку до столбца Продолжительность и установите флажок Продолжительность.

  8. В правом нижнем углу щелкните Фильтры столбцов, чтобы открыть диалоговое окно Изменить фильтр. В диалоговом окне Изменить фильтр выполните следующие действия.

    1. В списке фильтров щелкните Продолжительность.

    2. В окне логического оператора раскройте узел Больше или равно, введите значение 80 и нажмите кнопку ОК.

  9. Чтобы запустить трассировку, нажмите кнопку Выполнить.

  10. На панели инструментов щелкните Остановить выбранную трассировку или Приостановить выбранную трассировку.

  11. В меню Файл укажите пункт Экспорт, затем Создать определение трассировки и щелкните Для набора сбора SQL-трассировки.

  12. В диалоговом окне Сохранить как в поле Имя файла введите имя определения трассировки и сохраните файл в нужном расположении. В этом примере имя файла совпадает с именем трассировки (SPgt80).

  13. Нажмите кнопку ОК, когда получите сообщение, что файл успешно сохранен, и закройте приложение Приложение SQL Server Profiler.

Создание сценария нового набора сбора из трассировки приложения SQL Server Profiler

  1. В среде SQL Server Management Studio в меню Файл выберите команду Открыть, а затем Файл.

  2. В диалоговом окне Открыть файл найдите и откройте файл, созданный в предыдущей процедуре (SPgt80).

    Сохраненная информация о трассировке открывается в окне «Запрос» и объединяется в сценарий, который можно запустить для создания новых наборов сбора.

  3. Прокрутите сценарий и выполните следующие замены, отмеченные в текстовом комментарии сценария.

    • Замените строку SQLTrace Collection Set Name Here именем, которое будет использоваться для набора сбора. В этом примере имя набора сбора SPROC_CollectionSet.

    • Замените строку SQLTrace Collection Item Name Here именем, которое будет использоваться для элемента сбора. В этом примере имя элемента сбора SPROC_Collection_Item.

  4. Нажмите кнопку Выполнить, чтобы запустить запрос и создать набор сбора.

  5. В обозревателе объектов убедитесь, что набор сбора создан. Для этого выполните следующие шаги.

    1. Щелкните правой кнопкой мыши Управление и выберите команду Обновить.

    2. Разверните узел Управление, затем узел Сбор данных.

    Набор сбора SPROC_CollectionSet появится на том же уровне, что и узел Системные наборы сбора данных. По умолчанию набор сбора отключен.

  6. С помощью обозревателя объектов измените свойства набора сбора SPROC_CollectionSet, например режим сбора и расписание передачи. Выполните те же процедуры, что и для наборов сбора системных данных, поставляемых со сборщиком данных.

Пример

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

/*************************************************************/
-- SQL Trace collection set generated from SQL Server Profiler
-- Date: 11/19/2007  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