Search
InstantDoc ID
GO

Browse By: Author | Issue

July 20, 2004 03:20 PM

9 Steps to an Automated Trace

Get the performance information you need with minimum hassle
Rating: (0)
Steven Berringer
SQL Server Magazine
InstantDoc ID #43014
Profiler, SQL Server's built-in tracing feature, is an invaluable tool for performance tuning and debugging SQL Server applications. With it, you can trace every request a database application makes to SQL Server and use that data to improve performance. For example, if you run a Profiler trace during a long-running process, you might find that SQL Server is using a table scan, encountering table locks, or running into other performance-robbing events. In most cases, I run Profiler at the reques...

...This article is for paid Professional Members only.

Already a Professional Member? Please log in now:

NOT A PROFESSIONAL MEMBER? YOU CHOOSE:

Professional Membership

Monthly

Annual

VIP Membership

Monthly

Annual

Compare Member Benefits

Add a Comment

Great work Steven Berringer.
Excellent.
Thanks.
Naras

NARASIMHAN8/17/2006 10:48:30 AM

Very good article... I plan to adapt this to write to database another server using linked server functionality.

David

dgoodale@casestack.com10/28/2005 11:22:44 AM

The sp referred to in the article - sp_create_trace is actually called sp_trace_create.

DEL7/5/2005 1:19:47 PM

Excellent article and code. What a great ideal to build the trace as you want in Profiler then save to a SQL script and transform into a stored proc for batch Profiler data collection. Well done Steven

flexdba 5/10/2005 3:43:48 PM

good article - a few code errors but nothing that can't be easily fixed. I used it as good starting point for customizing for a multi-server environment for auditing after our Sarbanes-Oxley audit - yuk!


PAIGE10/27/2004 6:26:56 AM

Very useful indeed.

Peter10/13/2004 7:51:57 AM

Very Good article and was very helpful

Venu

avula_venu 8/31/2004 8:07:48 AM

I only found the 1 bug. I had to modify the code for Step2 of the StopTrace job from:

SELECT @trace_ID = traceid FROM ::fn_trace_getinfo(0)
WHERE property = 2
AND value = @path_file

to

SELECT @trace_ID = traceid FROM ::fn_trace_getinfo(0)
WHERE property = 2
AND convert(varchar(104),value) = @path_file

otherwise the match on @path_file always returned 0 rows.

Otherwise, very useful article which taight me some nice tricks with handling Profiler traces that I didn't know about.

pyale8/2/2004 7:39:09 AM

Good article but the code is full of bugs

bstevenson 7/29/2004 12:03:05 PM

Good article. however, in most of the cases, tracing is done against the production server (it is part of making the DB perfect!), but the uploading (into a table) and anlyzing is done on another server (may be DBA's personal server or dev server). This article didnt mention how the automation is designed in a multi server environment

preethi7/26/2004 2:25:50 AM

You must log on before posting a comment.

Are you a new visitor? Register Here
GOOGLE LINKS
SPONSORED LINKS
FEATURED LINKS

© 2010 Penton Media, Inc.

Windows is a trademark of the Microsoft group of companies. Windows IT Pro is used by Penton Media Inc. under license from owner.