How to use SQL Trace definition

Use SQL Profiler in your production environment to target poorly performing T-SQL commands in SQL Server 2000 and SQL 2005. Microsoft database expert Michelle Gutzait guides you through SQL Trace, from how to use trace definition through incorporating SSIS/DTS packages into the process. Locate problematic T-SQL commands and send the reports to your SQL Server development team.

This Content Component encountered an error

How to use the trace definition:
Now that you have the trace definition on hand, you can run a job that creates a trace and saves the results to a table every X minutes.
Here are the steps to accomplish that:
 

  1. Create a table that holds your trace number.
  2. Create a table that will hold the trace results.
  3. Create a job that runs every X minutes (e.g., 10 minutes). The job will do the following:

  4. a. Check if there is a trace running (querying the table that holds the trace number)
    b. If a trace is running:
    → Stop the trace.
    → Insert the results from the file into the table holding the trace results.
    → Start the trace.

    Else (the trace is not running):
    → Create and start the trace.
    → Keep the trace number in the table.

You probably want to trace the production environment, but remember to keep the trace results in a separate environment (let's call it "Monitor" environment). This way production performance is not affected.
In this case, you would need to use a DTS/SSIS package that does the following:

  1. In production -- Create a table that holds your trace number.
  2. In "Monitor" environment -- Create a table that will hold the trace results.
  3. Create a job that runs every X minutes (e.g., 10 minutes). The job will run a DTS/SSIS package that does the following:

  4. c. In production --
    i. Checks if there is a trace running (querying the table that holds the trace number)
    ii. If a trace is running:
    → Stops the trace.
    → In "Monitor" environment -- inserts the results from the file into the table holding the trace results. Makes sure the directory where the trace file is located has a share name and can be accessed by the process that reads it in the "Monitor" environment.
    → Start the trace.

    Else (the trace is not running):
    → Create and start the trace.
    → Keep the trace number in the table.

Since we would like to have reports in the "Monitor" environment, we probably need the Database Name from production, along with the Database ID that we import in the trace results. This is why the DTS/SSIS should also import the list of databases and database IDs from production.
If we are constantly running reports in the "Monitor" environment, we would need to import the database list to a separate table and update the real database's table after the import.

Furthermore, we probably want to purge historical data so the "Monitor" database will not grow infinitely. It is recommended to add a step in the package to purge historical data from the "Monitor" database.

 


 


Targeting SQL Profiler to determine performance problems


 Home: Introduction
 Step 1: Tracing statements in SQL Server 2000
 Step 2: Tracing statements in SQL Server 2005
 Step 3: How to use SQL Trace definition
 Step 4: Script example for SQL Trace 
 Step 5: SQL Trace results in SQL Profiler

 

ABOUT THE AUTHOR:   
 
Michelle Gutzait works as a senior databases consultant for ITERGY International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the last 10 years, she has worked exclusively with SQL Server. Her skills include database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS packages, administrative and infrastructure tools development, reporting services, and more.
Copyright 2007 TechTarget
 

More on SearchSQLServer.com
 

This was first published in March 2007

Dig deeper on SQL-Transact SQL (T-SQL)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close