Trace using Tuning template turn server unavailable

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » Trace using Tuning template turn server...

Trace using Tuning template turn server unavailable

Rate Topic

Display Mode

Topic Options

Author

Message

lluaces

Posted Friday, October 22, 2010 1:46 PM

Forum 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

Adiga

Posted Saturday, October 23, 2010 2:17 AM

SSCommitted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:50 AM
Points: 1,618, Visits: 20,898

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

Grant Fritchey

Posted Saturday, October 23, 2010 5:22 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 10:57 AM
Points: 13,371, Visits: 25,149

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1009593

lluaces

Posted Saturday, October 23, 2010 9:46 AM

Forum 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 »

Permissions