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

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

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

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

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

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

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]

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

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

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

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 these ads

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

Pingback: Como monitorar o Page Split de um Índice? | Fabrício Lima
Kleber Rafael says:

25 de November de 2011 at 17:45

fala Juninho…

baita artigo bacan…vou usá-lo!

abraços,

Klebão.

M	T	W	T	F	S	S
« Sep				Nov »
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31

$hell Your Experience !!!

As aventuras de um DBA usando o Poder do $hell

O que mais o Transaction Log pode fazer por mim ?

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.

Like this:

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

Leave a Reply Cancel reply

Follow “$hell Your Experience !!!”