Com apenas o Recovery Model Bulk-Logged sua importação é minimamente registrada em log?

Olá Pessoal,

Hoje vou falar de uma situação que talvez muitas pessoas tenham dúvidas, ou alguma vez passou pela sua cabeça se apenas alterando o Recovery Model do Banco de dados para Bulk Logged fosse suficiente para que uma carga em massa de dados seja minimamente registrada em log.

Na verdade existem alguns pre-requisitos para serem satisfeitos para garantir esse objetivo, tais como a tabela de destino é um HEAP ? ou tem um Índice Clustered ? tem algum índice NonClustered ? a tabela estava vazia durante a importação? etc. Para visualizarmos de maneira mais clara essas situações iremos utilizar a tabela abaixo:

Observe que existem alguns fatores na estrutura da tabela de destino que devem ser avaliados antes da importação dos dados. De acordo com a tabela acima em uma tabela vazia independente desta tiver índices ou não a importação será sempre minimamente registrada em log e caso esta já contenha dados e tiver índices a importação não será minimamente registrada em log, ou seja, todos os registros serão inseridos no log de transação por completo (Exceto para o índex NonClustered de qual será minimamente registrada em log a tabela de dados e um registro completo no index).

Para demonstrar essas situações vou criar um banco de dados de teste chamado DB_BULK criar uma tabela e inserir alguns registros para povoa-la, após isso vamos exportar os dados para um arquivo de texto simples e recriar o banco de dados e a tabela para fazermos a importação alterando o Recovery Model do banco de dados para Bulk Logged e verificar se o Transaction Log irá ser preenchido ou não após a importação validando se a importação foi minimamente registrada em log ou não. Para referenciar os scripts utilizados irei colocar um número para identificar cada bloco de script.

—Script1: Criando o banco de dados DB_Bulk especificando como tamanho inicial do Transaction Log 200MB
–crecimento automatico de 100MB.
CREATE DATABASE DB_Bulk
ON
(
    NAME = ‘Bulk_DATA’,
    FILENAME = ‘C:BulkBulk_DATA.MDF’,
    SIZE = 100MB,
    FILEGROWTH = 100MB
)
LOG ON
(
    NAME = ‘Bulk_LOG’,
    FILENAME = ‘C:BulkBulk_LOG.LDF’,
    SIZE = 200MB,
    FILEGROWTH = 100MB
)
GO

–Backup Full para o banco de dados sair do modo "Pseudo Simple"
BACKUP DATABASE DB_Bulk TO DISK = ‘C:BACKUPDB_Bulk.BAK’ WITH INIT

–Criando a tabela de teste e gerando os dados.
USE DB_Bulk

CREATE TABLE TB_Bulk
(
     COD INT IDENTITY(1,1)
    ,NOME VARCHAR(100)
    ,DATA DATETIME
    ,ENDERECO VARCHAR(100)
)
–Inserindo 500 Mil linhas na tabela
INSERT INTO TB_Bulk VALUES (‘A’,GETDATE() – 10,‘B’)
GO 500000

–Criando os indices Clustered e NonClustered
CREATE CLUSTERED INDEX [IND_CL] ON TB_Bulk(COD ASC)
CREATE INDEX [IND_NC] ON TB_Bulk(NOME ASC)

Com a tabela criada e os dados gerados vamos exportar os dados para um arquivo de texto utilizando o aplicativo BCP, conforme o script abaixo.

Script 2:

C:Program FilesMicrosoft SQL Server90ToolsBinn> BCP DB_Bulk.dbo.TB_Bulk out IMPORT.txt -t, -U sa –P (senha) -c

Arquivo IMPORT.txt:

 

 

 

 

Ok, com nosso arquivo de texto gerado com os dados para a importação vamos excluir o banco de dados e executar novamente o Script1 para recriar o banco de dados e a tabela de teste. Execute todo o Script 1 exceto a instrução INSERT para povoar os dados, iremos utilizar o arquivo IMPORT.txt para importar os dados. Vamos alterar o Recovery Model do banco de dados para Bulk-Logged e verificar o tamanho do Transaction Log e seu espaço livre.

Script 3:

USE MASTER

DROP DATABASE DB_Bulk

Execute o Script 1 Exceto a instrução INSERT.

–Alterando o Recovery Model para Bulk Logged
ALTER DATABASE DB_Bulk SET RECOVERY BULK_LOGGED

–Visualizando o tamanho e espaco livre do Transaction log para o banco de dados DB_Bulk
DBCC SQLPERF (LOGSPACE)

Observe que antes da importação o Transaction Log do nosso banco de dados DB_Bulk tem o tamanho de 200 MB (199.9922 exatamente) e neste momento estar com 0.3425427% de seu espaço utilizado. Agora com o Recovery Model Bulk Logged configurado vamos fazer a importação no banco de dados e verificar os resultados. Para realizar a importação utilize novamente o utilitário BCP conforme script abaixo.

Script 4:

C:Program FilesMicrosoft SQL Server90ToolsBinn> BCP DB_Bulk.dbo.TB_Bulk in IMPORT.txt -t, -U sa –P (senha)-c

500000 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 22853  Average : (21878.97 rows per sec.)

–Visualizando o tamanho e espaco livre do Transaction log após a importação
DBCC SQLPERF (LOGSPACE)

Com o resultado do comando DBCC SQLPERF (LOGSPACE) podemos verificar que o tamanho atual do nosso Transaction Log é 399 MB, ou seja, o Transaction Log cresceu durante a importação dos dados, como configuramos na opção FILEGROWTH no momento da criação do banco de dados para o crescimento de 100 em 100 MB, o nosso Log cresceu 2 vezes até atingir os 399 MB. Podemos observar também que a coluna Log Space Used(%) estar indicando que o espaço usado dentro do log é 32.1288% de 399.9922MB cerca de 128MB utilizados.

A saída do utilitário BCP nos mostra que foram importadas 500 mil linhas e que o tempo gasto na importação foi de 22853 milisegundos. Com esses resultados podemos concluir que a importação não foi minimamente registrada em log ou seja, os dados foram completamente logados dentro do Transaction Log. Como podemos observar apenas alterar o Recovery Model do banco de dados para Bulk Logged não assegura que a importação será minimamente registrada em log, para garantirmos isso é preciso utilizar a opção TABLOCK do utilitário BCP, com essa opção estamos especificando o modo de bloqueio exclusivo durante a importação. O TABLOCK é necessário para garantir que a importação será minimamente registrada em log juntamente com os outros fatores relacionados a index que descrevi no inicio do artigo. Além de ser necessário, este fornece uma melhor performance durante a importação pois impede que o SQL Server adquira bloqueios a nível de linha para cada instrução durante a importação.

Outro fator importante durante a importação é a opção para especificar o tamanho do lote, o tamanho do lote é quem irá definir a quantidade de linhas inseridas em cada lote, por exemplo poderíamos definir que a importação iria utilizar 5 lotes de 100 mil linhas cada. Qual a desvantagem disso? Se voltarmos no inicio do artigo falei que o SQL Server trabalha diferente durante a importação para tabelas vazias e tabelas que já contenham dados e esteja indexada, com isso se dividirmos em vários lotes nossa importação durante o primeiro lote o SQL Server irá minimamente registrar em log os registros, mas a partir do segundo lote a importação não será mais minimamente registrada em log, porque a tabela não estará mais vazia, assim as instruções serão registradas em log por completo. Vamos ver um exemplo desta situação mais adiante. Por padrão o utilitário BCP trabalha com o conceito de único lote, as mensagems informadas durante a importação são apenas informativas, para especificar o tamanho do lote utilize a opção b e o número de linhas.

Agora que entendemos o porque que nossa importação não foi minimamente registrada em log vamos refazer os testes utilizando a opção TABLOCK do utilitário BCP e verificar os resultados. Para refazermos os testes siga a sequencia de script listada abaixo para recriar o banco de dados e a tabela de teste.

• Execute a instrução DROP DATABASE DB_Bulk do Script 3.
• Execute o Script 1 exceto a instrução INSERT.
• Execute a instrução ALTER DATABASE do Script 3.

Com o banco de dados recriado e configurado o Recovery Model Bulk Logged execute o script abaixo para importar os dados para a tabela TB_Bulk,

Script 5:

C:Program FilesMicrosoft SQL Server90ToolsBinn>BCP DB_Bulk.dbo.TB_Bulk in IMPORT.txt -t, -U sa –P (senha) -c -h TABLOCK -b 500000

Starting copy…
500000 rows sent to SQL Server. Total sent: 500000

500000 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 5688   Average : (87904.36 rows per sec.)

–Visualizando o tamanho e espaco livre do Transaction log após a importação
DBCC SQLPERF (LOGSPACE)

Agora podemos observar que a saída do comando DBCC SQLPERF (LOGSPACE) é bastante diferente e por si só já fala muita coisa hein ? Neste caso podemos ver que o tamanho atual do Transaction Log não foi alterado, ou seja, durante a importação são foi preciso estender o log como a importação anterior. O nosso Transaction Log permanece com os mesmos 200 MB de qual foi especificado durante a criação do banco de dados e olhando a coluna Log Space Use(%) podemos perceber também uma grande diferença em relação a importação anterior pois agora apenas 10.4655% do tamanho do Transaction Log é utilizado cerca de 20MB, a primeira importação ocupou cerca de 128MB do tamanho do log.

A saída do utilitário BCP também nos mostra informações importantes como o tempo total da importação que em nosso exemplo foi apenas 5688 milisegundos comparados com a ultima importação é uma diferença muito grande. O Fato da primeira importação precisar estender o Transaction Log contribuiu para o aumento no tempo total da importação. Com estes resultados podemos ver que a importação foi minimamente registrada em log.

Para demostrarmos a diferença entre a tabela indexada vazia e a tabela contendo alguns valores no momento da importação vamos executar os seguintes passos. Execute todo o procedimento listado abaixo para recriar a estrutura e inserir um único valor a tabela TB_Block, após isso vamos realizar novamente a importação e verificar os resultados.

Execute a instrução DROP DATABASE DB_Bulk do Script 3.

Execute o Script 1, inserindo apenas 1 registro na tabela TB_Bulk.

Execute a instrução ALTER DATABASE do Script 3.

Execute o script 5 para importar os dados.

Starting copy…
500000 rows sent to SQL Server. Total sent: 500000

500000 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 21290  Average : (23485.20 rows per sec.)

–Visualizando o tamanho e espaco livre do Transaction log após a importação
DBCC SQLPERF (LOGSPACE)

Podemos observar que durante esta importação obtemos quase o mesmo resultado como a primeira importação,ou seja, a importação não foi minimamente registrada em log. Isso aconteceu pois com a tabela indexada e que já tenha registro a importação não será minimamente registrada em log como descrito acima e demonstrado a partir da tabela no inicio do artigo.

Com isso podemos concluir que apenas especificando o Recovery Model Bulk Logged durante a importação de uma massa de dados não é suficiente para esta ser minimamente registrada em log, é preciso satisfazer diversos fatores como existencia de index, a tabela estar vazia ou não, utilizar a opção TABLOCK do utilitário BCP, utilizar um lote único durante a importação e outro ponto a observar que não comentei acima é que a tabela de destino não pode estar participando de uma replicação. É extremamente recomendado realizar importação de dados durante uma janela de manutenção para melhor otimização do processo, diminuindo contenção de recursos do servidor, melhor situação para utilizar a opção TABLOCK de qual adquire um lock exclusivo na tabela de destino e talvez até dropar alguns índices antes da importação dos dados para melhor performance e assegurar que a importação será minimamente registrada em log, feita a importação os índices seriam reconstruídos, sei que não é algo tão simples pois alguns índices são utilizados para garantir a integridade dos dados.

Espero que este artigo seja útil para a comunidade, até o próximo.