Indirect CheckPoints no SQL Server 2012

Posted on novembro 8, 2012

1


 

Introdução

Um dos novos recursos do SQL Server 2012 chamado de Indirect CheckPoint tem como objetivo ter acesso ao tempo no qual as “Dirty Pages” serão tiradas do “Buffer Cache” para ser colocado em disco. Porém para entendermos como essa novo recurso trabalha em nível banco de dados, precisamos entender como realmente um CheckPoint funciona e o porque de sua importância. 

CheckPoint

O CheckPoint é o processo que retira as páginas de dados que já foram alteradas dentro do “Buffer Cache” e as persiste em disco. Para que isso seja realizado, há diversas operação que acontecem.

– É necessário inicialmente que um registro seja gravado no LOG, marcando assim o início dessa operação.

– Escrever todas as “Dirty Pages” em disco no log e no dado.

– Escrever no log o fim do CheckPoint.

 

Porém é importante saber o motivo do qual faz com que esse processo seja executado, e com isso temos algumas situações.

– Em operação Minimamente Logadas como BCP.

– Arquivos do banco de dados adicionados pelo comando ALTER DATABASE.

– Parar ou Reiniciar a instância do SQL Server.

– Por padrão o SQL Server realiza a geração de CheckPoints de 1 em 1 minuto, porém dependento do ambiente esse valor pode diminuir consideravelmente.

– Backup tirado do banco de dados.

 

Os CheckPoints que são realizados automaticamente pelo SQL Server são gerados pela Engine do Banco de dados, a partir de intervalos de tempo (padrão de 1 em 1 minuto). O CheckPoint é totalmente influenciável pelo tipo de “Recovery Model” que você está utilizando para seu banco de dados. Se estiver em FULL ou Bullk-Logged então CheckPoints irão ser executados sempre que o números de registros no LOG atingir a estimativa na qual o Database Engine estipula. Se estiver utilizando o “Recovery Model” Simple, alêm de realizar CheckPoint pelas estimativas da Engine ele tambêm irá realizar quando 70% do arquivo de log estiver cheio.

 

Indirect CheckPoint

Ou seja por mais que se pareça um processo relativamente simples, dependendo do ambiente no qual você está lidando o impacto de diversos CheckPoints acontecendo repetitivamente poderá causar alguns impactos ao ambiente. Com isso o novo recurso do SQL Server 2012 possibita o controle por banco de dados do tempo no qual será realizados os CheckPoints, para entermos melhor irei demonstrar aqui.

 

Primeiramente iremos realizar um Database Snapshot do banco de dados AdentureWorks2012 para mantermos íntegro.

CREATE DATABASE AdventureWorks2012_20121107

ON ( Name = AdventureWorks2012_Data,

        Filename = ‘C:\temp\AdventureWorks2012.ss’)

AS SNAPSHOT OF AdventureWorks2012

Com isso iremos ver a configuração da instância do Recovery Interval (min), ou seja a configuração de frequência dos CheckPoints automáticos. Por padrão temos 0.

SELECT *

FROM sys.configurations

WHERE name = ‘recovery interval (min)’

image

(Figura 1 – Valor de Configuração do Recovery Interval (min).)

 

Após isso, iremos ver como está a configuração do Indirect CheckPoint no banco de dados.

 

SELECT database_id, target_recovery_time_in_seconds, recovery_model_desc

FROM sys.databases

WHERE name = ‘AdventureWorks2012’

image

(Figura 2 – Target_Recovery_Time_In_Second padrão.)

 

 

Agora nesse momento iremos realizar a criação de um Extended Event para capturarmos a frequência do CheckPoint desse banco de dados.

 

USE AdventureWorks2012

go

 

CREATE EVENT SESSION CheckPointAdventureWorks2012

ON SERVER

ADD EVENT sqlserver.checkpoint_end(ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.is_system)

WHERE ([sqlserver].[database_id]=(8)))

ADD TARGET package0.ring_buffer(SET max_memory=(2048))

GO

 

ALTER EVENT SESSION CheckPointAdventureWorks2012

ON SERVER STATE = START

GO

Iremos estimular o CheckPoint com algumas inserções no banco de dados.

 

SELECT *

INTO dbo.CheckPointIndirectInsert

FROM Sales.SalesOrderHeader

GO

 

INSERT INTO [AdventureWorks2012].[dbo].[CheckPointIndirectInsert]

SELECT [RevisionNumber]

      ,[OrderDate]

      ,[DueDate]

      ,[ShipDate]

      ,[Status]

      ,[OnlineOrderFlag]

      ,[SalesOrderNumber]

      ,[PurchaseOrderNumber]

      ,[AccountNumber]

      ,[CustomerID]

      ,[SalesPersonID]

      ,[TerritoryID]

      ,[BillToAddressID]

      ,[ShipToAddressID]

      ,[ShipMethodID]

      ,[CreditCardID]

      ,[CreditCardApprovalCode]

      ,[CurrencyRateID]

      ,LEFT(1000 + (1001000)*RAND(),3)

      ,LEFT(10 + (010)*RAND(),1)

      ,[Freight]

      ,[TotalDue]

      ,[Comment]

      ,NEWID()

      ,CAST(CAST(RAND()*100000 AS INT) AS DATETIME)

FROM AdventureWorks2012.Sales.SalesOrderHeader

go 50

 

Após inserções veremos a quantidade de CheckPoints que foram gerados a partir desses inserts.

 

 

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 = ‘CheckPointAdventureWorks2012’

          AND t.target_name = ‘ring_buffer’

    ) AS sub

    CROSS APPLY target_data.nodes(‘RingBufferTarget/event’) AS q(td)

) AS tab

GO

 

Fonte da Consulta: http://www.sqlskills.com/blogs/jonathan/

 

image

(Figura 3 – Quantidade de CheckPoints gerados pelo Insert.)

 

image

(Figura 4 – Quantidade Total.)

 

Ou seja tivemos 50 CheckPoints somente para essa operação.  Agora, iremos parar o Extended Event e restaurar o Database Snapshot do banco de dados AdventureWorks2012.

 

ALTER EVENT SESSION CheckPointAdventureWorks2012

ON SERVER STATE = STOP

 

USE master

go

 

RESTORE DATABASE AdventureWorks2012

FROM DATABASE_SNAPSHOT = ‘AdventureWorks2012_20121107’;

GO

 

Após isso, iremos agora habilitar o novo recurso, colocaremos o Indirect CheckPoint para 5 minutos.

 

ALTER DATABASE AdventureWorks2012

SET TARGET_RECOVERY_TIME = 5 MINUTES

 

SELECT database_id, target_recovery_time_in_seconds, recovery_model_desc

FROM sys.databases

WHERE name = ‘AdventureWorks2012’

image

(Figura 5 – Alterando a Configuração do Indirect CheckPoint para o banco de dados.)

 

Agora iremos habilitar novamente o Extended Event e realizar todos os inserts novamente.

 

SELECT *

INTO dbo.CheckPointIndirectInsert

FROM Sales.SalesOrderHeader

GO

 

INSERT INTO [AdventureWorks2012].[dbo].[CheckPointIndirectInsert]

SELECT [RevisionNumber]

      ,[OrderDate]

      ,[DueDate]

      ,[ShipDate]

      ,[Status]

      ,[OnlineOrderFlag]

      ,[SalesOrderNumber]

      ,[PurchaseOrderNumber]

      ,[AccountNumber]

      ,[CustomerID]

      ,[SalesPersonID]

      ,[TerritoryID]

      ,[BillToAddressID]

      ,[ShipToAddressID]

      ,[ShipMethodID]

      ,[CreditCardID]

      ,[CreditCardApprovalCode]

      ,[CurrencyRateID]

      ,LEFT(1000 + (1001000)*RAND(),3)

      ,LEFT(10 + (010)*RAND(),1)

      ,[Freight]

      ,[TotalDue]

      ,[Comment]

      ,NEWID()

      ,CAST(CAST(RAND()*100000 AS INT) AS DATETIME)

FROM AdventureWorks2012.Sales.SalesOrderHeader

go 50

 

Vendo novamente a quantidade de “Automatic CheckPoints” realizados temos….

 

image

(Figura 6 – Quantidade de CheckPoints gerados pelo Insert após a configuração do Indirect CheckPoint.)

 

image

(Figura 7 – Quantidade Total.)

 

Conclusão

Esse recurso tem que ser utilizado com cuidado, é preciso conhecer bastante seu ambiente para se aventurar nessa configuração, podemos ter ganhos consideráveis, porem tudo é um TRADEOFF, se não estiver com um disco bom em suas mãos, então a quantidade de registros irá aumentar e com isso a espera para gravação poderá ser maior, dentre outros fatores que essa opção pode acarretar para seu ambiente.

Posted in: SQL Server 2012