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

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.
• A database has the AUTO_CLOSE database option set to ON. Maintenance operations are performed, such as the DBCC CHECKDB operation or a backup operation. When the operations finish, 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.

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: