Thursday, March 19, 2015

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.


No comments: