Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Profiler


SQL Server Profiler

Author
Message
Preet_S
Preet_S
SSC Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 240
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55573 Visits: 44699
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, 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


Preet_S
Preet_S
SSC Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 240
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55573 Visits: 44699
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, 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


Preet_S
Preet_S
SSC Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 240
We have ;

c: Op Sys
d: Prog Files & SQL log files
e: SQL Data Files
kranthikumar Gadde
kranthikumar Gadde
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 28
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55573 Visits: 44699
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, 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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search