THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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:

DBCC FLUSHPROCINDB(<db_id>);

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!

~Kalen

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

Comments

 

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.

~Kalen

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

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement