March 31, 2003 at 5:09 am
Anyone know why you can't see the execution plan used for the statements inside a function? Or number of page reads using SET STATISTICS IO?
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
April 3, 2003 at 8:00 am
This was removed by the editor as SPAM
April 3, 2003 at 8:48 am
Chris
I raised virtually the same issue on these forums a few months ago, and got the same response as you... Nothing.
The execution plan just shows a table scan for the function, and Statistics IO show nothing.
To get some idea of the query plan for a function, I have resorted to pasting the function code into QA. Not very satisfactory!!
The answer as to the lack of a query plan and STATISTICS IO may lie in the way some functions get executed.
A while back I ran a full Profiler trace while running a single SQL statement that used a function. For the single SQL statement I saw a huge number of SP:Starting and SP:Completed entries for the function - presumably one for each time it was invoking the function.
As each execution of a SP normally generates it's own set of statistics (and has it's own query plan), there would have been a huge amount of statistics and query plans for the execution of the function.
Maybe that's why Microsoft don't show the stats - they would somehow have to sum the stats for each invocation of the function and somehow show a consolidated plan.
April 3, 2003 at 2:52 pm
quote:
As each execution of a SP normally generates it's own set of statistics (and has it's own query plan), there would have been a huge amount of statistics and query plans for the execution of the function.Maybe that's why Microsoft don't show the stats - they would somehow have to sum the stats for each invocation of the function and somehow show a consolidated plan.
Ian, sounds like some good theories there. However, even if there are several plans and stats created I would have liked to see them anyway, one by one.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
April 3, 2003 at 6:33 pm
They are more than just theories. They are correct. The function fires for each and every row affected, and this would make the execution plan displayed practically useless. I know a couple microsoft guys personally, and we have had this discussion before.
Oh, and they actually cannot sum the statistics as they happen at a diferent level from the actual execution plan, and are excluded from the level of plan you see. For this reason, I try to stay away from functions any time possible, and remove function calls at every opportunity. Talk about a performance hit....
kudos...ianscarlett
Edited by - scorpion_66 on 04/03/2003 6:33:40 PM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply