Arquivo

Archive for the ‘Casos do Dia a Dia’ Category

Casos do Dia a Dia – Diminuíndo um problema de memória no SQL Server

25 de dezembro de 2010 9 comentários

 

Esse post foi transferido para o novo domínio do Blog. Clique aqui para acessá-lo.

Anúncios

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



Casos do Dia a Dia – Exclusão de um índice grande e pouco utilizado

30 de novembro de 2010 5 comentários

Fala pessoal, 

Algum tempo atrás, compartilhei uma experiência que tive no Blog do Fabrício Catae (Blog|Twitter), mas também resolvi deixar registrado por aqui.

Muitos de vocês já utilizaram a dmv sys.dm_db_index_usage_stats para verificar a utilização e atualização dos índices de uma tabela. Também sabemos que essa dmv tem seus dados reiniciados quando o serviço do SQL Server é reiniciado. Assim, como os servidores do meu ambiente de banco de dados possuem uma atualização mensal de segurança, os servidores são reiniciados mensalmente e os dados dessa dmv seriam perdidos. Ou seja, eu só teria essas valiosas informações sobre os índices durante o período de um mês.

Para resolver esse problema, criei uma tabela que armazena diariamente a utilização dos índices. Com isso, posso analisar durante um período muito grande, a utilização dos meus índices antes de excluí-los. Eu já possuo mais de 1 ano de baseline já que o espaço ocupado por essas informações é pequeno.

Para quem quiser possuir um histórico dessas informações, o script abaixo cria uma tabela de histórico e insere as informações de utilização dos índices nessa tabela.

CREATE TABLE [dbo].[Historico_Utilizacao_Indices](
                               [Id_Historico_Utilizacao_Indices] [int] IDENTITY(1,1) NOT NULL,
                               [Dt_Historico] [datetime] NULL,
                               [Nm_Servidor] [varchar](30) NULL,
                               [Nm_Database] [varchar](30) NULL,
                               [Nm_Tabela] [varchar](50) NULL,
                               [Nm_Indice] [varchar](50) NULL,
                               [User_Seeks] [int] NULL,
                               [User_Scans] [int] NULL,
                               [User_Lookups] [int] NULL,
                               [User_Updates] [int] NULL,
                               [Ultimo_Acesso] [datetime] NULL )

INSERT INTO Historico_Utilizacao_Indices(Dt_Historico, Nm_Servidor, Nm_Database, Nm_Tabela, Nm_Indice,  User_Seeks, User_Scans,User_Lookups, User_Updates, Ultimo_acesso)
SELECT getdate(), @@SERVERNAME, db_name(db_Id()), o.Name, i.name, s.user_seeks,s.user_scans,s.user_lookups, s.user_Updates,  isnull(s.last_user_seek,isnull(s.last_user_scan,s.last_User_Lookup)) Ultimo_acesso
FROM sys.dm_db_index_usage_stats s
             join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id
             join sys.sysobjects o on i.object_id = o.id
WHERE s.database_id = db_id()
ORDER BY o.Name, i.name, s.index_id

Para armazenar as informações, criei um job que roda essa query de INSERT para cada database que eu defini guardar os históricos.

Assim, eu utilizo essa tabela de histórico para analisar uma possível exclusão dos índices que são pouco utilizados ou que não são utilizados. Agora, compartilhando a experiência que tive, eu possuo uma tabela com muitas consultas e alterações que armazena 50 milhões de registros. Nessa tabela, tenho um índice em um campo chamado Fl_Situacao que pode possuir os valores 0, 1, 2, 3 ou 4.

Sempre acompanhei esse índice e verifiquei que tinha algumas utilizações somente no inicio do mês. Um certo dia, resolvi excluir esse índice seguindo o raciocínio de que o índice era pouco seletivo, a tabela é muito grande e o índice era pouco utilizado, não valendo a pena o custo de manutenção do mesmo. Após excluir o índice, acompanhando meu trace com as querys que demoram mais de 3 segundos, verifiquei que nenhuma query apresentou problema de lentidão.

Show de bola, diminuí uma operação de manutenção de um índice em uma tabela muito utilizada.

Entretanto, no inicio do mês, existia uma query com uma condição “where Fl_Situacao = 2” dentre outras restrições. Quando essa query rodou sem o índice que eu excluí, a mesma fez um clustered index scan nessa tabela, me causando um grande problema de lentidão no banco de dados. Isso aconteceu pois, dos 50 milhões de registros existentes na tabela, apenas 1.000 registros possuíam o campo Fl_Situacao = 2, o que tornava o índice existente nessa coluna extremamente eficiente para essa query. 

Resultado, como não dava para alterar a consulta, tive que recriar o índice na mesma noite.

Mais uma vez eu digo, vivendo e aprendendo!!! Meu maior aprendizado está no meu dia a dia de trabalho.

Abraços, 

Fabrício França Lima

Casos do Dia a Dia: Exclusão de campo Text não libera espaço em disco?

15 de outubro de 2010 4 comentários

Fala pessoal,

Compartilhando mais uma experiência que tive no meu dia a dia de trabalho, após a verificação com a equipe de desenvolvimento da possibilidade de exclusão de 2 campos do tipo text de uma tabela muito utilizada, fui com toda empolgação na minha base de testes verificar o quanto de espaço em disco eu ganharia. Para isso, rodei a sp_spaceused para essa tabela, exclui os 2 campos text e rodei novamente a sp_spaceused.

Para minha surpresa, o espaço em disco utilizado por essa tabela continuava o mesmo. Ah não! É impossível! Como que esses campos não ocupam nenhum espaço no banco?

Vamos visualizar essa situação. Com o script abaixo criamos e populamos uma tabela comum:

SET NOCOUNT ON

CREATE TABLE Anotacao(
Id_Anotacao INT IDENTITY(1,1),
Dt_Anotacao DATETIME DEFAULT(getdate()),
Ds_Anotacao TEXT,
CONSTRAINT PK_Anotacao PRIMARY KEY(Id_Anotacao)) — Índice clustered
GO
INSERT INTO Anotacao(Ds_Anotacao)
SELECT REPLICATE(‘A’,4000)
GO 10000

Em seguida, executando o comando:

exec sp_spaceused Anotacao

 Temos o seguinte resultado:

spaceused_antes

Como podemos ver, a tabela possui mais de 40 MB de dados.

Agora excluímos o campo com o comando abaixo:

ALTER TABLE Anotacao
DROP COLUMN Ds_Anotacao 

Mais uma vez, executando a sp_spaceused temos o resultado abaixo:

exec sp_spaceused Anotacao

spaceused_antes

Como assim? O resultado não mudou?

Nesse momento abri uma thread no fórum do technet e após trocar alguns posts com o Gustavo Aguiar (Blog), obtive uma resposta muito esclarecedora:

“Olá Fabrício,

Antes da exclusão da coluna, o espaço estava alocado e os dados organizados. Se você excluir a coluna, para que o espaço seja imediatamente liberado, o SQL Server teria que reorganizar tudo. Para reorganizar, fatalmente haveria um trabalho de IO envolvido bem como uma possível indisponibilidade. Como o SQL Server não sabe a criticidade da tabela, ele opta por não reorganizar e manter o espaço alocado mesmo após a exclusão da coluna.

O espaço será liberado assim que você promover um REINDEX do índice clustered. Se a tabela não possuir um, será necessário criar e depois removê-lo (a menos que seja SQL Server 2008).”

Pronto, acredito que agora tudo já esteja esclarecido para vocês. Vamos testar?

Basta dar um REBUILD no índice:

ALTER INDEX PK_Anotacao ON Anotacao REBUILD

Agora, executando a sp_spaceused novamente, temos o seguinte resultado:

spaceused_depois

Agora podemos visualizar o espaço que ganhamos com a exclusão do campo. O tamanho da tabela diminuiu 40 MB.

Bom, depois da ajuda do gustavo, verifiquei que no meu caso ganharia 10GB de espaço em disco com a exclusão dos meus 2 campos text. =)

Abraços,

Fabrício França Lima

Casos do Dia a Dia: Database em modo Suspect

8 de outubro de 2010 9 comentários

 

Fala Pessoal,

Gostaria de compartilhar com vocês uma experiência que tive no ambiente de banco de dados que administro. Realizando um boot em um dos meus servidores SQL Server devido as atualizações de segurança do windows, uma de minhas databases subiu em modo Suspect. Como essa database é restaurada diariamente do servidor de produção para esse servidor D-1 e é utilizada por poucos relatórios, tive tempo de pesquisar se havia algum modo de recuperá-la sem precisar voltar um backup da mesma.

Mas Fabrício, como vou saber se alguma de minhas databases está em modo Suspect?

R: Além de ser possível visualizar no próprio Object Explore do Management Studio, rodando a query abaixo também podemos verificar se existe alguma database suspect em nosso ambiente:

SELECT NAME,STATE_DESC FROM SYS.DATABASES
WHERE STATE_DESC=’SUSPECT’

Após algumas buscas, encontrei o seguinte artigo onde é ensinado um passo a passo de como voltar uma database em modo suspect: How to repair a Suspect Database in SQL Server.

Esse artigo identifica 5 possíveis motivos para uma database ficar como Suspect, são eles:

1. Database could have been corrupted.

2. There is not enough space available for the SQL Server to recover the database during startup.

3. Database cannot be opened due to inaccessible files or insufficient memory or disk space.

4. Database files are being held by operating system, third party backup software etc.

5. Unexpected SQL Server Shutdown

Tudo indica que comigo aconteceu o problema de número 5. E para resolve-lo, apenas segui o passo a passo desse artigo.

Com a database em estado suspect, não era possível verificar as propriedades e enchergar as tabelas dessa database no Object Explore do Management Studio, o que já deixa os usuários da database bem desesperados com medo de terem perdido os dados.

Para iniciar o processo de recuperação, executei o comando:

ALTER DATABASE NOME_DATABASE SET EMERGENCY

Nesse momento minha database ja estava em modo de emergencia e já era possível acessar as tabelas. Nessa hora já bate um aquele alívio. UFA, minhas tabelas estão acessíveis.

Entretanto, quando uma database está em modo de emergencia ela se torna READ_ONLY e o acesso fica limitado a membros da server role sysadmin. Para deixa minha database online novamente, executei o comando:

ALTER DATABASE NOME_DATABASE SET ONLINE

Minha database ficou totalmente acessível e meus usuários puderam utilizá-la normalmente.

Após deixar a database online, executei o comando DBCC CHECKDB para verificar se existia alguma corrupção nessa base. O resultado foi “found 0 errors and repaired 0 errors”.

Com isso, não precisei restaurar um backup da minha database para poder recuperá-la. Espero que possa ajudar alguém que passe por esse problema. Caso você tenha alguma experiência com uma database Suspect, deixe um comentário para compartilhar sua experiência com outras pessoas.

Abraços,

Fabrício França Lima