SQL Server 2012 introduces the “indirect checkpoints” feature. At a high level, it allows you to adjust the target recovery time of a specific database, rather than relying entirely on the SQL Server instance-level ‘recovery interval (min)’ setting. To demonstrate the impact, I’ll walk through a scenario where we’re performing large batch inserts into a data warehousing Fact table. In this demo I’m using the Codeplex “AdventureWorksDWDenali” database.
As a first step, I’ll back up the database so that I can reset the baseline in order to compare the default checkpoint behavior with the new indirect checkpoint setting:
USE master
GO
BACKUP DATABASE [AdventureWorksDWDenali]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\IndirectCheckpoint_AWDW.bak'
WITH NOFORMAT, NOINIT, NAME = N'AdventureWorksDWDenali-Full Database Backup', CHECKSUM
GO
Now with that out of the way I’ll check the recovery interval of the SQL Server instance:
SELECT value_in_use
FROM sys.configurations
WHERE name = 'recovery interval (min)'
The value in use is “0” – the default. So basically the estimated checkpoint will be every minute for an active database (although I’m over-simplifying things here – since automatic checkpoint frequency varies based on other factors as well - but for this demo you'll still see a clear difference in the before-and-after).
Now I’ll check the indirect checkpoint time for the AdventureWorksDWDenali database:
SELECT target_recovery_time_in_seconds
FROM sys.databases
WHERE name = 'AdventureWorksDWDenali'
This returns “0” – meaning we haven’t configured this new SQL Server 2012 database option (yet) and so the SQL Server instance level setting is in effect.
Now I’ll create an extended events session to track the individual checkpoint events for the database (database_id "5" happens to be the AdventureWorksDWDenali database on my instance):
CREATE EVENT SESSION [track_checkpoints] ON SERVER
ADD EVENT sqlserver.checkpoint_end(
ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.is_system)
WHERE ([sqlserver].[database_id]=(5)))
ADD TARGET package0.ring_buffer(SET max_memory=(2048))
GO
(By the way - I updated max_memory down to 2MB from the 100MB per Jonathan Kehayias' heads up on potential limits - not to mention that this was more memory than needed for this example).
Next I’ll turn it on:
ALTER EVENT SESSION [track_checkpoints] ON SERVER STATE=START
GO
And now I’m going to push some I/O by creating a new table and populating it multiple times from the FactInternetSales table:
USE AdventureWorksDWDenali
GO
SELECT *
INTO dbo.CheckPoint_Test_FactInternetSales
FROM dbo.FactInternetSales
GO
INSERT dbo.CheckPoint_Test_FactInternetSales
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, SalesTerritoryKey, LEFT(CAST(NEWID() AS NVARCHAR(36)),20), SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount,
TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, OrderDate, DueDate, ShipDate
FROM dbo.FactInternetSales
GO 10
This shows the following rows affected:
(60398 row(s) affected)
Beginning execution loop
(60398 row(s) affected)
(60398 row(s) affected)
(60398 row(s) affected)
(60398 row(s) affected)
(60398 row(s) affected)
(60398 row(s) affected)
(60398 row(s) affected)
(60398 row(s) affected)
(60398 row(s) affected)
(60398 row(s) affected)
Batch execution completed 10 times.
Now I’ll query the track_checkpoints Extended Event session (** and thanks to my colleague Jonathan Kehayias for the following query which I just modified to query against track_checkpoints and then change a few column names **):
SELECT
n.value('(event/@name)[1]', 'varchar(50)') AS event_name,
n.value('(event/@timestamp)[1]', 'datetime') as event_datetime
FROM
( SELECT td.query('.') as n
FROM
(
SELECT CAST(target_data AS XML) as target_data
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t
ON s.address = t.event_session_address
WHERE s.name = 'track_checkpoints'
AND t.target_name = 'ring_buffer'
) AS sub
CROSS APPLY target_data.nodes('RingBufferTarget/event') AS q(td)
) as tab
GO
This returns the following checkpoint event data:
event_name event_datetime
checkpoint_end 2011-10-18 20:54:46.200
checkpoint_end 2011-10-18 20:54:46.650
checkpoint_end 2011-10-18 20:54:47.063
checkpoint_end 2011-10-18 20:54:47.433
checkpoint_end 2011-10-18 20:54:47.647
checkpoint_end 2011-10-18 20:54:48.200
checkpoint_end 2011-10-18 20:54:48.597
checkpoint_end 2011-10-18 20:54:49.157
checkpoint_end 2011-10-18 20:54:49.620
checkpoint_end 2011-10-18 20:54:50.127
So we see 10 checkpoints for the default behavior.
And now I’m going to stop the event session and restore the database back to its original state to show you the comparison to the SQL Server 2012 indirect checkpoint option:
ALTER EVENT SESSION [track_checkpoints] ON SERVER STATE=STOP
-- Restoring the database back to the original format
USE master
GO
RESTORE DATABASE [AdventureWorksDWDenali]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\IndirectCheckpoint_AWDW.bak'
WITH FILE = 1
GO
Next I’m going to change the database’s TARGET_RECOVERY_TIME to 5 minutes (this is the SQL Server 2012 indirect checkpoints feature I was talking about):
ALTER DATABASE AdventureWorksDWDenali
SET TARGET_RECOVERY_TIME = 5 MINUTES
Just to confirm that it “took” – I’ll validate sys.databases:
SELECT target_recovery_time_in_seconds
FROM sys.databases
WHERE name = 'AdventureWorksDWDenali'
This returns 300 seconds instead of the 0 seconds we saw before – 300 representing the new target recovery time I just set in minutes.
And now I’m going to enable the event session again and re-populate the table:
ALTER EVENT SESSION [track_checkpoints] ON SERVER STATE=START
GO
USE AdventureWorksDWDenali
GO
SELECT *
INTO dbo.CheckPoint_Test_FactInternetSales
FROM dbo.FactInternetSales
GO
INSERT dbo.CheckPoint_Test_FactInternetSales
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, SalesTerritoryKey, LEFT(CAST(NEWID() AS NVARCHAR(36)),20), SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount,
TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, OrderDate, DueDate, ShipDate
FROM dbo.FactInternetSales
GO 10
How many checkpoints will we see now? With all things equal between tests, we now only see four checkpoints:
event_name event_datetime
checkpoint_end 2011-10-18 21:06:52.697
checkpoint_end 2011-10-18 21:06:53.520
checkpoint_end 2011-10-18 21:06:54.717
checkpoint_end 2011-10-18 21:06:56.277
I tested this before-and-after scenario three times, and each time I saw the identical change in the number of checkpoints. So I saw a consistent result given an identical test setup.
That’s all for now – but two closing thoughts:
· I like how we’re able to adjust this target recovery time at the database level. This allows us to take into account the recovery time requirements per application if we need to – adjusting upward or downward based on different requirements or issues.
· I didn’t measure the actual performance impact against the load, but if I had I imagine we would have seen some performance improvement with the second scenario of less frequent checkpoints (but with a trade-off of longer recovery time - a nontrivial consideration).