Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trace using Tuning template turn server unavailable Expand / Collapse
Author
Message
Posted Friday, October 22, 2010 1:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 25, 2010 3:33 PM
Points: 2, Visits: 12
I was trying to gather traces to do a database tuning.

Using "Tuning" template in SQL Profiler I started a trace to gather events for 1 hour. Data gathered was stored in a table of a Debug database.

However, after several minutes the server went to it's knees and users started complaining.

I stopped the trace and the server went back to normal.

I understand that running Tuning Advisor is resource-demanding and should only be run off peak hours, but should traces also be avoided?

Please help!
Post #1009475
Posted Saturday, October 23, 2010 2:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
It is not recommended to run Profiler trace on a Production instance. Instead run a server side trace using as appropriate. An example here

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Post #1009573
Posted Saturday, October 23, 2010 5:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, October 23, 2014 6:53 AM
Points: 13,890, Visits: 28,285
You should absolutely not run profiler on a production system as was mentioned. In addition, you should output your trace to file, not a database and especially not a database on the same server. You can use fn_trace_gettable to read the files and input them into a table.

Here is an excellent book on Profiler. Brad's the man.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1009593
Posted Saturday, October 23, 2010 9:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 25, 2010 3:33 PM
Points: 2, Visits: 12
Thank you Pradeep and Grant for the replies and tips.

I was not aware that Profiler stages the trace on C: drive. In addition, to ad injury to insult, I found that a daily C: drive defrag was scheduled for that same time on that server! I will create a server side trace instead. And of course, have the defrag rescheduled.

Thanks!
Post #1009619
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse