SQL Server Profiler

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Performance Tuning » SQL Server Profiler

SQL Server Profiler

Rate Topic

Display Mode

Topic Options

Author

Message

Preet_S

Posted Tuesday, June 24, 2008 7:17 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 5:15 AM
Points: 236, Visits: 235

Hi All

Does anybody know if and how I can get SQL Profiler to start automatically on a test server but to log the events of a production server ?
From the test server, I created & scripted the trace, connecting to the production server. Then placed the scripted trace into a stored proc on the test server and created a SQL job to run the stored proc. The only problem is that it logs the activity of the test server.

Thanks in advance.

Preet

Post #522523

GilaMonster

Posted Tuesday, June 24, 2008 7:22 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687, Visits: 29,946

Put the trace into a job on the prod server. It's not a heavy load providing you're writing to a drive that's separate from the data or log files.

The server-side trace has to be run on the server that it's tracing.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #522531

Preet_S

Posted Tuesday, June 24, 2008 10:05 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 5:15 AM
Points: 236, Visits: 235

Thanks for the reply.
I would run it on the live server but we cant risk any performance problems and we dont have a spare drive in any case. There are many warnings against doing this such as in BOL (Auditing SQL Server Activity). i.e. where the recommendation is to use a non-production server.
I dont necessarily want to run a server side trace, rather to automate SQL Profiler running on a test server but logging activity on a production server. Of course it seems that it can only be automated using a server side trace.
Also there is a suggestion that client sided traces utilise considerable b/width ; http://sqlblog.com/blogs/linchi_shea/archive/2007/08/01/trace-profiler-test.aspx
So it seems that there will be a significant performance hit in our case with either method.

Post #522680

GilaMonster

Posted Tuesday, June 24, 2008 11:48 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687, Visits: 29,946

There are many warnings about running profiler on a production server, but those warnings are for the profiler GUI. The recommendation for tracing a production system is to use the server-side trace scripts (like you're doing) and to write the trace to a file on a fast drive on the production system. They have to run on the server that is been traced.

I've often done these kind of traces. The impact is minimal, much less than tracing using the profiler front end.

What drives do you have in your server?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #522781

Preet_S

Posted Wednesday, June 25, 2008 10:04 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 5:15 AM
Points: 236, Visits: 235

We have ;

c: Op Sys
d: Prog Files & SQL log files
e: SQL Data Files

Post #523517

kranthikumar Gadde

Posted Wednesday, August 08, 2012 5:49 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 3:06 AM
Points: 233, Visits: 26

Extended Events are better than SQL Profiler in terms of performance,scalability. Xevents are introduced in 2008. In SQL Server 2012 Extended events more features are available.

Using SQL Profiler to trace is not supported on production systems.

Post #1341807

GilaMonster

Posted Friday, August 10, 2012 5:20 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687, Visits: 29,946

kranthi1.aprdc (8/8/2012)

Using SQL Profiler to trace is not supported on production systems.

It's completely supported. It's not recommended on busier servers, but it's fully supported.

p.s. 4 year old thread about SQL 2000 (which does not have extended events).

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1343299

« Prev Topic | Next Topic »

Permissions