Setting indirect checkpoints by database in SQL Server 2012

This blog post applies to SQL Server 2012 CTP3 (11.0.1440).

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_end2011-10-18 20:54:46.200

checkpoint_end2011-10-18 20:54:46.650

checkpoint_end2011-10-18 20:54:47.063

checkpoint_end2011-10-18 20:54:47.433

checkpoint_end2011-10-18 20:54:47.647

checkpoint_end2011-10-18 20:54:48.200

checkpoint_end2011-10-18 20:54:48.597

checkpoint_end2011-10-18 20:54:49.157

checkpoint_end2011-10-18 20:54:49.620

checkpoint_end2011-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_end2011-10-18 21:06:52.697

checkpoint_end2011-10-18 21:06:53.520

checkpoint_end2011-10-18 21:06:54.717

checkpoint_end2011-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).