CPU usage increases when the database is detached

When migrating a database to a multi-database server, in order to save time, we may directly detach a database and then directly use the MDF file to attach the database to another server. In this case, all cache plans will be cleared out of the buffer zone. As a result, the CPU usage will remain high for a period of time.

Apart from separation, the cache will be cleared. Other operations include: Setting the database online or offline, renaming the database, and attempting to modify the database sorting rules (whether successful or not) when you directly delete a database. Other tests are not specific, and these operations may cause this problem.

To observe the test results, you can run the following statement (sqlserver2005sp2 test environment) on adventureworks on your machine ):

Code
Use adventureworks;
Go
Declare @ dbid int
Select @ dbid = db_id ()
DBCC flushprocindb (@ dbid)
Go
Select * from sales. Customer where customerid = 1
Go
Select ECP. objtype, usecounts, P. Text
From SYS. dm_exec_cached_plans as ECP
Cross apply
(Select * From SYS. dm_exec_ SQL _text (ECP. plan_handle) P
Where p. Text like '% sales. Customer %'
And P. Text not like '% SYS. dm_exec_cached_plans %'

Then, you can perform any of the preceding operations on any other database, such as northwind. Then, check SYS. dm_exec_cached_plans and find that the cache plan is no longer available. I have not figured out why sqlserver chose to do so.

However, we should avoid performing the above operations on the database during peak hours through the above results.