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:
- Create a table that holds your trace number.
- Create a table that will hold the trace results.
- Create a job that runs every X minutes (e.g., 10 minutes). The job will do the following:
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:
- In production -- Create a table that holds your trace number.
- In "Monitor" environment -- Create a table that will hold the trace results.
- Create a job that runs every X minutes (e.g., 10 minutes). The job will run a DTS/SSIS package that does the following:
Requires Free Membership to View
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
- Step-by-Step Guide: How to interpret performance metrics in SQL Server 2005
- Using SQL Server Profiler with Analysis Services
- FAQ: DTS packages in SQL Server
-
This was first published in March 2007
There are Comments. Add yours.