THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: Clearing Plans for a Single Database

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.

Have fun!


Published Friday, May 21, 2010 6:46 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Ranga Narasimhan said:

May 25, 2010 10:40 AM

Kalen Delaney said:

Thanks Ranga... this is a start, but it obviously is not complete since it doesn't include the PARAMETERIZATION or ANSI options that were the ones I tested.


May 25, 2010 6:58 PM

GeBagong said:

hi, kalen.i wanna ask you a question.can you tell me how to use fn_dblog?There's no documentation of the format of a log record in anywhere and I haven't been able to locate it anywhere else.thanks

May 26, 2010 7:35 AM

king said:



January 16, 2018 11:34 PM

aaaa said:

mt0417 mt0417

April 16, 2018 8:45 PM

chenyingying said:


May 6, 2018 10:47 PM

linying123 said:


May 10, 2018 8:08 PM

shenyuhang said:


June 1, 2018 7:04 PM

dongdong8 said:



June 29, 2018 2:48 AM

linying123 said:



July 16, 2018 8:34 PM

dongdong8 said:



July 23, 2018 11:31 PM

shenyuhang said:


July 23, 2018 11:39 PM

obat diabetes said:

August 1, 2018 11:22 PM

obat bronkitis anak secara alami said:

August 7, 2018 7:52 PM

obat sinusitis said:

August 10, 2018 7:25 PM

yaoxuemei said:


August 15, 2018 2:08 AM

qqq said:

August 16, 2018 12:19 AM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement