Arquivo

Posts Tagged ‘Internals’

Casos do Dia a Dia – Você sabia que um arquivo de Log do SQL Server se fragmenta?

19 de dezembro de 2010 2 comentários

Fala Pessoal,

Vou ser sincero com vocês, até pouco tempo eu não tinha a menor idéia que um arquivo de log do SQL Server se fragmentava. Nunca tinha lido sobre o assunto. Entretanto, para isso que servem os mais 100 Blogs de SQL Server cadastrados no meu google reader e os vários profissionais SQL Server que sigo no twitter e que também divulgam dicas de excelentes artigos, whitepapers e etc.

Pouco tempo atrás, alguém “twitou” esse excelente artigo 8 Steps to better Transaction Log throughput.

Nesse artigo Kimberly L. Tripp (Blog|Twitter) da uma série de dicas para serem aplicadas nos arquivos de log do SQL Server.  Uma dessas dicas (a de número oito) é para checar e corrigir a fragmentação interna desses arquivos. Ela ainda disse que essa dica resolveu um problema de um cliente na Turquia.

Caso o seu arquivo de log tenha sofrido muitos autogrowths, ele pode ficar internamente fragmentado. O arquivo de log do SQL Server é dividido em pequenos pedaços chamados VLF(Virtual Log Files) que crescem a medida que seu arquivo de log cresce. Segundo Kimberly, geralmente, a maioria dos arquivos de logs devem ter somente entre 20 e 30 VLF. 50 ainda é aceitável dependendo do tamanho do seu arquivo de log.

Um número excessivo de VLF pode causar um impacto negativo em todas as atividades do transaction log e além disso, ainda pode ocorrer uma degradação da performance enquanto um backup do log está sendo executado. Para conferir quantos VLF você tem em uma database, basta verificar o número de linhas retornadas pelo comando DBCC LOGINFO conforme a figura abaixo:

Nesse caso, o arquivo de log dessa database possui 4 VLFs.

Seguindo o procedimento do artigo, realizei os seguintes passos para desfragmentar o arquivo de log:

 1 – Esperei por um período de pouco movimento e limpei o transaction log realizando um backup do mesmo. Caso você utilize o recovery model SIMPLE, você não precisa realizar um backup do log, ao invés disso, você limpará o transaction log rodando um CHECKPOINT.

            BACKUP LOG databasename TO devicename

2 – Realizei um Shrink no log para que ele ficasse com o menor tamanho possível (por isso que ele é limpo no passo 1).

            DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)

3 – Alterei o arquivo de log da database para um tamanho apropriado onde ele necessite crescer apenas em alguns casos raros.

            ALTER DATABASE databasename
            MODIFY FILE
            ( NAME = transactionloglogicalfilename,
              SIZE = newtotalsize ) 

No meu ambiente eu possuía uma database com 275 VLF e as maiores estavam todas com mais de 150 VLF. Após realizar os passos acima, todas as databases estão com menos de 20 VLF, sendo que dentre elas, mantenho arquivos de log com 4 GB devido a algumas operações e manutenções que ocorrem durante a madrugada.

Com relação a performance, em um dos meus servidores, faço um backup do log a cada 7 minutos para minhas 5 principais databases, com isso, possuo um grande número de backups do log. Como eu monitoro toda query que demora mais de 3 segundos, constantemente a query que fazia backup do log demorava para ser executada e era armazenada no meu log. Após a realização dos passos descritos acima, é muito raro um backup do log  entrar nesse trace. Essa melhora foi bem perceptível no meu ambiente.

Então, cabe a vocês verificarem a quantidade de VLF de suas databases pois é um procedimento bem simples.

Abraços,

Fabrício França Lima


Anúncios

Eu preciso dar um sp_recompile em uma procedure após alterá-la?

25 de julho de 2010 4 comentários

Fala Pessoal,

Você já houviu alguém dizer que quando se altera uma procedure devemos dar um sp_recompile nessa SP?

Até essa semana eu acreditava realmente que isso era necessário. O argumento que eu houvi quando aprendi e que eu mesmo respondia para alguém quando era questionado sobre o motivo de executar a sp_recompile em uma procedure após alterá-la era o seguinte:

“Quando alteramos uma procedure devemos executar o sp_recompile nessa procedure para forçarmos uma recompilação do seu plano de execução que pode ter sido alterado com a manutenção que foi realizada nessa procedure.”

Realmente, faz todo o sentido, pois ao alterarmos uma procedure podemos incluir ou excluir uma query, o que com certeza altera o plano de execução dessa procedure. Entretanto, realizando a leitura do meu livro SQL Server 2008 Internals, eu li que a execução do comando ALTER PROCEDURE exclui o plano dessa procedure do cache. Na mesma hora veio a voz do Silvio Santos na minha cabeça: “Ma ma eu só acreditoooo… veeeendoo, hi hiiii”.Então subi minha Vm para realizar meus testes.

Inicialmente, criei uma tabela e populei a mesma com 1000 registros.

CREATE TABLE Venda(
      Id_Venda INT IDENTITY(1,1),
      Dt_Venda DATETIME,
      Vl_Venda NUMERIC(15,2)
      )

GO
INSERT INTO Venda(Dt_Venda,Vl_Venda)
SELECT GETDATE(),CAST(1000000*RAND() AS INT)%1000
GO 1000
GO
CREATE CLUSTERED INDEX SK01_Venda ON Venda(Id_Venda)
CREATE NONCLUSTERED INDEX SK02_Venda ON Venda(Vl_Venda)include(Dt_Venda)

Em seguida criei duas procedures para realizar os testes.

CREATE PROCEDURE stpConsulta_Vendas @Vl_Venda NUMERIC(15,2)
AS
SELECT Id_Venda,Dt_Venda,Vl_Venda
FROM Venda
WHERE Vl_Venda >= @Vl_Venda

GO

CREATE PROCEDURE stpConsulta_Vendas_2 @Vl_Venda NUMERIC(15,2)
AS
EXEC stpConsulta_Vendas @Vl_Venda

Com a query abaixo podemos ver quais os planos de execução estão em cache. Chamarei essa query de QUERY A para referenciar as execuções posteriores da mesma.

SELECT Text, Plan_Handle, Size_in_bytes, Usecounts
FROM sys.dm_Exec_cached_plans AS cp
      CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE Text LIKE '%stpConsulta_Vendas%' -- para visualizar apenas o plano dessas procedures
      AND Text NOT LIKE '%dm_Exec_cached_plans%' --para não aparecer essa propria query
      AND Objtype = 'Proc' --Procedures
ORDER BY Size_in_bytes DESC

Neste momento, essa query não retorna nenhum registro. Entretanto, após a execução do script abaixo.

EXEC dbo.stpConsulta_Vendas 900.00
GO
EXEC dbo.stpConsulta_Vendas 800.50
GO
EXEC dbo.stpConsulta_Vendas_2 950.46
GO
EXEC dbo.stpConsulta_Vendas_2 990.25

Executando a QUERY A novamente temos o seguinte resultado.

FIGURA 1

Podemos claramente verificar no valor da coluna Usecounts que o mesmo plano de execução da stpConsulta_Vendas foi utilizado 4 vezes, sendo duas chamadas diretas e duas chamadas de dentro da stpConsulta_Vendas_2. Já o plano de execução da stpConsulta_Vendas_2 foi utilizado as duas vezes que foi chamado explicitamente.

Agora que é a hora da verdade, vamos alterar nossa procedure e ver se o planos de execução será eliminado do cache.

ALTER PROCEDURE stpConsulta_Vendas @Vl_Venda NUMERIC(15,2)
AS
SELECT Id_Venda,Dt_Venda,Vl_Venda
FROM Venda
WHERE Vl_Venda >= @Vl_Venda

Executando a Query A, temos o seguinte resultado.

FIGURA 2

Logo, o plano de execução da procedure stpConsulta_Vendas que havia sido executado 4 vezes foi removido do cache. Com isso, confirmamos o fato de que quando alteramos uma procedure, o plano de execução dessa procedure já é excluído do cache automaticamente, não necessitando assim de executarmos o comando sp_recompile para essa procedure.

Aproveitando, vamos executar o comando sp_recompile para a stoConsulta_Vendas_2 para visualizarmos que o plano de execução dessa procedure também será excluído do cache.

exec sp_recompile stpConsulta_Vendas_2

Executando novamente a QUERY A é possível identificar que o plano de execução dessa procedure também foi retirado do cache.

Em suma, a partir de agora nunca mais executarei um sp_recompile após alterar uma procedure. Vivendo e aprendendo.

Abraços,

Fabrício França Lima

Meus novos livros sobre SQL Server

20 de abril de 2010 2 comentários

Olá pessoal,

Comprei 2 livros de SQL Server na Amazon no dia 30/01/2010 com a previsão de entrega para o dia 15/03/2010. Depois de 79 longos dias de espera, ontem (19/04/2010) recebi uma correspondência dos correios dizendo para buscá-los. Já estava considerando esses livros como perdidos.

Os livros que comprei são os seguintes:

  • SQL Server MVP Deep Dives: Este livro foi escrito por 53 MVPs e possui uma coleção de técnicas e melhores práticas para administradores e desenvolvedores de SQL Server. Além disso, 100% do lucro dos autores vai para a War Child International.
  • Microsoft SQL Server 2008 Internals: Livro da Kelen Delaney onde espero descobrir como o SQL Server realiza cada tarefa internamente.

Eles foram muito indicados por especialistas no assunto, espero aumentar muito meu conhecimento com a leitura desses livros. Tenho certeza que eles me ajudarão em meus futuros posts.

                            

 

Abraços,

Fabrício França Lima

MCITP – Database Administrator

http://fabriciodba.spaces.live.com/

http://twitter.com/fabriciodba

Categorias:Livros Tags:, , ,