September 24, 2009 at 10:01 am
I had a scenario today that I haven't been able to determine if it is a bug or by design. I began running a trace to see if a certain table is being used in our environment. While the trace was running, the daily import process for our data warehouse began. It came to a crawl. Using a dynamic management view to look at what was running I noticed that it was 'stuck' on a function call. Turned off the trace and the load continued normally. Turned it back on, and once again it was on a functional call.
The 'E' in ETL worked fine with the trace on. It was the 'T' that was having the issue. Basically anywhere a function was used it took much, much longer than usual. The load portion of the ETL process worked fine with the trace on.
I only found a handful of articles about this. Here's one of them. http://sqlblog.com/blogs/linchi_shea/archive/2009/06/15/performance-impact-sql-trace-and-user-defined-scalar-functions.aspx
It should be noted that my trace was only capturing events where a certain table was being used. That being said, I still think that profiler needs to parse through everything in order to determine what to capture.
Is this behavior normal?
J.D. Gonzalez
September 25, 2009 at 8:06 am
Based on Linchi's post and your explanation of the process I'd say this is the expected behavior.
It should be noted that my trace was only capturing events where a certain table was being used. That being said, I still think that profiler needs to parse through everything in order to determine what to capture.
I believe you are correct here. The SQL Trace architecture is such that it collects all events specified and then applies any filters, check out this from BOL, http://msdn.microsoft.com/en-us/library/ms187933.aspx. Linchi has another post about Profiler\Trace performance where in the comments someone mentions that the # of filters applied can also affect server performance.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 25, 2009 at 8:27 am
Seems like a bug to me if it only affects UDF's. Is there another solution for determining if a given table is being used? I need to track this over several days.
September 25, 2009 at 8:46 am
Based on the information you have provided, I don't know of another way to do it because as long as you are tracing SP:Completed, etc... you will collect the information for that UDF before any filtering takes place.
I'd agree that this seems like a bug, but not being intimate with the internals of SQL Trace I can't say for sure. I suggest entering a Connect[/url] item about it. If you do this post the link here and I'll vote on it and get it out among my contacts. I can speak from personal experience that MS does look at and address Connect issues.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply