Changing DB_CHAIN Can Clear the Plan Cache

Home / SQL Server 2008 / Changing DB_CHAIN Can Clear the Plan Cache

If you make changes to the settings of a database, it can cause the procedure cache to be cleared. Microsoft has documented changes that cause this for all procs within a database (scroll down to just above the examples). But guess what, if you change the DB_CHAINING option, it clears the cache too. Here’s a sample script to show it in action.

The script is almost self-explanatory. I want to point out that I put in one cross-database query to imply the possibility of cross-database ownership or access, but also to show that regardless of what’s referenced, all queries from this database are flushed from cache.

The first of the simple DMO queries returns two rows, the second returns no rows because everything is out of the cache because of the change to the database. It’s a little thing, but since it wasn’t explicitly stated in the Microsoft documentation, I thought I’d toss this out there.

OK, fine, but what do you think?