I have a typical case where parameter sniffing causes a "bad" execution plan to land in the plan cache, causing subsequent executions of my stored procedure to be very slow. I can "solve" this problem with local variables, OPTIMIZE FOR ... UNKNOWN, and OPTION(RECOMPILE). However, I can also dive into the query and try to optimize it.
I'm trying to determine whether I should: given limited time to fix problems I would like to know the cost of not doing it. As I see it, if I just stick with OPTION(RECOMPILE), the net effect is that a query plan is recreated every time the query is run. So, I think I need to know:
How to find out what the cost of creating a query plan is?
To answer my own question, I've Googled (e.g. with this query), and I've gone through the documentation of columns for the dm_exec_query_stats DMV. I've also inspected the output window in SSMS for "Actual Query Plan" to find this info. Finally, I've searched DBA.SE. None of those led to an answer.
Can anyone tell me? Is it possible ot find or measure time needed for plan creation?
Aucun commentaire:
Enregistrer un commentaire