April 17, 2016 at 9:14 am
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
April 17, 2016 at 9:55 am
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
April 18, 2016 at 2:49 am
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
April 18, 2016 at 5:21 am
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 18, 2016 at 6:46 am
thanks all
April 18, 2016 at 7:48 am
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.
April 18, 2016 at 7:57 am
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply