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

sql profiler trace Expand / Collapse
Author
Message
Posted Tuesday, October 19, 2010 4:33 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:47 AM
Points: 186, Visits: 127
Hi,

Please help me out in understanding,how does running the sql profiler trace on production database can cause performance overheads..

Thanks,
Deepak
Post #1007382
Posted Tuesday, October 19, 2010 11:47 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 4:45 AM
Points: 2,385, Visits: 1,843
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."
Post #1007469
Posted Tuesday, October 19, 2010 11:57 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
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 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 #1007472
Posted Wednesday, October 20, 2010 9:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 2:08 AM
Points: 1,350, Visits: 15,200
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).

SQL Trace


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.
Post #1007813
Posted Wednesday, October 20, 2010 9:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 2:08 AM
Points: 1,350, Visits: 15,200
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.
Post #1007822
Posted Wednesday, October 20, 2010 11:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
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 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 #1007892
Posted Wednesday, October 20, 2010 5:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:47 AM
Points: 186, Visits: 127
Thanks to every one for your input's.

Thanks,
Deepak
Post #1008105
Posted Thursday, October 21, 2010 2:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 2:08 AM
Points: 1,350, Visits: 15,200
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.
Post #1008227
Posted Thursday, October 28, 2010 2:06 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 12:36 PM
Points: 256, Visits: 1,063
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
Post #1012631
Posted Thursday, October 28, 2010 3:57 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, March 27, 2014 6:04 PM
Points: 450, Visits: 991
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/




--SQLFRNDZ
Post #1012716
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse