1

I'm studying about ad hoc statement performance, and it seems everything is moving around the type of statement "safe", respectively "unsafe" with a sable/unstable plan.

Simply said, how can I know the statement is deemed as "unsafe", but it has a "stable" plan. I think the answer is around dm_exec_query_plan and dm_exec_query_stats, but it is not very clear to me.

user3104183
  • 408
  • 1
  • 9
  • 26
  • Safe for what? Auto parameterization? [You can see if this was attempted and succeeded or not using the query here](http://stackoverflow.com/a/15304023/73226) – Martin Smith Jan 01 '14 at 15:28

1 Answers1

0

There is no ad hoc safe plan, there are application type of plans that are ad hoc however they are compiled in the code, explicitly or using some OR mapper.

For SQL all that is not pre-compiled (that's SPs, views, and other data extraction) is ad hoc, best to parameterise to have cache plan re-use. Most of your time is typically going to be in compiling queries.

ErikE
  • 48,881
  • 23
  • 151
  • 196
Walter Verhoeven
  • 213
  • 1
  • 6