SQL Server Profiler를 사용하여 SQL 추적 컬렉션 집합 만들기Use SQL Server Profiler to Create a SQL Trace Collection Set

SQL Server 2017SQL Server 2017 에서 SQL Server 프로파일러SQL Server Profiler 의 서버 쪽 추적 기능을 이용하여 일반 SQL 추적 수집기 유형을 사용하는 컬렉션 집합을 만들기 위한 추적 정의를 내보낼 수 있습니다.In SQL Server 2017SQL Server 2017 you can exploit the server-side trace capabilities of SQL Server 프로파일러SQL Server Profiler to export a trace definition that you can use to create a collection set that uses the Generic SQL Trace collector type. 이 프로세스는 두 부분으로 구성되어 있습니다.There are two parts to this process:

  1. SQL Server 프로파일러SQL Server Profiler 추적을 만들고 내보냅니다.Create and export a SQL Server 프로파일러SQL Server Profiler trace.

  2. 내보낸 추적을 기반으로 새 컬렉션 집합을 스크립팅합니다.Script a new collection set based on an exported trace.

    다음 절차에 대한 시나리오에는 완료하는 데 80밀리초 이상이 걸리는 저장 프로시저에 대한 정보를 수집하는 과정이 포함되어 있습니다.The scenario for the following procedures involves collecting data about any stored procedure that requires 80 milliseconds or longer to complete. 이 프로시저를 완료하려면 다음을 수행할 수 있어야 합니다.In order to complete these procedures you should be able to:

  • SQL Server 프로파일러SQL Server Profiler 를 사용하여 추적을 만들고 구성합니다.Use SQL Server 프로파일러SQL Server Profiler to create and configure a trace.

  • SQL Server Management StudioSQL Server Management Studio 를 사용하여 쿼리를 열고 편집하며 실행합니다.Use SQL Server Management StudioSQL Server Management Studio to open, edit, and execute a query.

SQL Server Profiler 추적 만들기 및 내보내기Create and export a SQL Server Profiler trace

  1. SQL Server Management StudioSQL Server Management Studio에서 SQL Server 프로파일러SQL Server Profiler를 엽니다.In SQL Server Management StudioSQL Server Management Studio, open SQL Server 프로파일러SQL Server Profiler. 도구 메뉴에서 SQL Server Profiler를 클릭하면 됩니다.(On the Tools menu, click SQL Server Profiler.)

  2. 서버에 연결 대화 상자에서 취소를 클릭합니다.In the Connect to Server dialog box, click Cancel.

  3. 이 시나리오에서는 기간 값을 밀리초로 표시(기본 설정)하도록 구성되어 있는지 확인합니다.For this scenario, ensure that duration values are configured to display in milliseconds (the default). 이렇게 하려면 다음 단계를 수행합니다.To do this, follow these steps:

    1. 도구 메뉴에서 옵션을 클릭합니다.On the Tools menu, click Options.

    2. 표시 옵션 영역에서 기간 열에 값 표시(마이크로초) 확인란이 선택 취소되어 있는지 확인합니다.In the Display Options area, ensure that the Show values in Duration column in microseconds check box is cleared.

    3. 확인 을 클릭하여 일반 옵션 대화 상자를 닫습니다.Click OK to close the General Options dialog box.

  4. 파일 메뉴에서 새 추적을 클릭합니다.On the File menu, click New Trace.

  5. 서버에 연결 대화 상자에서 연결할 서버를 선택한 다음 연결을 클릭합니다.In the Connect to Server dialog box, select the server that you want to connect to, and then click Connect.

    추적 속성 대화 상자가 나타납니다.The Trace Properties dialog box appears.

  6. 일반 탭에서 다음을 수행합니다.On the General tab, do the following:

    1. 추적 이름 상자에서 추적에 사용할 이름을 입력합니다.In the Trace name box, type the name that you want to use for the trace. 이 예에서 추적 이름은 SPgt80입니다.For this example, the trace name is SPgt80.

    2. 템플릿 사용목록에서 추적에 사용할 템플릿을 선택합니다.In the Use the template list, select the template to use for the trace. 이 예에서는 TSQL_SPs를 클릭합니다.For this example, click TSQL_SPs.

  7. 이벤트 선택 탭에서 다음을 수행합니다.On the Events Selection tab, do the following:

    1. 추적에 사용할 이벤트를 지정합니다.Identify the events to use for the trace. 이 예에서는 이벤트 열에서 ExistingConnectionSP:Completed를 제외한 모든 확인란을 선택 취소합니다.For this example, clear all check boxes in the Events column, except for ExistingConnection and SP:Completed.

    2. 오른쪽 아래 모서리에 있는 모든 열 표시 확인란을 선택합니다.In the lower-right corner, select the Show all columns check box.

    3. SP:Completed 행을 클릭합니다.Click the SP:Completed row.

    4. 열을 스크롤하여 기간 열을 찾은 다음 기간 확인란을 선택합니다.Scroll across the row to the Duration column, and then select the Duration check box.

  8. 오른쪽 아래 모서리에 있는 열 필터 를 클릭하여 필터 편집 대화 상자를 엽니다.In the lower-right corner, click Column Filters to open the Edit Filter dialog box. 필터 편집 대화 상자에서 다음을 수행합니다.In the Edit Filter dialog box, do the following:

    1. 필터 목록에서 기간을 클릭합니다.In the filter list, click Duration.

    2. 부울 연산자 창에서 크거나 같음 노드를 확장하고 80 을 값으로 입력한 다음 확인을 클릭합니다.In the Boolean operator window, expand the Greater than or equal node, type 80 as the value, and then click OK.

  9. 실행 을 클릭하여 추적을 시작합니다.Click Run to start the trace.

  10. 도구 모음에서 선택한 추적 중지 또는 선택한 추적 일시 중지를 클릭합니다.On the toolbar, click Stop Selected Trace or Pause Selected Trace.

  11. 파일 메뉴에서 내보내기, 추적 정의 스크립트를 차례로 가리킨 다음 SQL 추적 컬렉션 집합을 클릭합니다.On the File menu, point to Export, point to Script Trace Definition, and then click For SQL Trace Collection Set.

  12. 다른 이름으로 저장 대화 상자의 파일 이름 상자에 추적 정의에 사용할 이름을 입력한 다음 원하는 위치에 파일을 저장합니다.In the Save As dialog box, type the name that you want to use for the trace definition in the File name box, and then save it in the location that you want. 이 예에서는 파일 이름이 추적 이름(SPgt80)과 동일합니다.For this example, the file name is the same as the trace name (SPgt80).

  13. 파일이 성공적으로 저장되었다는 메시지가 나타나면 확인 을 클릭한 다음 SQL Server 프로파일러SQL Server Profiler를 닫습니다.Click OK when you receive a message that the file was successfully saved, and then close SQL Server 프로파일러SQL Server Profiler.

SQL Server Profiler 추적에서 새 컬렉션 집합 스크립팅Script a new collection set from a SQL Server Profiler trace

  1. SQL Server Management StudioSQL Server Management Studio파일 메뉴에서 열기 를 가리킨 다음 파일을 클릭합니다.In SQL Server Management StudioSQL Server Management Studio, on the File menu, point to Open, and then click File.

  2. 파일 열기 대화 상자에서 이전 절차에서 만든 파일(SPgt80)을 찾아 엽니다.In the Open File dialog box, locate and then open the file that you created in the previous procedure (SPgt80).

    저장한 추적 정보는 쿼리 창에서 열리고 새 컬렉션 집합을 만들기 위해 실행할 수 있는 스크립트로 병합됩니다.The trace information that you saved is opened in a Query window and merged into a script that you can run to create the new collection set.

  3. 스크립트를 스크롤한 다음 스크립트 주석 텍스트에서 설명하는 다음과 같은 대체 작업을 수행합니다.Scroll through the script and make the following replacements, which are noted in the script comment text:

    • SQLTrace Collection Set Name Here 를 컬렉션 집합에 사용할 이름으로 바꿉니다.Replace SQLTrace Collection Set Name Here with the name that you want to use for the collection set. 이 예에서는 컬렉션 집합 이름을 SPROC_CollectionSet으로 정합니다.For this example, name the collection set SPROC_CollectionSet.

    • SQLTrace Collection Item Name Here 를 컬렉션 항목에 사용할 이름으로 바꿉니다.Replace SQLTrace Collection Item Name Here with the name that you want to use for the collection item. 이 예에서는 컬렉션 항목 이름을 SPROC_Collection_Item으로 정합니다.For this example, name the collection item SPROC_Collection_Item.

  4. 실행 을 클릭하여 쿼리를 실행하고 컬렉션 집합을 만듭니다.Click Execute to run the query and to create the collection set.

  5. 개체 탐색기에서 컬렉션 집합이 만들어졌는지 확인합니다.In Object Explorer, verify that the collection set was created. 이렇게 하려면 다음 단계를 수행합니다.To do this, follow these steps:

    1. 관리를 마우스 오른쪽 단추로 클릭한 다음 새로 고침을 클릭합니다.Right-click Management, and then click Refresh.

    2. 관리데이터 컬렉션을 차례로 확장합니다.Expand Management, and then expand Data Collection.

      시스템 데이터 컬렉션 집합 노드와 동일한 수준에서 SPROC_CollectionSet 컬렉션 집합이 표시됩니다.The SPROC_CollectionSet collection set appears at the same level as the System Data Collection Sets node. 이 컬렉션 집합은 기본적으로 해제되어 있습니다.By default, the collection set is disabled.

  6. 개체 탐색기를 사용하여 SPROC_CollectionSet에 대해 컬렉션 모드 및 업로드 일정 등의 속성을 편집합니다.Use Object Explorer to edit the properties of SPROC_CollectionSet, such as the collection mode and upload schedule. 데이터 수집기와 함께 제공되는 시스템 데이터 컬렉션 집합의 경우와 동일한 절차를 따릅니다.Follow the same procedures that you would for the System Data collection sets that are provided with the data collector.

예제Example

다음 코드 샘플은 이전 절차에서 설명한 단계의 결과로 생성되는 최종 스크립트입니다.The following code sample is the final script resulting from the steps documented in the preceding procedures.

/*************************************************************/  
-- 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