dbcc FREEPROCCHACE

fredp1

I'm contemplating on clearing the Query Execution Plan cache.

Or db was now gone from a test to go live condition, and I want to be sure to be sure that the plans been used are up to date and reflect real life data.
I have some basic knowledge of the qep's that could be generated, and I am looking for reassurace that if I clear the cache that SQL will re-populate the cache with a more realistic plan that matches our current data.

Do you think this is okay to perform a dbcc freeproccache?

Can someone can explain when the proccache gets 'refreshed' with respect to Nav?
My limited understanding... is SQL will save the cache for that query/procedure forever. With our system, if we had test data with limited records, the qep that SQL would decide to use could/would be very different to a real data unless you run a 'dbcc freeproccache'.
So running a dbcc freeproccache is something that should be run to have efficient qep's... hopefully better performance too! Is this valid?

If anyone can provide some more infor on this topic it would be helpfull for a lot of other SQL users too.

Thanks
Fred

stryk

Hi!

Well, QEP are not cached forever - cache-space is limited, thus SQL Server has to release some memory sometimes, so it will "forget" - but it's not possible to predict what & when.
DBCC FREEPROCCACHE will indeed erase all cached QEP, this could be considered a full brain-wash
Hence, SQL Server is forced to compile (and cache) new QEP for each new query.
But this does not grant that any QEP will be perfect - there is still a chance that SQL starts scanning indexes, caching the "bad" QEP and repeatedly doing something "stupid" (Parameter Sniffing).

IMHO the DBCC could be used to once clear the cache (e.g. to prepare a test), but should not be used frequently - since SQL 2005 (which version are you running?) you could use RECOMPILE hints, e.g. by using "Plan Guides" (or from within NAV) - see "Books Online" about details.

Hope this helps a little.

Best regards,
Jörg

fredp1

Hi Stryk,

The freeproccache appeared to help our Regenertaive MRP run. It used to take 45-50 min now its about 30-35 minutes. (we run SQL2005)
I'm assuming that the cache is not database/company specific. Does a query/proc that is for the reservation entry table for Company A has a different QEP cached for the same table for Company B?
Logically I think it would. Yes?

I thought I read something in your book or somewhere about "parameter sniffing". Does this help or should I look closer at the ndoconfig table?
Sorry for all the questions, but how is it possible to view the QEP for a cached proc?

Thanks
Fred

stryk

Yeah, I can imagine that DBCC FREEPROCCACHE helped - at first: but it might happen that "bad" QEP are still generated after the DBCC. And I would rather use RECOMPILE Hints or Plan Guides than using the DBCC periodically (which was indeed necessary with SQL 2000!) ...

I am not sure at which extent the QEP are cached, but I don't think the QEP for a "Reservation Entry" query is the same for two different companies: for each company a separate "Reservation Entry" table is created, thus they are different SQL objects ("CompanyA$Reservation Entry" and "CompanyB$Reservation Entry").