How does a database RESTORE affect plan cache?

October 24, 2019 Andy Backup & Recovery 0

If you restore a database, what does that do to the plan cache? Well, let’s start by looking at the documentation for RESTORE. (Emphasis mine)

Restoring a database clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. For each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: ” SQL Server has encountered %d occurrence(s) of cachestore flush for the ‘%s’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations”. This message is logged every five minutes as long as the cache is flushed within that time interval.

This beautifully restored Mini would clear some cash from my bank account.

Yikes. That first sentence sounds like it is going to clear the cache for the entire instance. That seems excessive. If I restore a new database (ie, I am creating a new database from backup), that shouldn’t affect the plan cache at all. And if I restore over top an existing database, I would only expect the cache for that one database to be affected. I can’t think of a scenario where I would want to “clear the plan cache for the instance” when doing a restore.

Let’s think of a few scenarios, and we can test:

  • Creating a new database from backup (I don’t think this should affect the plan cache at all)
  • Restoring over an existing database (I would expect only the cache for this one database to be cleared)
  • Restoring over an existing database that is involved in cross-database activity (This could be more interesting)

Let’s set up a test

I’m currently sipping coffee with one of my dogs laying at my feet. So let’s name these test databases after my dogs, Callie and Day-Z. We’ll also create a simple stored procedure in each database that we’ll use to start testing:

CREATE DATABASE Callie;
CREATE DATABASE DayZ;
GO
USE Callie;
GO
CREATE OR ALTER PROCEDURE dbo.Woof
AS
SELECT Callie = 'Woof!'
WHERE EXISTS (SELECT 1
			FROM sys.objects AS o
			WHERE o.is_ms_shipped = 0
);
GO
USE DayZ;
GO
CREATE OR ALTER PROCEDURE dbo.Arf
AS
SELECT DayZ = 'Arf!'
WHERE EXISTS (SELECT 1
			FROM sys.objects AS o
			WHERE o.is_ms_shipped = 0
);
GO

Creating a new database from backup

We’re creating a brand new database that doesn’t exist yet–we’re just doing it by using a RESTORE DATABASE command instead of a CREATE DATABASE command. I wouldn’t expect this to affect the plan cache at all (CREATE DATABASE doesn’t affect the plan cache.)

Here’s our testing plan:

  • Create a backup
  • Execute a stored procedure in each of the Callie & Day-Z databases
  • Confirm both procedures are in cache
  • Create a new Callie_BAK database from the backup
  • See if the procedures are both still in cache
BACKUP DATABASE Callie TO DISK = 'C:\SQL\BAK\Callie.bak' WITH INIT;
GO
DBCC FREEPROCCACHE;

USE Callie
GO
EXEC dbo.Woof;
GO
USE DayZ
GO
EXEC dbo.Arf;
GO
USE master
GO

SELECT  DatabaseName = db_name(t.dbid), 
		ObjectName   = object_name(t.objectid,t.dbid)
FROM sys.dm_exec_cached_plans AS qp
CROSS APPLY sys.dm_exec_sql_text (qp.plan_handle) AS t
WHERE t.dbid IN (db_id('Callie'),db_id('DayZ'));

RESTORE DATABASE Callie_bak FROM DISK = 'C:\SQL\BAK\Callie.bak' 
	WITH REPLACE,
		 MOVE 'Callie' TO 'C:\SQL\MSSQL15.MSSQLSERVER\MSSQL\DATA\Callie2.mdf',
		 MOVE 'Callie_Log' TO 'C:\SQL\MSSQL15.MSSQLSERVER\MSSQL\DATA\Callie2_log.ldf';

SELECT  DatabaseName = db_name(t.dbid), 
		ObjectName   = object_name(t.objectid,t.dbid)
FROM sys.dm_exec_cached_plans AS qp
CROSS APPLY sys.dm_exec_sql_text (qp.plan_handle) AS t
WHERE t.dbid IN (db_id('Callie'),db_id('DayZ'));

That’s exactly what I expected. Even after doing a RESTORE to create a new database, the plan cache is unaffected. Both of the procedures that were in cache before the restore are there after the restore.

Restoring over an existing database

In this case, we’re going to destroy an existing database & recreate it from backup. I’d expect SQL Server would clear the cache for just this one database that I’m replacing–but not the other one.

Here’s our testing plan:

  • Create a backup
  • Execute a stored procedure in each of the Callie & Day-Z databases
  • Confirm both procedures are in cache
  • Replace the Day-Z database
  • See if the procedures are both still in cache
BACKUP DATABASE DayZ   TO DISK = 'C:\SQL\BAK\DayZ.bak'   WITH INIT;

DBCC FREEPROCCACHE;

USE Callie
GO
EXEC dbo.Woof;
GO
USE DayZ
GO
EXEC dbo.Arf;
GO
USE master
GO

SELECT  DatabaseName = db_name(t.dbid), 
		ObjectName   = object_name(t.objectid,t.dbid)
FROM sys.dm_exec_cached_plans AS qp
CROSS APPLY sys.dm_exec_sql_text (qp.plan_handle) AS t
WHERE t.dbid IN (db_id('Callie'),db_id('DayZ'));


RESTORE DATABASE DayZ FROM DISK = 'C:\SQL\BAK\DayZ.bak' 
	WITH REPLACE;

SELECT  DatabaseName = db_name(t.dbid), 
		ObjectName   = object_name(t.objectid,t.dbid)
FROM sys.dm_exec_cached_plans AS qp
CROSS APPLY sys.dm_exec_sql_text (qp.plan_handle) AS t
WHERE t.dbid IN (db_id('Callie'),db_id('DayZ'));

This makes a lot of sense, too. After doing a RESTORE to replace an existing database, the plan cache is cleared for just the one database. The procedure for the other database is still in cache after the RESTORE.

Restoring over an existing database that is involved in cross-database activity

I’m guessing that this will behave just the prior test–only the restored database will have it’s plan cache affected. The plan cache just associates the object’s plan with a single database–it doesn’t map the plan to every database involved, just the one that “owns” it. That said, I do have a glimmer of doubt. SQL Server is pretty smart–maybe they’ve figured out that if the Callie database depends on DayZ, then restoring DayZ would affect the Callie plan & demand a recompile? Let’s find out.

Here’s our testing plan:

  • Create a backup
  • Create some new procedures that do cross-database joins
  • Execute those new stored procedures in each of the Callie & Day-Z databases
  • Confirm both procedures are in cache
  • Replace the Day-Z database
  • See if the procedures are both still in cache
USE Callie;
GO
CREATE OR ALTER PROCEDURE dbo.DoggiePlayTime
AS
SELECT Callie = 'Woof!'
WHERE EXISTS (SELECT 1
			FROM sys.objects AS o
			WHERE o.is_ms_shipped = 0
)
AND EXISTS (SELECT 1
			FROM DayZ.sys.objects AS o
			WHERE o.is_ms_shipped = 0
);
GO
USE DayZ;
GO
CREATE OR ALTER PROCEDURE dbo.DoggiePlayTime
AS
SELECT DayZ = 'Arf!'
WHERE EXISTS (SELECT 1
			FROM sys.objects AS o
			WHERE o.is_ms_shipped = 0
)
AND EXISTS (SELECT 1
			FROM Callie.sys.objects AS o
			WHERE o.is_ms_shipped = 0
);
GO

BACKUP DATABASE DayZ   TO DISK = 'C:\SQL\BAK\DayZ.bak'   WITH INIT;

DBCC FREEPROCCACHE;

USE Callie
GO
EXEC dbo.DoggiePlayTime;
GO
USE DayZ
GO
EXEC dbo.DoggiePlayTime;
GO
USE master
GO

SELECT  DatabaseName = db_name(t.dbid), 
		ObjectName   = object_name(t.objectid,t.dbid)
FROM sys.dm_exec_cached_plans AS qp
CROSS APPLY sys.dm_exec_sql_text (qp.plan_handle) AS t
WHERE t.dbid IN (db_id('Callie'),db_id('DayZ'));


RESTORE DATABASE DayZ FROM DISK = 'C:\SQL\BAK\DayZ.bak' 
	WITH REPLACE;

SELECT  DatabaseName = db_name(t.dbid), 
		ObjectName   = object_name(t.objectid,t.dbid)
FROM sys.dm_exec_cached_plans AS qp
CROSS APPLY sys.dm_exec_sql_text (qp.plan_handle) AS t
WHERE t.dbid IN (db_id('Callie'),db_id('DayZ'));

Look at that–even with cross-database dependencies, the plan cache is only cleared for the database that was replaced. The procedure for the other database is still in cache after the RESTORE.

What about those docs?

The docs are imprecise, and thus inaccurate:

Restoring a database clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. For each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: ” SQL Server has encountered %d occurrence(s) of cachestore flush for the ‘%s’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations”. This message is logged every five minutes as long as the cache is flushed within that time interval.

Thankfully, the SQL Server documentation is on GitHub, and anyone can contribute to the documentation.

I’ve submitted a pull request to make the language more accurate–by the time you’re reading this, the current docs may already be updated to reflect my suggested language:

Restoring a database clears the plan cache for the database being restored. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance.