You are currently offline, waiting for your internet to reconnect

You may experience a decrease in query performance after you perform certain database maintenance operations or regular transaction operations in SQL Server 2005

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

Bug #: 329155 (SQLBUDT)
SYMPTOMS
In Microsoft SQL Server 2005, you may experience a decrease in query performance after you perform certain database maintenance operations or regular transaction operations. For example, you may notice a sudden decrease in query performance after you restore a database backup.

Starting with SQL Server 2005 Service Pack 2, you will notice messages such as the following in the SQL Server error log when this issue occurs:
2006-10-15 06:03:29.330 spid59 SQL Server has encountered 4 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2006-10-15 06:03:29.420 spid59 SQL Server has encountered 4 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2006-10-15 06:03:29.420 spid59 SQL Server has encountered 4 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
However, you can run the DBCC FREEPROCCACHE command or the DBCC FREESYSTEMCACHE command to flush the procedure cache. If the procedure cache is flushed by running one of these commands, you will notice messages that resemble the following in the SQL Server error log:
2006-12-14 11:37:03.57 spid53 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.
2006-12-14 11:37:03.57 spid53 SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.
Therefore, you can examine the SQL Server error log to determine whether the issue is caused by the problem that is described in this article.

Note This behavior does not occur in Microsoft SQL Server 2008.
CAUSE
This issue occurs because certain database maintenance operations or regular transaction operations clear the whole procedure cache.
STATUS
This behavior is by design.
MORE INFORMATION
The whole procedure cache is cleared when certain database level operations are performed in the following scenarios:
  • A database has the AUTO_CLOSE database option set to ON. When no user connection references or uses the database, the background task tries to close and shut down the database automatically.
  • You run several queries against a database that has default options. Then, the database is dropped.
  • A database snapshot for a source database is dropped.

    Note Database snapshots are only available in Microsoft SQL Server 2005 Enterprise Edition.
  • You change the database state to OFFLINE or ONLINE.
  • You successfully rebuild the transaction log for a database.
  • You restore a database backup.
  • You run the DBCC CHECKDB statement.

    Note This is true only in versions of SQL Server 2005 that are earlier than SQL Server 2005 SP2. After you install SQL Server 2005 SP2 or later versions, the whole procedure cache is not flushed when you run the DBCC CHECKDB statement.
  • You detach a database.
  • You specify one of the following optionswhen you run the ALTER DATABASE statement:
    • OFFLINE
    • ONLINE
    • MODIFY FILEGROUP DEFAULT
    • MODIFY_NAME
    • MODIFY FILEGROUP READ_WRITE
    • COLLATE
    • MODIFY FILEGROUP READ_ONLY
    • READ_ONLY
    • READ_WRITE
  • The whole procedure cache is cleared if one of the following server options is changed by the RECONFIGURE statement:
    • cross db ownership chaining
    • index create memory (KB)
    • remote query timeout (s)
    • user options
    • max text repl size (B)
    • cost threshold for parallelism
    • max degree of parallelism
    • min memory per query (KB)
    • query wait (s)
    • min server memory (MB)
    • max server memory (MB)
    • query governor cost limit
    Note Procedure cache will not be cleared if the actual value does not change or if the new value for the max server memory server option is set to 0.
If you experience this issue, you notice changes in the following values when you use Performance Monitor to collect data from SQL Server 2005 performance counters:
  • Performance object: Process
    Counter: %Processor Time
    Instance: sqlservr

    The value of this counter will increase because of increased CPU activity. Essentially, the whole procedure cache is cleared if this issue occurs. Therefore, subsequent requests must generate new plans to be cached. This behavior will slightly increase CPU activity.
  • Performance object: SQLServer:Plan Cache
    Counter: Cache Object Counts
    Instance: _Total

    Performance object: SQLServer:Plan Cache
    Counter: Cache Pages
    Instance: _Total

    The values of these counters will suddenly decrease.

    Note For a named instance of SQL Server 2005, the performance object is named MSSQL$InstanceName:Plan Cache.
  • Performance object: SQLServer:SQL Statistics
    Counter: SQLCompilations/sec

    The value of this counter will significantly increase after this incident.

    Note For a named instance of SQL Server 2005, the performance object is named MSSQL$InstanceName: SQL Statistics.
If you capture a SQL Profiler Trace by using the SP:CacheRemove event, you notice that this event is generated together with the following TextData column value when this issue occurs:
"Entire Procedure Cache Flushed"
Properties

Article ID: 917828 - Last Review: 02/17/2009 20:20:22 - Revision: 7.0

Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Workgroup Edition, Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2005 Express Edition with Advanced Services

  • kbsql2005engine kbexpertiseadvanced kbtshoot kbprb KB917828
Feedback