Cost in generating new execution plan

NEERAJ PRASAD SHARMA 2015-03-17 07:25:38

Hey guys,

We heard lots of time that plan generation is a very expensive process so we avoid recompilations and all, so can anybody can tell is there any DMV to get to know how much A particular plan cost US.

SQLkiwi 2015-03-18 06:56:53
This is a good question, without a simple answer.

Query compilation can be expensive, but it depends on how much effort the optimizer decides to put in, how many alternatives it can find, and a number of other factors.

Reusing a cached plan is 'obviously' cheaper than constructing a new plan each time, but only if the cached plan is the same plan (or close enough) as a fresh compilation would produce. If a fresh compilation results in a more optimal plan, and that plan saves more time and resources than we spent on the compilation process, the equation is reversed.

One way to determine how much effort a particular query plan required to compile is to look at the root node properties: you will see Compile CPU, Compile Memory, and Compile Time. These will typically be small numbers, unless the query is very complex.

NEERAJ PRASAD SHARMA 2015-03-19 11:41:39
Hey Paul,

Thanx for the answer