What could be a reason for not saving exec plan in proc cache ?

Question

Post reply

What could be a reason for not saving exec plan in proc cache ?

SQL Guy 1

SSCoach

Points: 15979
More actions
December 6, 2011 at 12:59 pm

#251608

We have a performance problem that we cannot solve.
When running very simple select * query, which should return no more than 1000 records, parse and compile time takes 3-4 sec, while execution time is below 20 ms.
When we query sys.dm_exec_cached_plans DMV, there are very few entries there (usually between 3 and 25). Even after execution of our query, a new exec plan is not entered in DMV. You may say that that's becuase the plan is trivial, but why it takes so long to parse?
By comparison, on another server, which is very similar to this one, there are 1,700+ entries in plan cache. We also compared sys.configurations between these servers, they are pretty much identical.
What impression I have is by some reason execution plan is not recorded in plan cache, or maybe it's pushed out from there shortly after execution by some processes.
Did somebody experience same problems? What should I check on the server ?
Thanks

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Trivial plans are cached.

Any databases set to autoclose?

Any regular restores?

Any cache flush messages in the error log?

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

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 2

We don't have any databases set to autoclose.

There are no restores running now.

Anfortunately, I don't have privileges to check error log (I don't have "sa" rights).

But I guess I will need to run profiler.

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 3

Is is possible you have enabled 'optimize for ad hoc workload' on the server? It will store plan stubs the first time a query is executed instead of storing the plan.

But the bigger concern is why it's taking 4 seconds to compile a plan. It sounds like something is up on that server. I would not suggest Profiler (in fact, don't run Profiler on production, ever, use a server-side trace or extended events). Instead, I think you need to look at wait states, memory and CPU consumption, etc. using Dynamic Management Objects and Performance Monitor.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Query direct on 1 table or 1 view?

If theview is complex enough, it might take 4 sec to really simplify it enough to run the query.

Can you post the actual execution plan?

Is it possible you are waiting on stats updates (wouldn't expect 3-4 secs, but it's in the not impossible bin)?

Megistal SSCrazy Eights Points: 8787 More actions · Answer 5

I believe it won't be related to your issue but who knows. It might be.

Applying the same configuration for a database, even tough nothing changed will result in plan cache flushed.

Ex: Setting the full recovery model even if it has not changed will flush the plan cache.