I changed the Recovery Model before BULK operation, is it a good practice?

I have already written a post on bulk operations, indicating the benefits we get by changing the recovery model before performing bulk operations. I got a couple offline questions after my SQL Server Stored Procedure session at SS SLUG and one question was this. I was talking about how plan cache getting flushed away and I mentioned that changing recovery model causes to get the plan cache flushed. Now the question is, should we change the recovery model for our bulk operation?

First of all, let's see whether changing the Recovery Model causes to get the plan cached removed. Here is the code I wrote for testing.

I am executing two SELECT statements, one against AdventureWorks2014 and another with AdventureWorksDW2014. The last statement checks plan cache.

-- EXECUTE STATEMENT SEPARATELY

-- Cleaning buffer
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
  
USE AdventureWorks2014;
GO

-- Query Sales.SalesOrderDetail table
SELECT * FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID = 1;
  

USE AdventureWorksDW2014;
GO

-- Query dbo.DimProduct
SELECT * FROM dbo.DimProduct
WHERE ProductKey = 1;

-- Checking chached plans
SELECT p.usecounts, p.cacheobjtype, p.objtype, t.text
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t
WHERE 
 TEXT LIKE '%SELECT * FROM Sales.SalesOrderDetail%'
 OR TEXT LIKE '%SELECT * FROM dbo.DimProduct%';

As we expected, we see two cached plans in plan cache.

This code changes the Recovery Model of AdventureWorks2014. Second statement shows plans in the cache after the change.

-- Change the Recovery Model
ALTER DATABASE AdventureWorks2014 SET RECOVERY Bulk_Logged;
GO

-- And see plans
SELECT p.usecounts, p.cacheobjtype, p.objtype, t.text
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t
WHERE 
 TEXT LIKE '%SELECT * FROM Sales.SalesOrderDetail%'
 OR TEXT LIKE '%SELECT * FROM dbo.DimProduct%';

As you see, plan related to AdventureWorks2014 has been removed. This proves that changing the Recovery Model flushes plans in the cache.

Now the question is, how advisable to do this in production environment for bulk operations. My recommendation is, if it is a large bulk operation and database is heavily accessed by users, bulk operation should be done during off-peak hours. We still lose cached plans but this will at least minimize the damage. However, if the database is continuously being used by users, then it is advisable to perform bulk operations without changing the Recovery Model.

How to fill the plan cache efficiently - Optimize for ad hoc workloads option

It is good to keep all plans used for compiled queries in the memory, as it helps to optimize the query processing, allowing engine to reuse the cached plan without recompiling query again for subsequent executions. But what if users frequently issue one-time-execute adhoc queries, and very rare to issue the same again? In that case, we should not waste the memory allocated to plans with them and should hold them only if they are executed again. How do we do it?

We can instruct SQL Server not to cache the plan for adhoc queries at the first execution but at the second execution using a server level setting. It is not recommend to change the default behavior but if we are opening the database for a similar situation, it may offer some performance benefit for other queries specifically with stored procedures.

This setting is called as optimize for ad hoc workloads. By default, it is disable, and it can be enabled passing 1 for this setting using sp_configure.

Here is the code for enabling it.

sp_configure 'optimize for ad hoc workloads', 1;
GO
RECONFIGURE;
GO

Now it is enabled. Let's run a query and see how it is cached.

-- Cleaning buffer
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
 
-- Simple adhoc query with a filter
SELECT * FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID = 1;
 
-- Checking chached plans
-- We should see one record for above query
SELECT p.usecounts, p.cacheobjtype, p.objtype, t.text
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t
WHERE p.usecounts > 0 AND
TEXT LIKE '%SELECT * FROM Sales.SalesOrderDetail%'
ORDER BY p.usecounts DESC;

As you see, there is a record indicating that plan is cached but the type is different. It means that plan has not been fully cached and SQL Server will not be able to use it again for next execution. This small compiled plan stub helps to identify the query with next execution, and then plan is recreated and stored.

Execute the SELECT query again and see the plan cache.

It is cached. Execute again and see.

It is being reused now. This saves resources heavily but have to enable only if we are 100% sure that we always get dissimilar adhoc queries.

For more info on this setting, refer: https://msdn.microsoft.com/en-us/library/cc645587.aspx

Does SQL Server use same cached plan for same statement with subsequent execution?

When you send a request to SQL Server, it takes your statement via four main steps. Generating a plan for it is part of these four steps. These steps are called as: Parsing, Normalization, Compilation, and Optimization. Parsing makes sure the query syntax is correct and reforms the query for compilation. Normalization checks all the object that have been referenced in the query for existence. This is step that throws errors such as Invalid object, Object not found. Compilation generates multiple plans for the query and Optimization picks the best and places it into the buffer.

Now the question is, once the plan is generated for the query, will it be reused with subsequent execution. Most of the time, we expect the plan to be reused but it is not always the case. Here is an example for it;

This adhoc statement queries against Sales.SalesOrderDetail table in AdventureWorks2014, filtering SalesOrderDetailID to 1. Do not execute queries together, execute one by one without selecting comments.

-- Cleaning buffer
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO

-- Simple adhoc query with a filter
SELECT * FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID = 1;

-- Checking chached plans
-- We should see one record for above query
SELECT p.usecounts, p.cacheobjtype, p.objtype, t.text
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t
WHERE p.usecounts > 0 AND
TEXT LIKE '%SELECT * FROM Sales.SalesOrderDetail%'
ORDER BY p.usecounts DESC;

As you see we have one cached plan for the query executed. Let's run the query again with the same filter and with some different filter values. Again, run these queries separately.

-- Executing the same 
-- and with two different values
-- Run these SELECT separately
SELECT * FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID = 1;

SELECT * FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID = 1000;

SELECT * FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID = 100000;

-- Check cached plans again
SELECT p.usecounts, p.cacheobjtype, p.objtype, t.text
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t
WHERE p.usecounts > 0 AND
TEXT LIKE '%SELECT * FROM Sales.SalesOrderDetail%'
ORDER BY p.usecounts DESC;

Can you see how the plan has been reused. SQL Server has reused the plan only for the first statement and for other statements, it has created new plans even though the query is same. How this can be happened?

This is not an issue or bug, this is how it compiles queries. When SQL Server receives a query, it checks the cache for plans by comparing the entire statement. As you see, even though the statement is same, it creates a new plan because statement does not match with previous one (because of the filter value). So, keep this in mind, if the query is lengthy and complex, the time it takes for executing will be always same when filter values are different, even though you expect it to be optimized by reusing the plan. This is one of reasons for recommending Stored Procedure for queries that are executed frequently with different filters.