O que mais o Transaction Log pode fazer por mim ?


Pessoal,

Segunda feira, mais um ótimo dia para falar de SQL Server, todos animados pra semana que segue.

Vamos começar a semana com uma dica que eu usei no ultimo projeto que foi bem legal, principalmente para definir fillfactor de índices.

As vezes precisamos saber quais a tabelas campeãs em comandos DML, dividido por estes comandos.

Ex, quero saber qual a tabela que teve maior insert em determinado período.

Se usarmos a sys.dm_db_index_usage_stats , não temos esta informação, pois os comandos DML são atualizados na coluna user_updates. OU seja ali tenho os inserts, deletes e update.

Uma maneira que podemos obter isso é via transaction log, usando a função fn_dblog.

Neste último projeto eu precisei desta informação para analisar os índices e propor um fillfactor adequado para cada um, pois com esta função também podemos verificar quais as campeãs de page splits.

Separei as tabelas que possuiam maior page split , verifiquei o número de inserts,updates e deletes e aí sim usando a sys.dm_db_index_usage_stats pude ter um IDÉIA de qual fillfactor usar. É assunto pro meu próximo post !!!

Vamos lá

Script para achar tabelas campeãs de page split :

select   allocUnitName,COUNT(*)
from    ::fn_dblog(null, null)
where Operation = N’LOP_DELETE_SPLIT’
group by allocUnitName
order by COUNT(*) desc

 

Agora vamos montar um cenário :

Create table TestLog ( id int identity(1,1) Primary Key Clustered,
                                 Name1 varchar(50),
                                 Name2 varchar(50))
go
Create index idx_testelog_01 on Testlog (name1)
go

Create index idx_testelog_02 on Testlog (name2)

 

Vamos primeiramente limpar o log

BACKUP LOG [DBA] TO  DISK = N’C:\Arquivos de programas\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\dba.trn’ WITH NOFORMAT, INIT,  NAME = N’DBA-Transaction Log  Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
checkpoint

 

Se rodarmos a consulta abaixo veremos que o bixinho ta limpim..limpim..

SELECT    a.allocUnitName,
               COUNT(*)
from    ::fn_dblog(null, null) a
group by a.allocUnitName

 

imagem1

Vamos povoar a tabela

insert into testlog values (‘Name 1′,’name2’)
go 1000

SELECT    a.allocUnitName,
               COUNT(*)
from    ::fn_dblog(null, null) a
group by a.allocUnitName

 

Como podemos ver na figura abaixo, o indice cluster e os dois indices não cluster foram atualizados

imagem2

Agora vamos ver quantos inserts foram dados por índice :

SELECT    a.allocUnitName,
               COUNT(*)
from    ::fn_dblog(null, null) a
where Operation =’LOP_INSERT_ROWS’
and a.allocUnitName like ‘%testlog%’
group by a.allocUnitName

 

imagem3

Podemos perceber que eu fiz um insert de 1000 linhas. O índice cluster (PK__..) e os outros foram atualizados. Cada um com no mínimo 1000 linhas. (na verdade existe uma diferença. Não somente 1000 linhas como podemos ver na figura, algumas operações são adicionadas para controle, como begin tran e comitt tran.)

Agora vamos fazer um Update com base no nome, índice idx_test_log_01 e o Cluster. O idx_test_log_02 não pode ser atualizado !!!!

update testlog set name2 = ‘teste2’
go
SELECT    a.allocUnitName,
        Sum((Case When Operation =’LOP_INSERT_ROWS’ Then 1 End)) [Rows Inserted],
        Sum((Case When Operation =’LOP_MODIFY_ROW’ Then 1 End)) [Rows Updated],
        Sum((Case When Operation =’LOP_DELETE_ROWS’ Then 1 End)) [Rows Deleted]
from    ::fn_dblog(null, null) a
where  a.allocUnitName like ‘%testlog%’
group by a.allocUnitName

 

imagem4

Como podemos ver, ele eliminou 1000 e inseriu 1000 (na verdade tem essa diferença como eu disse acima)no índice correto. Atualizou o Updated no cluster tb.

Agora vamos atualizar o índice idx_testlog_01. O idx_testlog_02 não pode ser mexido.

update testlog set name1 = ‘teste1’
go
SELECT    a.allocUnitName,
        Sum((Case When Operation =’LOP_INSERT_ROWS’ Then 1 End)) [Rows Inserted],
        Sum((Case When Operation =’LOP_MODIFY_ROW’ Then 1 End)) [Rows Updated],
        Sum((Case When Operation =’LOP_DELETE_ROWS’ Then 1 End)) [Rows Deleted]
from    ::fn_dblog(null, null) a
where  a.allocUnitName like ‘%testlog%’
group by a.allocUnitName

 

imagem5

UHUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU !!!!!!

Eliminou as linhas do idx_testlog_01, adicionou as linhas novas (processo de update) e atualizou o cluster somente !!!

Vamos ver filtrando pela coluna [transaction id]

imagem7

Podemos verificar as operações feitas, LOP_MODIFY_ROW no Índice cluster, LOP_DELETE_ROWS e LOP_INSERT_ROWS no índice não cluster !!!!

Agora vamos limpar o log

BACKUP LOG [DBA] TO  DISK = N’C:\Arquivos de programas\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\dba.trn’ WITH NOFORMAT, INIT,  NAME = N’DBA-Transaction Log  Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
checkpoint
go
SELECT    a.allocUnitName,
        Sum((Case When Operation =’LOP_INSERT_ROWS’ Then 1 End)) [Rows Inserted],
        Sum((Case When Operation =’LOP_MODIFY_ROW’ Then 1 End)) [Rows Updated],
        Sum((Case When Operation =’LOP_DELETE_ROWS’ Then 1 End)) [Rows Deleted]
from    ::fn_dblog(null, null) a
where  a.allocUnitName like ‘%testlog%’
group by a.allocUnitName

 

imagem6

O mininu ta limpim..limpim !!!!

Vamos fazer mais um teste, vamos eliminar linhas pra ver oque acontece :

delete from testlog where name1 = ‘name 1’
go
select x.* from
(SELECT    a.allocUnitName,[transaction id],
        Sum((Case When Operation =’LOP_INSERT_ROWS’ Then 1 End)) [Rows Inserted],
        Sum((Case When Operation =’LOP_MODIFY_ROW’ Then 1 End)) [Rows Updated],
        Sum((Case When Operation =’LOP_DELETE_ROWS’ Then 1 End)) [Rows Deleted]
from    ::fn_dblog(null, null) a
where  a.allocUnitName like ‘%testlog%’
group by a.allocUnitName,[transaction id]
)  x where [Rows Deleted]> 10

imagem8

Bom..com base nesses dados podemos dizer quais os índices que tem maior insert, delete e update

SELECT    a.allocUnitName,
        Sum((Case When Operation =’LOP_INSERT_ROWS’ Then 1 End)) [Rows Inserted],
        Sum((Case When Operation =’LOP_MODIFY_ROW’ Then 1 End)) [Rows Updated],
        Sum((Case When Operation =’LOP_DELETE_ROWS’ Then 1 End)) [Rows Deleted]
from    ::fn_dblog(null, null) a
where  a.allocUnitName like ‘%testlog%’
group by a.allocUnitName

Podemos até melhorar a consulta, fazendo que ele somente traga os que foram comitados :

Declare @tableName as Varchar(100);

Set @tableName = ‘testlog’;

SELECT    Sum((Case When Operation =’LOP_INSERT_ROWS’ Then 1 End)) [Rows Inserted],
        Sum((Case When Operation =’LOP_MODIFY_ROW’ Then 1 End)) [Rows Updated],
        Sum((Case When Operation =’LOP_DELETE_ROWS’ Then 1 End)) [Rows Deleted]
from ::fn_dblog(null, null) logdata

inner Join    (    SELECT    [Transaction id],[End Time]
                FROM    ::fn_dblog(null, null) sub1
                Where    Operation=’LOP_COMMIT_XACT’
                And Exists    (    SELECT    [Transaction id]
                                FROM    ::fn_dblog(null, null) sub2
                                Where    allocUnitName + ‘.’ Like ‘%.’ + @tableName + ‘.%’
                                and        sub1.[Transaction id]=sub2.[Transaction id]
                            )
            ) commitedtran
    on    commitedtran.[Transaction id] = logdata.[Transaction id]

 

Podemos jogar para uma tabela e guardar estas informações. Há, mas eu faço backup de log a cada 2 horas.

Para não termos que nos preocupar com LSN inicial  final, podemos colocar um job 15 minutos antes de fazer o backup do log para pegar estas informações, ou filtra pela data da transação comitada ( o que eu acho melhor – este script eu peguei da WEB a um bom tempo – AGRADECIMENTOS AO AUTOR !!!!!).

SELECT    Sum((Case When Operation =’LOP_INSERT_ROWS’ Then 1 End)) [Rows Inserted],
        Sum((Case When Operation =’LOP_MODIFY_ROW’ Then 1 End)) [Rows Updated],
        Sum((Case When Operation =’LOP_DELETE_ROWS’ Then 1 End)) [Rows Deleted]
from ::fn_dblog(null, null) logdata

inner Join    (    SELECT    [Transaction id],[End Time]
                FROM    ::fn_dblog(null, null) sub1
                Where    Operation=’LOP_COMMIT_XACT’
                And Exists    (    SELECT    [Transaction id]
                                FROM    ::fn_dblog(null, null) sub2
                                Where    allocUnitName + ‘.’ Like ‘%.’ + @tableName + ‘.%’
                                and        sub1.[Transaction id]=sub2.[Transaction id]
                            )
            ) commitedtran
    on    commitedtran.[Transaction id] = logdata.[Transaction id]
where    commitedtran.[End Time] between ‘2009-05-10 13:00’ and ‘2009-05-10 13:45’

 
Com base nisso e confrontando com a soma das colunas (use_seeks, user_scans,user_lookups) da sys.dm_db_index_usage_stats
podemos saber se este índice possui mais leitura ou escrita, definindo assim um fillfactor adequado para ele.

select        OBJECT_NAME(A.object_id) TableName ,
            B.Name AS IndexName ,
            sum(A.user_seeks +  A.user_scans +  A.user_lookups) Acessos
from    sys.dm_db_index_usage_stats A

inner join sys.indexes B
ON a.object_id = b.object_id
AND a.index_id = b.index_id

where A.OBJECT_ID = object_id(‘testlog’)
group by  OBJECT_NAME(A.object_id),B.Name

 

Eu usei isto neste projeto anterior, e estarei postando como cheguei em um fiilfactor adequado e em uma reindexação customizada para cada índice no próximo artigo !!!

Mais um pouco de leitura sobre :

http://www.sqlskills.com/BLOGS/PAUL/post/Finding-out-who-dropped-a-table-using-the-transaction-log.aspx

 

Abraços Galerinha !!!!!!!

E BOA Semana !!!!

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in SQL SERVER EM GERAL. Bookmark the permalink.

2 Responses to O que mais o Transaction Log pode fazer por mim ?

  1. Pingback: Como monitorar o Page Split de um Índice? | Fabrício Lima

  2. Kleber Rafael says:

    fala Juninho…

    baita artigo bacan…vou usá-lo!

    abraços,

    Klebão.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s