October 19, 2010 at 4:33 pm
Hi,
Please help me out in understanding,how does running the sql profiler trace on production database can cause performance overheads..
Thanks,
Deepak
October 19, 2010 at 11:47 pm
Running profiler means you are capturing the information related to specific database / activity. For that SERVER has to collect the details and any activity on the server affect CPU/Memory/IO etc.
That is the reason if you need accurate data of load of your database/server you need to run the profiler from the other machine however that will increase the network traffic.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
October 19, 2010 at 11:57 pm
You don't ever want to use Profiler gui against a production server. Use a server-side trace and trace to a fast local drive instead.
http://scarydba.wordpress.com/2008/12/18/profiler-research/
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
October 20, 2010 at 9:16 am
This is the key page for understanding how SQL Trace works and feeds into the I/O providers (rowset/SMO/Profiler is one of them).
The rowset provider, on the other hand, is not designed to make any data loss guarantees. If data is not being consumed quickly enough and its internal buffers fill, it waits up to 20 seconds before it begins jettisoning events in order to free buffers to get things moving. The SQL Server Profiler client tool will send a special error message if events are getting dropped, but you can also find out if you’re headed in that direction by monitoring SQL Server’s TRACEWRITE wait type, which is incremented as threads are waiting for buffers to free up.
Profiler impacts performance when implementing larges traces due to the number of events being fired. This would impede CPU/Memory[/IO] if there large number of events occuring.
Once an event fires, its data is routed into a global event sink, which queues the event data for distribution to each trace that is actively listening. The trace controller routes the data to each listening trace based on its internal list of traces and watched events
Hope this helps.
October 20, 2010 at 9:20 am
GilaMonster (10/19/2010)
You don't ever want to use Profiler gui against a production server. Use a server-side trace and trace to a fast local drive instead.
FWIW I've found lightweight traces are fine on high volume database environments. I believe its a combination of myth and/or poorly configured traces that have spread this across a multitude of articles.
October 20, 2010 at 11:02 am
MysteryJimbo (10/20/2010)
I believe its a combination of myth and/or poorly configured traces that have spread this across a multitude of articles.
I have personally brought down a busy production server with a lightweight trace via the profiler GUI. It's no myth.
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
October 20, 2010 at 5:26 pm
Thanks to every one for your input's.
Thanks,
Deepak
October 21, 2010 at 2:13 am
GilaMonster (10/20/2010)
MysteryJimbo (10/20/2010)
I believe its a combination of myth and/or poorly configured traces that have spread this across a multitude of articles.I have personally brought down a busy production server with a lightweight trace via the profiler GUI. It's no myth.
I'm not denying it isnt possible to bring down a server. Just its not impossible to use profiler if you are careful. We have two profiler traces and a server side trace running on a busy website server with between 5000 and 20000 concurrent and running connections. The database itself performs at least 40 million transactions/queries per day. So it is possible to successfullly use profiler on a prod db server so long as you are selective.
October 28, 2010 at 2:06 pm
MysteryJimbo (10/21/2010)
GilaMonster (10/20/2010)
MysteryJimbo (10/20/2010)
I believe its a combination of myth and/or poorly configured traces that have spread this across a multitude of articles.I have personally brought down a busy production server with a lightweight trace via the profiler GUI. It's no myth.
I'm not denying it isnt possible to bring down a server. Just its not impossible to use profiler if you are careful. We have two profiler traces and a server side trace running on a busy website server with between 5000 and 20000 concurrent and running connections. The database itself performs at least 40 million transactions/queries per day. So it is possible to successfullly use profiler on a prod db server so long as you are selective.
I've had similar positive experiences on very busy servers by implementing traces prudently. I have never locked up a server by using Profiler and executing traces if the server wasn't already overloaded.
LC
October 28, 2010 at 3:57 pm
use data collection under management
easy and clear output with reports.
profiler is always a headache for me from my exp.
http://www.simple-talk.com/sql/learn-sql-server/sql-server-2008-performance-data-collector/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply