very strange issue with profiler

Question

very strange issue with profiler

Mad-Dog

SSCertifiable

Points: 6108
More actions
April 17, 2016 at 9:14 am

#326564

Hi,
I've noticed strange issue in the profiler while it is running with sp:stmtcompleted some of my query become very very slow.
if i remove the sp:stmtcompleted those specific query finished after 3 sec, if i add the sp:stmtcompleted the query finished after 30 sec.
why is that?
it hurts query performance badly..
THX

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

I tell clients to only run statement completed profiler traces for a VERY short period when they want to see just how truly horrific Scalar and Multi-StatementTVF User Defined Functions are, or how bad cursors and ORMs can be. Tracking those onesy-calls can be, as you have seen, devastating for performance. This is ESPECIALLY true if you run profiler GUI and ESPECIALLY ESPECIALLY if you run said profiler GUI on the server itself.

So, when you tell a doctor "it hurts when I press here" the doctor will tell you "don't press there"!! Stop running statement level profiler traces! 😀

The very sad thing is that this is enabled on the default "Tuning" template installed with profiler! :crazy:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

Mad-Dog (4/17/2016)
why is that?
it hurts query performance badly..

Because the Profiler GUI uses latches to sync the display, meaning the server runs far slower, and sometimes even crashes.

Don't ever use the Profiler GUI against a production server. Server-side traces or, since you're on 2012, 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

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

Piling on, don't use the Profiler GUI against production systems. It hurts performance.

If you do go to use extended events (also my advice on a 2012 forum), be sure you output to a file, not to the default, which is the buffer. That can also hurt performance.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Mad-Dog SSCertifiable Points: 6108 More actions · Answer 4

Mad-Dog

SSCertifiable

Points: 6108

April 18, 2016 at 6:46 am

#1872727

thanks all

jeff.mason SSCrazy Eights Points: 9555 More actions · Answer 5

Grant Fritchey (4/18/2016)
Piling on, don't use the Profiler GUI against production systems. It hurts performance.
If you do go to use extended events (also my advice on a 2012 forum), be sure you output to a file, not to the default, which is the buffer. That can also hurt performance.

I still remember the SQL Saturday session you led where you showed us the difference between running Profiler through the GUI and using it to generate the template for a server side trace. That was probably the single best piece of help I got that day, at least in terms of me still remembering it and using it on a daily basis.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 6

jeff.mason (4/18/2016)
Grant Fritchey (4/18/2016)
Piling on, don't use the Profiler GUI against production systems. It hurts performance.
If you do go to use extended events (also my advice on a 2012 forum), be sure you output to a file, not to the default, which is the buffer. That can also hurt performance.
I still remember the SQL Saturday session you led where you showed us the difference between running Profiler through the GUI and using it to generate the template for a server side trace. That was probably the single best piece of help I got that day, at least in terms of me still remembering it and using it on a daily basis.

Wow! That was a little while ago. Glad it helped.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software