SQL Server Profiler를 사용하여 SQL 추적 컬렉션 집합 만들기

아티클
12/30/2023

적용 대상: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를 엽니다. (On the 도구 메뉴에서 SQL Server 프로파일러를 선택합니다.)
서버에 연결 대화 상자에서 취소를 선택합니다.
이 시나리오에서는 기간 값을 밀리초로 표시(기본 설정)하도록 구성되어 있는지 확인합니다. 이렇게 하려면 다음 단계를 수행합니다.
1. 도구 메뉴에서 옵션을 선택합니다.
2. 표시 옵션 영역에서 기간 열에 값 표시(마이크로초) 확인란이 선택 취소되어 있는지 확인합니다.
3. 확인을 선택하여 일반 옵션 대화 상자를 닫습니다.
파일 메뉴에서 새 추적을 선택합니다.
서버로 커넥트 대화 상자에서 연결할 서버를 선택한 다음, 커넥트 선택합니다.

추적 속성 대화 상자가 나타납니다.
일반 탭에서 다음을 수행합니다.
1. 추적 이름 상자에 추적에 사용할 이름을 입력합니다. 이 예제의 경우 추적 이름은 .입니다 SPgt140.
2. 템플릿 사용목록에서 추적에 사용할 템플릿을 선택합니다. 이 예제에서는 TSQL_SPs 선택합니다.
이벤트 선택 탭에서 다음을 수행합니다.
1. 추적에 사용할 이벤트를 식별합니다. 이 예제에서는 Existing커넥트ion 및 SP:Completed를 제외한 이벤트 열의 모든 검사 상자를 지웁니다.
2. 오른쪽 아래 모서리에서 검사 모든 열 표시 상자를 선택합니다.
3. SP:Completed 행을 선택합니다.
4. 열을 스크롤하여 기간 열을 찾은 다음 기간 확인란을 선택합니다.
오른쪽 아래 모서리에서 열 필터를 선택하여 필터 편집 대화 상자를 엽니다. 필터 편집 대화 상자에서 다음을 수행합니다.
1. 필터 목록에서 기간을 선택합니다.
2. 부울 연산자 창에서 보다 큼 또는 같음 노드를 확장하고 값으로 입력 80 한 다음 확인을 선택합니다.
실행을 선택하여 추적을 시작합니다.
도구 모음에서 선택한 추적 중지 또는 선택한 추적 일시 중지를 선택합니다.
파일 메뉴에서 내보내기를 가리키고, 추적 정의 스크립트를 가리킨 다음, SQL 추적 컬렉션 집합을 선택합니다.
다른 이름으로 저장 대화 상자의 파일 이름 상자에 추적 정의에 사용할 이름을 입력한 다음 원하는 위치에 저장합니다. 이 예제에서 파일 이름은 추적 이름(SPgt140)과 같습니다.
파일이 성공적으로 저장되었다는 메시지를 받으면 확인을 선택한 다음 SQL Server Profiler를 닫습니다.

SQL Server Profiler 추적에서 새 컬렉션 집합 스크립티

SQL Server Management Studio의 파일 메뉴에서 열기를 가리킨 다음 파일을 선택합니다.
파일 열기 대화 상자에서 이전 절차(SPgt140)에서 만든 파일을 찾아 엽니다.

저장한 추적 정보는 쿼리 창에서 열리고 새 컬렉션 집합을 만들기 위해 실행할 수 있는 스크립트로 병합됩니다.
스크립트를 스크롤하여 스크립트 주석 텍스트에 기록된 다음 대체 항목을 만듭니다.
- 여기서 SQLTrace 컬렉션 집합 이름을 컬렉션 집합에 사용할 이름으로 바꿉니다. 이 예제에서는 컬렉션 집합 의 이름을 SPROC_CollectionSet.
- 여기서 SQLTrace 컬렉션 항목 이름을 컬렉션 항목에 사용할 이름으로 바꿉니다. 이 예제에서는 컬렉션 항목 의 이름을 SPROC_Collection_Item.
실행을 선택하여 쿼리를 실행하고 컬렉션 집합을 만듭니다.
개체 탐색기 컬렉션 집합이 생성되었는지 확인합니다. 이렇게 하려면 다음 단계를 수행합니다.
1. 관리를 마우스 오른쪽 단추로 클릭한 다음 새로 고침을 선택합니다.
2. 관리를 확장한 다음 데이터 수집을 확장합니다.
SPROC_CollectionSet 컬렉션 집합은 시스템 데이터 컬렉션 집합 노드와 동일한 수준에 나타납니다. 기본적으로 컬렉션 집합은 사용하지 않도록 설정됩니다.
개체 탐색기를 사용하여 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

SQL Server Profiler를 사용하여 SQL 추적 컬렉션 집합 만들기

SQL Server Profiler 추적 만들기 및 내보내기

SQL Server Profiler 추적에서 새 컬렉션 집합 스크립티

예제

피드백

피드백

추가 리소스