Backup and Restore of the SQL Server buffer pool

Have you ever missed this feature? Whenever you install the latest security update, it is very likely that your server requires a restart, leading also to a restart of SQL Server. If this ever happens, SQL Server needs to start from scratch, having nothing in the plan cache and also no data pages in the pool.I have many customers that complain about applications performing poorly after a server restart – for whatever reason that may have been necessary.

So finally, in the current project, we ended up developing a method for warming up the cache on our own after SQL Server has been started. Obviously not an easy task, but we had a smart guy who implemented this. The final solution is by no means perfect but almost sufficient according to our requirements.

When discussing about how we should implement this feature and also the pros and cons, we had the idea that we did nothing more than SQL Server itself should be capable of. Wouldn’t it be nice, if we could just perform a backup of the buffer pool and later restore it? Or even better, how about just sending SQL Server to hibernate mode (like Windows) and wake it up later? Clearly this won’t work under every circumstance, since some updates or patches may require a "cold" restart. But in those cases where it’s technically possible, I’d like to have this opportunity.

I like the idea so much that I’ve added a regarding change request on Microsoft’s connect platform (item# 561951). If you agree, you can vote for it here.

Implicit type conversation and the plan cache

You probably read already something about caveats and catches when relying on implicit type conversation. Here’s another one regarding the plan cache.

Whenever an execution plan is compiled, SQL Server will try to store this plan in the plan cache. That’s because plan compilations are quite expensive in respect of CPU usage and therefore should be minimized. But sometimes this process doesn’t work as expected. Have a look at the following example.

Problem

First, we clear the plan cache

dbcc freeproccache

Now let’s execute the following query:

select * from msdb.dbo.backupfile  where backup_set_id=100000

and see what’s in the plan cache by invoking this query:

select qt.text       ,qs.execution_count   from sys.dm_exec_query_stats as qs        cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt

Here’s the result:

 

Ok, no surprise. There’s a cached plan with a usage count of 1. This is as expected.

Now, let’s execute a second query like this:

select * from msdb.dbo.backupfile  where backup_set_id=1000

I expected the cached plan was reused. But surprisingly not! Let’s see what the plan cache reveals now. It looks like this:

As we asked for backup_set_id=1000 this time, our parameter is implicitly converted into a SMALLINT, since SMALLINT is the smallest possible data type a value of 1000 fits into. Consequently a new plan is generated and cached.

We can repeat our experiment with a predicate of WHERE backup_set_id=10 and see another plan is going into the plan cache:

So, we have three plans now, all with an execution count of 1!

As for me, I think this behavior is somewhat strange. The column backupset_id is of type INT, so why not just create one plan with INT as parameter type and stick to this plan?

Solutions

Since implicit type conversation is the root of our problem, we may solve it by avoiding this implicit conversation and cast explicit. We can re-write our queries like this:

select * from msdb.dbo.backupfile  where backup_set_id=cast(100000 as int)   select * from msdb.dbo.backupfile  where backup_set_id=cast(1000 as int) select * from msdb.dbo.backupfile  where backup_set_id=cast(10 as int)

After doing so, there’s only one plan in the cache. But I suppose, you wouldn’t want to introduce those explicit casts to all of your queries. It’s not very readable and looks like bad programming style, doesn’t it?

Another solution comes through sp_executesql:

exec sp_executesql N'select * from msdb.dbo.backupfile                       where backup_set_id=@id'                   ,N'@id int'                   ,@id = 100000   exec sp_executesql N'select * from msdb.dbo.backupfile                       where backup_set_id=@id'                   ,N'@id int'                   ,@id = 1000 exec sp_executesql N'select * from msdb.dbo.backupfile                       where backup_set_id=@id'                   ,N'@id int'                   ,@id = 10

sp_executesql expects parameter declarations and, therefore, no implicit type conversation is involved this time. We see only one cached plan with an execution count of 3, when all of the above three queries have been executed.