quarta-feira, 16 de setembro de 2015

SQL Server 2014 SP1 BUG - Log Shipping em modo StandBy corrompe Transaction Log



Recentemente passei por um problema sério em um cliente, onde o Transaction Log corrompia intermitentemente no servidor secundário.  Isso me obrigava a refazer a sincronia inicial, isto é, executar um Backup FULL na produção, copiar pela rede (esse cliente tem bancos de 300GB a 700GB) e Restore no servidor secundário.

Recentemente a Microsoft liberou um HotFix para resolver este problema, relatado no artigo KB 2987585:


O problema é específico para Log Shipping que utiliza a opção Standby, em volumes formatados com modo avançado.  Quem quiser entender o que significa “formatar com modo avançado”, o artigo abaixo explica muito bem:


Para fazer download do HotFix, que está no pacote “Cumulative Update 2” do SQL Server 2014 SP1, basta clicar no link abaixo:


Até o próximo post.

Saudações Tricolores,
Landry

terça-feira, 28 de julho de 2015

Entendendo o crescimento do Transaction Log


O crescimento do Transaction Log é talvez um dos mais frequentes problemas que um Administrador de Banco de Dados pode enfrentar no dia a dia, chegando até encher por completo o disco ou esgotar a capacidade de armazenamento do arquivo.

A coluna log_reuse_wait_desc da visão de sistema sys.databases nos dá uma dica do status do Transaction Log e como reduzir sua ocupação interna.  Vamos simular o preenchimento completo do Transaction Log e observar o conteúdo da coluna log_reuse_wait_desc.

O script abaixo cria um banco de dados com Recovery Model FULL para teste:

IF exists (SELECT * FROM sys.databases WHERE name = 'TesteLogBD')
   DROP DATABASE TesteLogBD
go
CREATE DATABASE TesteLogBD
ON (NAME = TesteLogBD_data,FILENAME = 'D:\Bancos\TesteLogBD.mdf',
SIZE = 5MB,FILEGROWTH = 1MB)
LOG ON (NAME = TesteLogBD_log,FILENAME = 'D:\Bancos\TesteLogBD_log.ldf',
SIZE = 1MB,FILEGROWTH = 0)
go

Repare que o Transaction Log foi gerado com 1MB sem crescimento automático (FILEGROWTH = 0).

Em seguida vamos fazer um Backup Full do banco de dados:

BACKUP DATABASE TesteLogBD to
DISK = 'D:\TEMP\TesteLogBD.bak' WITH compression,init

Utilizando o SELECT abaixo na sys.databases observamos que a coluna log_reuse_wait_desc está com valor NOTHING, indicando que não tem nada a fazer para reutilizar o espaço interno do Transaction Log pois está vazio.

SELECT log_reuse_wait_desc FROM sys.databases
WHERE [name] = 'TesteLogBD'

Já DBCC mostra que a ocupação interna do Transaction Log está em 38%:

use TesteLogBD
go
DBCC SQLPERF (LOGSPACE)

Para provocar o preenchimento completo do Transaction Log, vou criar uma tabela e incluir algumas linhas:

CREATE TABLE TesteLogBD.dbo.EncheLog (c1 INT, c2 CHAR(3000))
go
WHILE (1=1) BEGIN
   INSERT INTO TesteLogBD.dbo.EncheLog VALUES (1, 'a')
END

Msg 9002, Level 17, State 2, Line 24
The transaction log for database 'TesteLogBD' is full due to 'LOG_BACKUP'.

O erro 9002 ocorre quando o SQL Server não consegue escrever no Transaction Log pois este está cheio, inviabilizando a execução do comando.  Vamos verificar agora o valor da coluna log_reuse_wait_desc da visão de sistema sys.databases:

SELECT log_reuse_wait_desc FROM sys.databases
WHERE [name] = 'TesteLogBD'

O valor de retorno LOG_BACKUP indica que para liberar espaço no Transaction Log basta executar um Backup do Log.  O Backup do Log salva todo conteúdo do Transaction Log num arquivo e em seguida trunca o Transaction Log, liberando espaço interno.  O DBCC SQLPERF mostra o Transaction Log com 100% de ocupação:

use TesteLogBD
go
DBCC SQLPERF (LOGSPACE)

Executando um Backup do Log liberamos espaço no Transaction Log, reduzindo a ocupação para 57%, e a coluna log_reuse_wait_desc retorna para o valor NOTHING:

BACKUP LOG TesteLogBD to DISK = 'D:\TEMP\TesteLogBD.trn'
WITH compression,noinit

SELECT log_reuse_wait_desc FROM sys.databases
WHERE [name] = 'TesteLogBD'

use TesteLogBD
go
DBCC SQLPERF (LOGSPACE)


Transação em aberto inviabiliza limpeza do Transaction Log

Quando uma transação está em aberto, o Backup do Log não consegue truncar o Transaction Log, e o conteúdo da coluna log_reuse_wait_desc fica com o valor ACTIVE_TRANSACTION.

DECLARE @i int
SET @i = 1
begin transaction
WHILE (@i < 100) BEGIN
   INSERT INTO TesteLogBD.dbo.EncheLog VALUES (1, 'a')
   SET @i += 1
END

SELECT log_reuse_wait_desc FROM sys.databases
WHERE [name] = 'TesteLogBD'

use TesteLogBD
go
DBCC SQLPERF (LOGSPACE)

Mesmo fazendo Backup do Log o resultado do SELECT na sys.databases e o DBCC SQLPERF, permanece inalterado!  Isto ocorre porque o SQL Server não limpa entradas no Transaction Log de transações em aberto!  A solução para o problema requer identificar a transação em aberto e finaliza-la.  O SELECT abaixo retorna a relação de conexões com transação em aberto:

SELECT * FROM sys.dm_exec_requests
WHERE open_transaction_count > 0

O commando KILL finaliza a transação com ROLLBACK, em seguida podemos executar um Backup do Log  para liberar espaço no Transaction Log.

Até o próximo post.

Saudações Tricolores,
Landry

quinta-feira, 16 de julho de 2015

Buffer Pool Extension (BPE)


Antes de escrever sobre Buffer Pool Extension, nova funcionalidade do SQL Server 2014, preciso fornecer o conhecimento necessário para percebermos os benefícios e cenários indicados de uso deste novo recurso.

Como o SQL Server acessa os dados

O SQL Server sempre lê os dados da memória.  Quando o usuário executa um comando SELECT o SQL Server verifica se a informação já se encontra na memória, se não estiver acessa o disco e carrega para memória.  Neste momento, as páginas na memória estão idênticas as páginas no disco recebendo a denominação de “Clean Pages” (Páginas Limpas).  Quando o usuário executa um comando de alteração dos dados no banco, o mesmo processo de levar as páginas para memória ocorre, sendo alteradas APENAS na memória.  Estas páginas alteradas na memória recebem a denominação de Dirty Pages (Páginas Sujas). 

De tempos em tempos um processo chamado de “Checkpoint” se encarrega de atualizar as páginas no disco a partir da Páginas Sujas (Dirty Pages) da memória.

Fica clara a importância de ter uma boa quantidade de memória para o SQL Server, não é?  Quando temos menos memória que o necessário para manter as páginas mais acessadas em memória, o SQL Server é obrigado a acessar constantemente o disco para atender as requisições dos usuários, com consequente perda de desempenho.

Buffer Pool Extension (BPE)

Já que memória é tão importante para o SQL Server, e com o surgimento dos discos SSD (Solid-state drive), o SQL Server 2014 nos dá a possibilidade de expandir o Buffer de memória para os discos SSD.  Claro que aumentar a memória RAM fornece um resultado muito melhor, mas estender o Buffer para discos SSD é melhor que trabalhar com pouca RAM!  Veja gráfico comparativo abaixo:


Retirado do artigo em:

Existem algumas restrições quanto ao uso do BPE:
·         Apenas Paginas Limpas (Clean Pages) podem estar no BPE, Páginas Sujas só na memória RAM.
·         BPE só SQL Server Enterprise, Standard ou Business Intelligence, todos 64 bits.
·         Necessária permissão ALTER SERVER STATE para habilitar BPE.
·         Limite máximo de 32 vezes o valor do valor de MaxServerMemory, sendo o indicado pela Microsoft entre 4 a 8 vezes.

Habilitando BPE

Para habilitar o Buffer Pool Extension (BPE) basta utilizar o script abaixo, lembrando de habilitar somente em volumes SSD.

USE Master
go
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME = 'S:\MyCache.bpe', SIZE = 10GB)

Para desabilitar BPE:

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF

As visões de sistema abaixo retornam informações do Buffer Pool Extension, quando habilitado:

SELECT * FROM sys.dm_os_buffer_pool_extension_configuration

SELECT * FROM sys.dm_os_buffer_descriptors

Até o próximo post.

Saudações Tricolores,
Landry