re: 10.0 Plan Cache Flush

shus — Tue, 30 Jan 2007 04:27:28 GMT

Plan Cache Flushing - We recommend doing the following instead of attaching/detaching the DBs of those partners:

1) Redirect or bring down the front-end server (web server/services or application server) for those partners

2) Putting the DB in RESTRICTED_USER mode

3) If you must bring the DB on/off, you should put the databases that will likely to be brought on/off line in a separate DB instance from the DB instances of those that will be always online.

SQL 2000 allows you to detach a DB without flushing the whole proc cache, but it does it incorrectly under certain situations. SQL 2005 fixed this problem by flushing the whole plan cache. Currently, due to resource constraints, we do not have plans to do DB specific plan cache flushing. We certainly will keep your feedback in mind going forward.

For the parameter sniffing problem – If you use one of the suggestions above, the parameter sniffing problem will be reduced. The other things you can do are:

1) Use plan guide for those queries

2) Use “OPTIMIZE FOR” query hint

3) Use “with recompile” (limited for stored procs or use plan guide)

I would love to hear more detailed cases about the parameter sniffing problems.

-Shus