How does a database RESTORE affect plan cache?
Tags: sql, sqlserver, backup
Published: 2020-01-01 15:08:32

---
title: How does a database RESTORE affect plan cache?
published: true
date: 2019-10-24 14:12:48 UTC
tags: sql, sqlserver, backup
canonical_url: https://am2.co/2019/10/how-does-a-database-restore-affect-plan-cache/
---

If you restore a database, what does that do to the plan cache? Well, let’s start by looking at [the documentation for RESTORE](https://web.archive.org/web/20190906084812/https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-2017#general-remarks). (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.

[![](http://am2.co/wp-content/uploads/joshua-naidoo-J954SeqDitQ-unsplash.jpg)](https://www.youtube.com/watch?v=RuW1f6FI18g)<figcaption id="caption-attachment-2248">This beautifully restored Mini would clear some cash from my bank account.</figcaption>

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](https://twitter.com/calliepie) and [Day-Z](https://twitter.com/day_z_spotz). We’ll also create a simple stored procedure in each database that we’ll use to start testing:

```sql
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

```sql
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'));
```

[![](http://am2.co/wp-content/uploads/RestoreCache_1.png)](http://am2.co/)

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

```sql
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'));
```

[![](http://am2.co/wp-content/uploads/RestoreCache_2.png)](http://am2.co/2019/10/how-does-a-database-restore-affect-plan-cache/restorecache_2/)

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

```sql
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'));
```

[![](http://am2.co/wp-content/uploads/RestoreCache_3.png)](http://am2.co/2019/10/how-does-a-database-restore-affect-plan-cache/restorecache_3/)

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](https://github.com/MicrosoftDocs/sql-docs), and [anyone can contribute to the documentation](https://docs.microsoft.com/en-us/sql/sql-server/sql-server-docs-contribute).

I’ve [submitted a pull request](https://github.com/MicrosoftDocs/sql-docs/pull/3353) to make the language more accurate–by the time you’re reading this, the [current docs](https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql#general-remarks) 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.

The post [How does a database RESTORE affect plan cache?](https://am2.co/2019/10/how-does-a-database-restore-affect-plan-cache/) appeared first on [Andy M Mallon - AM²](https://am2.co).