Alternative Tool for SQL Profiler Trace?

Question

Alternative Tool for SQL Profiler Trace?

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
April 15, 2011 at 10:53 am

#148812

I was wondering if there is some tool which could provide me same data as sql profiler does. The goal is to not touch production system for analysis instead copy the database and run the tool to capture the sql which happened since each and every action is recorded in the log file? Is that possible?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

You have three options, all of them require you to run something on the production server.

You can set up a server side trace, which works just fine and as long as you output to a file and capture a reasonable set of events & columns doesn't cause any issues. As a matter of fact, this is my first choice in most situations.

You can set up to capture extended events. It's a bit of work to set up, but you get more control and flexibility than you do with a trace, but it still has to run against the server.

You can use dynamic management views to capture what's in cache on an ongoing basis. This can be problematic because it won't capture everything, but it can capture most things.

Those are basically your choices.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

harvey007 Valued Member Points: 52 More actions · Answer 2

https://github.com/harvey007y/IdealSqlTracer is a free open source alternative to SQL Profiler. IdealSqlTracer uses sp_trace_create, sp_trace_filter, sp_trace_setstatus to create custom traces that can eliminate a lot of noise. The SQL that is generated behind each action is beautifully formatted and placed into notepad so it can be easily copied to SSMS.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

harvey007 - Sunday, July 9, 2017 1:15 PM
https://github.com/harvey007y/IdealSqlTracer is a free open source alternative to SQL Profiler. IdealSqlTracer uses sp_trace_create, sp_trace_filter, sp_trace_setstatus to create custom traces that can eliminate a lot of noise. The SQL that is generated behind each action is beautifully formatted and placed into notepad so it can be easily copied to SSMS.

Please note: 6 year old thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 4

GilaMonster - Sunday, July 9, 2017 1:50 PM
harvey007 - Sunday, July 9, 2017 1:15 PM
https://github.com/harvey007y/IdealSqlTracer is a free open source alternative to SQL Profiler. IdealSqlTracer uses sp_trace_create, sp_trace_filter, sp_trace_setstatus to create custom traces that can eliminate a lot of noise. The SQL that is generated behind each action is beautifully formatted and placed into notepad so it can be easily copied to SSMS.
Please note: 6 year old thread.

Not sure the age of a thread matters here. Even though the OP may be long gone and no longer "listening", others that find the thread can benefit from even ages old threads.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)