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

sexta-feira, 10 de julho de 2015

Instalação e novas funcionalidades do SQL Server 2016 CTP 2.1


A Microsoft liberou o download da versão CTP (Community Technology Preview) 2.1 do novo SQL Server 2016, link abaixo:

Neste post vou falar do processo de instalação e das novas funcionalidades, que serão exploradas em detalhes nos próximos posts.

Instalando SQL Server 2016 CTP 2.1

Existem várias publicações na internet sobre a instalação do SQL Server passo-a-passo, vou enfatizar as diferenças das versões anteriores.

Assim como no SQL Server 2014, a ferramenta para desenvolvimento de BI (Business Intelligence) não vem na mídia de instalação, tem que ser feito Download a parte, link em destaque na imagem abaixo.



Na tela de seleção de funcionalidades (Feature Selection) aparece uma opção nova “PolyBase Query Service for External Data”.



Selecionando a opção em destaque na figura acima, serão instalados dois serviços:
- SQL Server PolyBase Data Movement
- SQL Server PolyBase Engine

Estes serviços fazem a integração com Hadoop Cluster, para acesso a Big Data, que será assunto para de um próximo post.  A seleção do “PolyBase” gera o erro abaixo na próxima tela do setup:


Para resolver precisamos instalar o Java no servidor, fazer Download através do link abaixo:

Depois de instalar o Java, basta clicar em “Back” e depois seguir com a instalação que o erro desaparece.

  
Novas funcionalidades do SQL Server 2016 CTP 2.1

- Query Store
Histórico de planos de execução com informações de desempenho, ficando fácil identificar consultas que recentemente apresentaram queda de desempenho, devido à alteração no plano de execução.

- Polybase
Conector para o Hadoop e Azure Blob Storage, tornando possível acesso a Big Data, por exemplo, em uma carga de dados com Integration Service.

- Stretch Database
Stretch é alongamento em inglês, representando a funcionalidade de transferir partes do seu banco de dados para nuvem, no SQL Azure.  O otimizador de consultas identifica que linhas estão no banco local e quais estão no Azure, dividindo a carga de trabalho.  Um exemplo de uso seria armazenar os dados recentes no banco local e o histórico na nuvem.

- Suporte a linguagem JSON
Java Script Object Notation (JSON) é usada para consultas diretas ao Hadoop.

- Always Encrypted
Criptografia no armazenamento e na transmissão dos dados com configuração mais simples.

- Melhorias no In-Memory OLTP
O SQL Server 2014 trouxe como novidade a possibilidade de manter tabelas em memória, porém com algumas limitações.  O SQL Server 2016 retirou algumas destas limitações.

- Segurança de linha
Row-level Security trás a possibilidade de definir que linhas cada usuário pode acessar.

- Melhorias no AlwaysOn
Mais de 3 replicas, suporte ao SSIS, Load balancing, são algumas melhorias nesta solução de alta disponibilidade.

Veja mais em:

Até o próximo post.

Saudações Tricolores,
Landry