SQL Server 2008 Tracing For SQL Statements

SQL Server 2008 Tracing For SQL Statements

book

Article ID: KB0078599

calendar_today

Updated On:

Products Versions
TIBCO Scribe Insight 7.9.1

Description

SQL Server Profiler offers predefined trace templates that allow you to easily configure the event classes that you will most likely need for specific traces. The Tuning Template captures information about Stored Procedures and Transact-SQL batch execution. Use to produce trace output that Database Engine Tuning Advisor can use as a workload to tune databases.

This example is designed for an interactive tracing session and requires the following:
  • SQL Server Profiler
  • SQL Server Database Engine Tuning Advisor


Edit The Tuning Template
 

In this example we will edit the existing Tuning Template and save it under another Template name to preserve the original settings.
  1. Open SQL Server Profiler and select Edit > Templates > Edit Template.
 
kA2320000004GlQCAU_en_US_1_0
 
 
kA2320000004GlQCAU_en_US_1_1
 
  1. Click the Events Selection tab.
 
kA2320000004GlQCAU_en_US_1_2
 
  1. Check the Show All Events checkbox.
  2. Under TSQL add the SQL StmtCompleted option. Check all associated columns.
  3. Uncheck the Show All Events checkbox.
 
kA2320000004GlQCAU_en_US_1_3
 
  1. Under Errors and Warnings select the Exception and User Error Message events
  2. Check the Show All Columns checkbox and select the columns shown in the graphic.
 
kA2320000004GlQCAU_en_US_1_4
 
  1. Uncheck the Show All Columns checkbox.
  2. Click Save As.
 
kA2320000004GlQCAU_en_US_1_5
 

Filtering Tracing Messages
 

Tracing can produce a large number of messages. Apply filters whenever possible to reduce the number of messages, such as by Database Name. If the Database Name is not available, filter by Database ID. Use the query below to list Database ID and Name.
 
SELECT [name],[dbid] FROM [master].[dbo].[sysdatabases] ORDER BY [name]
 
kA2320000004GlQCAU_en_US_1_6
 

Configure And Run The Trace
 

  1. In SQL Server Profiler select Edit > New Trace.
  2. Select the correct Template from the drop-down in the Use the template field.
  3. Click the Run button.
 
kA2320000004GlQCAU_en_US_1_7
 
  1. After a period of time or when an event has occurred, stop the trace and review the results.
 
kA2320000004GlQCAU_en_US_1_8
 

Example - Capturing A GP eConnect Stored Procedure

NOTE: The SQL can be copied into SQL Server for testing purposes.

 
kA2320000004GlQCAU_en_US_1_9


You may want to add an additional filter in this case specifying the eConnect Stored Procedure name. The eConnect Stored Procedure name can be found in the Scribe Trace file by searching for stored a procedure.

 
kA2320000004GlQCAU_en_US_1_10

Issue/Introduction

Use trace templates in SQL Server Profiler to easily configure the event classes that you need for specific traces.
Powered by Wolken Software