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
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.
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.
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.
@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).