Post reply

Cache a query plan for a function

  • July 2, 2012 at 2:35 am

    #271928

    Hi

    Does the query plan for a function that returns a table be cached like that of what a view would?

    Thanks in advance

  • July 2, 2012 at 4:43 am

    #1507673

    SQL server stores the query plan for all the select,delete,insert and update statements unless recompile options were used.Sometime you might not see the plan in query if the statement was executed long time back as the plan was flushed to make room for plan for other statements.

    Thus if your function is using some kind of select statement. You should be able to get the plan for that sql.

    --Try something like this

    select * from FROM sys.dm_exec_cached_plans cp

    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) qt

    where qt.text like '%<part of your sql statement from function which unqieuly identify the sql>%'

    GulliMeel

    Finding top n Worst Performing queries[/url]
    Improve the performance of Merge Join(special case)
    How to Post Performance Problem -Gail Shaw[/url]

  • July 2, 2012 at 4:47 am

    #1507674

    Thanks a lot

    great answer

  • July 2, 2012 at 5:04 am

    #1507679

    You can test this using the attached simple script and compare the generated plans and consumptions :w00t:

    The last result set will contain the execution plans ... if they reside in cache :unsure:

    edited : script modified because of error 🙁

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • July 2, 2012 at 5:29 am

    #1507686

    A view's execution plan is not cached. Views don't run independently (like procedures do), they're part of other queries. Those queries get their plans cached, the views don't.

    Inline table-valued functions are like views, they're inlined into queries when the queries run and hence they don't get independent execution plans. Multi-statement table valued functions are more like procedures, they're not inlined and they do have their own cached execution plans.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

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