I know Friday afternoon isn't the best time for blogging, as everyone is going home now, and by Monday morning, this post will be old news. But I'm not shutting down just yet, and a something came up this week that I just realized not everybody knew about, so I decided to blog it.
Many (or most?) of you are aware that you can clear all cached plans using DBCC FREEPROCCACHE. In addition, there are certain configuration options, for which changing their values will cause all plans in cache to be removed.
I blogged twice about how to clear a single plan from cache… here and here.
This post is about something in between. You can clear all the plans for one particular database from cache, either explicitly, or as a side effect. To explicitly clear plans from a single db, you can use the command:
The other method is analogous to changing a configuration option, but for a single database. Namely, ALTERing a database, to change a database option value, can sometimes clear the plans for that database. Not all database option changes will clear plans from cache, and I haven't found a definitive list anywhere. In fact, I've found barely any 'official' mention of clearing plans for a single database. So this blog might be as 'official' as it gets.
I haven't tested all database options, but the ones that I know will clear plans for one database are the options that change how queries are compiled, optimized or executed. These include PARAMETERIZATION (set to SIMPLE or FORCED), the ANSI options such as ANSI_NULLS (set to ON or OFF), and the auto statistics options such as AUTO_UPDATE_STATISTICS.
If anyone comes up with a complete list, feel free to blog it, or if you don't have a blog, send it to me and I'll post it here.