Does a Mirroring Failover Clear the Procedure Cache?

I saw a question on the MSDN forums asking what happens to the procedure cache (also known as the plan cache) on the principal and mirror when a failover occurs. My reply was starting to get long, so I decided to move it out to a blog post. The original question poses the theory that the procedure cache is flushed on both mirror and principal when a failover occurs.

How Mirroring Transfers Transactions

The first part I want to address is whether there would be any expectation of having cached procedures on the mirror server. This requires understanding how mirroring processes the transactions. I addressed some of this in a post called What Does Redone Mean? back in December. Crash recovery and database mirroring work very similar in this case. Mirroring doesn’t replay transactions. It replays log records.

For example, if I execute a stored procedure on the principal that updates 3 records, this yields a series of log records that detail every change that is made. On the mirror side, the individual log records are applied exactly as they were on the principal. The procedure does not get called on the mirror. Since it is not replaying transactions, there will be no procedures for that database in cache. Nothing will be added to the procedure cache for a database mirror. That does not mean that there can’t be anything in there.

The Experiment

I have a test database named TwoTBDatabase from an earlier demo that is mirrored between 2 instances on my laptop. You can use the code below if you want to set the same up on your system. The code will create a database with one table and one stored procedure in it.

-- Create new database
Create Database TwoTBDatabase;
Go

-- Make sure the recovery model is FULL
Alter Database TwoTBDatabase Set Recovery Full;
Go

-- Switch to database
Use TwoTBDatabase;
Go

-- Create a table and populate it
Select * INTO dbo.MasterFiles
From sys.master_files
Go

-- Create a procedure
Create Procedure dbo.TestProc
As

Select *
From sys.master_files
Except
Select *
From dbo.MasterFiles
Go

/***************************************
Mirror the database to a second instance
***************************************/

Now that the mirroring has been set up, we’ll execute the procedure and check the plan cache on both servers. The query simply checks the cache for query plans for objects in the mirrored database. If I run teh below query on both servers, it returns no rows for the mirror server and 1 row for the principal server. The query plan is cached on the principal but not on the mirror as expected.

Select *
From sys.dm_exec_cached_plans CP
CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP
Where QP.dbid = DB_ID('TwoTBDatabase')

Failover the database to the mirror instance and check the cache again. I run the same queries as before. No rows returned on the new principal (original mirror) as expected. The result on the new mirror (original principal) is not so expected. I get an error returned stating that the database is mirrored and cannot be opened. I assumed it was the reference to the database in the WHERE clause that caused the error, so I try again without the WHERE clause. I get a lot of results and then the error returns. It errors out when it hits a query plan in the mirror database.

Msg 954, Level 14, State 1, Line 1
The database “TwoTBDatabase” cannot be opened. It is acting as a mirror database.

This clearly indicated that when the database transitions from principal to mirror, it does not flush the plan cache. Does that mean that if we fail back, the plan will still be cached?

I fail back to the original principal and query the cache again. On the mirror, I get no rows returned, again as expected. On the principal, I get …….. wait for it …….. no rows returned. Clearly, the plan cache is flushed when the database is recovered. So even if there are lingering plans in cache from an earlier failover, they will be flushed when the mirror is brought online.

There was one more thing I wanted to test. I executed the procedure again and failed over again to duplicate the state where querying the plan cache on the mirror server fails. At this point, I dropped mirroring and manually recovered the mirror database using the following command:

Restore Database TwoTBDatabase With Recovery;

.

As expected, querying the plan cache for the recovered database resulted in no rows returned. I wanted to demonstrate that it is the recovery of the database that casued the plan cache to be flushed and not just mirroring itself.

Summary

Getting back to the question, a mirroring failover does flush the plan cache when the mirror database is recovered and made the principal. The plan cache does not get flushed when the principal transitions to mirror. No matter what, the mirrored database will have a cold plan cache when it is recovered and brought online whether that is due to a failover or bringing the database online manually.