asked
SQL Profiler having massive performance impact
I seem to have hit a problem using SQL profiler to diagnose some performance issues in an
ASP.net page. The page calls a stored procedure which when called from management studio runs in about 2 seconds. When you run a profiler trace and run the SP from management studio, the run time increases to over 30 seconds and seems to include a huge number of calls to a UDF used within the procedure. It is almost as if profiler is affecting the way the SP runs. Can anyone shed any light or offer any pointers? I could do with being able to use profiler while we run the page at the front end to see why that is performing so badly but can't do so while profiler is itself causing performance problems I'm using SQL 2008
Are you sure when you ran it no "extra" activity was occurring in the instance or the specific database?
I'm dropping in a day late but, if you are seeing lots of UDF statements (unexpected?) within the procedure then you are likely catching the SP:StmtStarting and/or SP:StmtCompleting events within your trace (same issues with SQL:StmtStarting and SQL:StmtCompleting). If you have used a UDF that is called for each row in the result set - say something like SELECT udf(column1, column1) FROM table... - then you will see as UDF call in profiler for every row in the result set. You will incur much more profiler overhead to track these executions that if you just track the procedure level rather than the statement level. If you want to reduce the monitoring overhead significantly, don't monitor the StmtStarting or StmtCompleting, just watch RPC:Completed to catch the overall execution statistics for the entire procedure. If you need to monitor at the StmtCompleting level, you will have to plan for the performance impact.
Thanks Ken, that does sort it - although using extended events as Grant suggests is probably better
It doesn't say what version of SQL Server you're dealing with, but you could use Extended Events instead of a server-side trace. They're much more lightweight and less likely to cause issues. Any chance you're running the Profiler GUI against the server directly? That can certainly cause issues. Other than the general load of running trace, I haven't run into the particular problem you're hitting here.
It's SQL 2008 - sorry, rather vital detail I'm forgetting! I've edited the question now to clarify If it's any help, I'm running profiler on a desktop that connects to the server
It could just be Profiler itself. If you use a server-side trace (a script generated from Profiler) that outputs to a file, do you see the same issues? Also, what events are you capturing with Profiler? Regardless, I'd just stop using Profiler and Trace to focus on Extended Events. They really are better in every possible way, except a good GUI for consuming the data.
How much free memory on your desktop? You may be getting delays due to the added network overhead of data and paging of RAM on your system. Grant is right, go with extended events, but try a server-side trace instead of a client-side trace and see if the issue persists.
You are probably having differentiering ansi settings between the
asp.net and management studio. Try to change exact_abort setting in management studio. Maybe I missunderstood the question, but if you run a query
from.net and the same query in management studio and get different execution times, it's probably because different ANSI settings.
The issue is more the effect profiler is having. If I run the SP in Management Studio it executes fine, but if I run a profiler trace and run the SP in Management Studio it doesn't. Essentially, the problem is that profiler is having such a serious performance impact that I can't use it to diagnose the performance issue in the
ASP.net page
@tomgough79 Ok... once again, I realize that I'm responding to an older thread... Folks have solved the performance issue associated with the use of SQL Profiler but what have you done to resolve the performance issue with the code? The problem that you're having there seems to be the UDF you've created and, from the sounds of things, it's either a scalar UDF (sF) or a Multi-Statement Table Valued Function (mTVF), both of which make some serious sucking sounds when it comes to performance. I recommend that you post the query that you're having problems with AND post the UDF so we can try to help you convert it to a nice, high performance iTVF (Inline Table Valued Function).
19 People are following this question.
