Identificando auto update statistics utilizando fn_dblog

Olá pessoal,

Recentemente estava realizando alguns estudos sobre estatisticas no SQL Server e encontrei uma forma de verificar quando o SQL Server realizou a atualização das estatisticas. Neste método que encontrei o SQL Server estava apenas registrando quando uma atualização automatica acontecia ou quando o comando UPDATE STATISTICS TableName é executado. Sabemos que existe também outros comandos para atualizar as estatisticas, porem esses não são registrados no transaction log, acredito que vale mais estudos para entender o comportamento. Para demonstrar então como esse evento é registrado vamos as demonstrações abaixo utilizando a função fn_dblog.

USE master
GO

IF EXISTS ( SELECT  1
            FROM    sys.databases
            WHERE   name = 'CreditCard' ) 
    BEGIN
        DROP DATABASE CreditCard
    END
GO
CREATE DATABASE CreditCard
GO
USE CreditCard
GO
CREATE TABLE tblCredit
    (
      idCredit INT IDENTITY ,
      FirstName VARCHAR(30) ,
      LastName VARCHAR(30) ,
      MiddleName VARCHAR(20)
    )
GO

INSERT  INTO tblCredit
VALUES  ( 'Marcos', 'Freccia', 'MF' ),
        ( 'Sara', 'Barbosa', 'Jardim' ),
        ( 'Henrique', 'Ribeiro', 'HR' ),
        ( 'Catiane', 'Souza', 'CS' ),
        ( 'Matheus', 'Storck', 'MS' ),
        ( 'Fabiano', 'Pereira', 'FP' ),
        ( 'Mauricio', 'Moura', 'MM' ),
        ( 'Nathalia', 'Mercio', 'MM' ),
        ( 'Ruy', 'Pimentel', 'RP' ),
        ( 'Thirumala', 'Komminemi', 'TK' )
GO 30

CREATE CLUSTERED INDEX ix_cluster ON dbo.tblCredit(idCredit)
GO
CREATE NONCLUSTERED INDEX ix_noncluster1 ON dbo.tblCredit(LastName,FirstName)

CHECKPOINT

 

No script acima estamos apenas criando uma tabela e populando dados na mesma.

A partir do SQL Server 2008 R2 SP2 possuimos uma nova DMF para visualizar as estatisticas de um banco de dados, chamada sys.dm_db_stats_properties. Abaixo podemos visualizar a saida de dados.

SELECT
OBJECT_NAME([sp].[object_id]) AS "Table",
[sp].[stats_id] AS "Statistic ID",
[s].[name] AS "Statistic",
[sp].[last_updated] AS "Last Updated",
[sp].[rows],
[sp].[rows_sampled],
[sp].[unfiltered_rows],
[sp].[modification_counter] AS "Modifications"
FROM [sys].[stats] AS [s]
OUTER APPLY sys.dm_db_stats_properties ([s].[object_id],[s].[stats_id]) AS [sp]
WHERE [s].[object_id] = OBJECT_ID(N'dbo.tblCredit');

 

image

Como podemos ver ao realizar a criação dos indices o SQL Server automaticamente cria também a estatistica para as mesmas e com isso é possivel também visualizar que após a criação das estatisticas nenhuma modificação aconteceu e com isso as estatisticas ainda não foram atualizadas.

O SQL Server possui até então três algoritmos utilizados para determinar quando a atualização das estatisticas irão ocorrer. Como em nosso exemplo a tabela já possui 300 registros inseridos o SQL Server levará em conta a seguinte regra para atualizar.

if RowCount < 500 and NumberOfChanges > 500

begin

Call AutoUpdateStats

end

Não que seja assim o algoritimo, mas seria mais ou menos isso. Para quem quiser saber mais sobre quais as regras impostas para o Auto Update Statistics, clique nesse link.

Sabemos entao que o SQL Server só ira disparar o update statistics após 500 registros e também após algum comando que modifique, consulte ou apague dados na tabela.

Vamos então realizar mais uma inserção de dados.

INSERT  INTO tblCredit
VALUES  ( 'Vishal', 'Gandhi', 'VG' ),
        ( 'Itiara', 'Giordani', 'IG' ),
        ( 'Marcelo', 'Mallmann', 'MM' ),
        ( 'Geodan', 'Silva', 'GS' ),
        ( 'Bob', 'Ward', 'BW' ),
        ( 'Erika', 'Madeira', 'EM' ),
        ( 'Frederico', 'Rezende', 'FR' ),
        ( 'Kyle', 'Evans', 'KE' ),
        ( 'Bruna', 'Souza', 'BS' ),
        ( 'Cinara', 'Souza', 'CS' )
GO 30

 

Realizando a mesma consulta para identificar as estatísticas temos o seguinte resultado.

image

Após a segunda inserção de dados, temos então 300 registros já modificados. Vamos realizar a inserção de mais 300 registros e analisar se as estatísticas serão atualizadas.

image

Como podemos ver o update stats não foi invocado, pois não realizamos nenhuma ação que fizesse com que as estatisticas para aquele indice fossem invalidadas. Vamos agora então realizar um simples comando de select que irá utilizar então o indice de nome ix_noncluster1.

SELECT  *
FROM    dbo.tblCredit
WHERE   LastName = 'Freccia'

Ao executar novamente o comando para visualizar as estatisticas temos.

image
Como podemos ver agora a estatística do índice não cluster 1 foi atualizada. Agora vamos ao que quero mostrar sobre o registro dessa informação no transaction log.

SELECT  [Transaction Name] ,
        Operation ,
        [Lock Information] ,
        Description ,
        [Begin Time]
FROM    ::fn_dblog(NULL, NULL)

Avaliando a saida desse comando temos.

image
Como podemos ver o T-LOG nos fornece o momento exato que o comando foi disparado e também algumas informações adicionais como: Tipos de Bloqueio adquiridos, objetos afetados pela ação, entre outras coisas.

Realizando o checkpoint para escrever em disco todas as paginas modificadas e para limpar nossa porção ativa do T-LOG, vamos agora realizar o comando UPDATE STATISTICS.

UPDATE STATISTICS dbo.tblCredit

image
O comando UPDATE STATISTICS também é registrado no T-LOG e dessa vez temos dois registros, que são todas as estatisticas presentes na tabela.

Executando o comando sp_updatestats.

EXEC sys.sp_updatestats

image
Como podemos ver, o comando sp_updatestats não é registrado. Esse é um comportamento que ainda estou pesquisando para entender o porque disso estar acontecendo.

Pessoal, o post serviu apenas para mostrar que temos também mais opções disponiveis para visualizar as ações que o SQL Server executa no momento de atualização das estatisticas.

Quero também agradecer ao Fabiano Amorim(Blog|Twitter) na ajuda prestada para correção do post.

Espero que tenham gostado e até a proxima.

Abraços,
Marcos Freccia

Sobre Marcos Freccia

MVP em SQL Server (Data Plataform) , especialista em SQL Server, e atualmente trabalhando com Microsoft Azure!

Publicado em março 17, 2013, em VirtualPass e marcado como , , . Adicione o link aos favoritos. Deixe um comentário.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: