Post reply

Number of Reads in Profiler and Actual Execution Plan

  • June 28, 2009 at 7:39 pm

    #1016574

    mike_walsh (6/28/2009)

    Hey Grant - I understand what you are saying but I am not 100% sure if I follow with this situation. Follow along and smack me around and tell me where my brain went off the deep end here (because I am certain it has 🙂 )

    The execution plans here are fairly complex (another issue we are working out.. much more work than necessary.. so this is more of an academic question.. Once the developers and I have our chat and things get rewritten things should be less complex). I agree that when the plan is displayed there are extra reads to get the information needed as part of the plan and I know in SQL 2000 the calls to do this work were definitely part of the reads in profiler. I don't know if they "fixed" that in 2005 so the reads are only buffer cache reads (whether data was there or had to be fetched from disk before getting there).

    The strange thing here is that the huge increase in reads is directly attirbuted to the UDF calls. If I run a statement level profile and see the reads for each statement call the biggest increase in reads is in the code that is inside of the UDF. It goes from displaying 0 per call (without the plan included) up to 90 per call (with the plan included) and the UDF is (this is why you just don't use scalar UDFs like this) called numerous times.

    While I agree with your point what confuses me is that the UDF really isn't in the execution plan each time it is called. In fact the execution plan will not even really display the UDF calls as UDF calls (part of the reason people might look at an execution plan and IO statistics with a scalar UDF in the select list and reason: "Oh the plan looks fine to me"). It just shows the compute from it and "hides" the UDF. and it does it once for each place a function is referenced in the proc, not once for each call.

    That is where I get a bit confused here. Of course it doesn't really matter in this case. The problem is many fold... First they put a UDF in the code like this at all. Then they are calling it in an inner most select (with plenty of subselects) and doing it on all rows before any filtering/aggregation is occurring rather than doing it on the final 30 rows that are being returned, etc. etc.

    I think Gail is likely right (there's a waste of typing). It makes sense that if you're attempting to capture a plan, the UDF would also get captured, even though it's not displayed... sort of. Your number still seems awfully high for this, but, since it's only occuring when you capture the plans... this makes sense. I'll have to try to steal some time to do some tests tomorrow.

    ----------------------------------------------------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

  • March 4, 2011 at 9:31 am

    #1294753

    Explicit cursors can crush your actual execution plan (and thus actual IO in profiler) too.

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

Viewing 2 posts - 16 through 16 (of 16 total)

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