Performance com LOBs no SQL Server

É bastante comum nos deparamos com databases que utilizam Large Objects. XML, armazenamento de arquivos (planilhas, imagens, documentos, etc) e logs gigantescos de aplicação são exemplos de utilização de LOBs.

Em termos práticos no SQL Server, podemos utilizar os seguintes tipos de dados para trabalhar com LOBs.

  • varchar(max)
  • nvarchar(max)
  • varbinary(max)
  • xml
  • text
  • ntext
  • image

Ok, mas quais os cuidados eu devo ter ao estar utilizando campos LOB? Vamos rodar uma pequena demonstração para tocar alguns pontos importantes sobre performance com LOBs no SQL Server.

Para o nosso exemplo, vamos criar um database e uma tabela utilizando campo VARCHAR(max). Após isso, vamos fazer uma carga de 1 milhão de linhas com dados aleatórios para começar os testes de performance.

-- Create database
create database LobData

-- Cria tabela com campo varchar(max)
drop table if exists dbo.InventarioDB_Varchar
go
create table dbo.InventarioDB_Varchar (
	Id			int identity(1,1) not null primary key
,	DtCriacao	date not null
,	TipoObjeto	varchar(32)
,	Texto		varchar(max)
)
go

-- Realiza carga na tabela InventarioDB_Varchar (~23 segundos)
insert into 
			dbo.InventarioDB_Varchar (DtCriacao, TipoObjeto, Texto)
select top 1000000
			convert(date, c.create_date), c.type_desc, a.definition
from 
			sys.all_sql_modules a
cross join	sys.all_objects c

Ótimo, inserimos os registros na tabela. Vamos agora fazer uma consulta?

set statistics io, time on

-- Vamos fazer uma consulta para retornar quantidade de objetos por tipo.
select
	TipoObjeto
,	count(TipoObjeto) as [Count]
from
	dbo.InventarioDB_Varchar
group by
	TipoObjeto
order by 
	count(TipoObjeto) desc

A consulta rodou em menos de 1 segundo, mas vamos analisar o número de logical reads e o tempo de CPU gasto.

A consulta rodou bem rápido (143 ms), mas algo está estranho. Foi necessário ler 267 mil páginas de dados pra retornar a consulta. É um número bastante alto para uma tabela de 1 milhão de linhas, não? Vamos entender como funciona o armazenamento dos dados pra tentar melhorar isso.

Para começar, vamos conferir como ficou a distribuição dos dados dentro da tabela? Pra isso, estou criando uma procedure que realiza a consulta em DMVs com informações das estruturas internas dos dados nas tabelas (links da documentação das DMVs no final do post).

-- Cria procedure para retornar distribuição das páginas na tabela
create or alter procedure dbo.GetPagesAllocation (@object sysname)
as
begin

	SELECT
		OBJECT_NAME ([sp].[object_id])	AS [Object Name]
	,	[au].[type_desc]				AS [Alloc Unit Type]
	,	[fg].name						as [FileGroup]
	,	[au].total_pages				AS TotalPages
	,	[au].data_pages					AS DataPages
	,	[au].used_pages					as UsedPages
	,	sp.[rows]						AS [Rows]
	FROM
				sys.partitions AS [sp]
	inner join	sys.system_internals_allocation_units [au]
				on  [au].container_id = sp.[partition_id]
	inner join	sys.filegroups [fg]
				on [fg].data_space_id = [au].filegroup_id
	WHERE
		[sp].[object_id] =	(	CASE WHEN (@object IS NULL)
									THEN [sp].[object_id]
									ELSE OBJECT_ID (@object)
								END )

end		
go

-- Verifica alocação dos dados nas tabelas
exec dbo.GetPagesAllocation @object = 'InventarioDB_Varchar'

Ao executar a procedure, obtivemos o resultado abaixo. Perceba que a distribuição das páginas no nosso exemplo é feita entre IN_ROW_DATA e LOB_DATA. O que é isso?

Cada partição de uma tabela é alocada em um tipo de Allocation Unit, que pode ser IN_ROW_DATA, ROW_OVERFLOW_DATA ou LOB_DATA. Se quiser ler mais sobre Allocations Units, alguns links: Link1 | Link2

O SQL Server trata os objetos LOBs de forma distinta, considerando uns como Large User-Defined Type (varchar(max), nvarchar(max), varbinary(max) e xml) e outros como BLOB (text, ntext e image). Mas o que isso implica em termos práticos?

Por exemplo, se você usa VARCHAR(MAX), o SQL Server por default vai tentar armazenar o conteúdo LOB dentro da página de dados (IN_ROW_DATA), respeitando o limite de 8000 bytes. Caso o valor seja maior então a coluna é armazenado no LOB Storage (LOB_DATA) tendo apenas um ponteiro dentro do IN_ROW_DATA.

No exemplo de um campo TEXT, o SQL Server por default armazena o conteúdo LOB diretamente no LOB Storage (LOB_DATA).

Mas é possível alterar esse comportamento. Existem duas Table Options específicas pra armazenamento LOBs. Você pode alterá-las através do comando sp_tableoption. Segue abaixo parte da documentação do site da Microsoft, disponível também nesse link. Mas prestem atenção, cada configuração vale para tipos de dados distintos.

text in rowWhen OFF or 0 (disabled, the default), it does not change current behavior, and there is no BLOB in row.

When specified and @OptionValue is ON (enabled) or an integer value from 24 through 7000, new textntext, or image strings are stored directly in the data row. All existing BLOB (binary large object: textntext, or image data) will be changed to text in row format when the BLOB value is updated. For more information, see Remarks.
large value types out of row1 = varchar(max)nvarchar(max)varbinary(max)xml and large user-defined type (UDT) columns in the table are stored out of row, with a 16-byte pointer to the root.

0 = varchar(max)nvarchar(max)varbinary(max)xml and large UDT values are stored directly in the data row, up to a limit of 8000 bytes and as long as the value can fit in the record. If the value does not fit in the record, a pointer is stored in-row and the rest is stored out of row in the LOB storage space. 0 is the default value.

Large user-defined type (UDT) applies to: SQL Server 2008 through SQL Server 2017.

Use the TEXTIMAGE_ON option of CREATE TABLE to specify a location for storage of large data types.

Alguém percebeu que mesmo a configuração “large value types out of row” igual a 0, eu ainda tenho páginas no LOB_DATA? Por quê?

Conforme explicado acima o SQL Server armazena no ROW_DATA apenas os valores até 8000 bytes. Caso seja maior, ele utiliza um ponteiro para uma página LOB_DATA.

No meu exemplo, tenho 96399 linhas com mais de 8000 bytes e por isso foram armazenadas ao LOB_DATA.

-- Retorna registros com Texto maior que 8000 bytes
select
	count(Id) as [Count]
from 
	dbo.InventarioDB_Varchar 
where 
	DATALENGTH(Texto) > 8000


-- Count
-- -----------
-- 96399

Agora que entendemos o armazenamento de LOBs, como fica a performance ao buscar os dados? Vamos voltar ao nosso exemplo.

Lembrando que a consulta utilizada não necessita do valor da coluna Texto (varchar(max)). Pensando nisso, seria uma boa alternativa deixar os registros LOBs em um Allocation Unit exclusivo para LOB_DATA, certo? Dessa maneira teríamos menos páginas de dados IN_ROW_DATA e consequentemente menas leituras necessárias para retornar nossa consulta. Então vamos fazer esse teste.

-- Vamos habilitar o "large value types out of row" para a tabela
EXEC sp_tableoption 'InventarioDB_Varchar', 'large value types out of row', 1;

Ao alterar o modo de armazenamento da tabela InventarioBD_Varchar ele vai considerar apenas as novas operações. O que já está armazenado continua do mesmo jeito. Qual a solução então? Forçar uma operação em todas as linhas sem alterar o conteúdo. Após isso, vamos rodar um Rebuild e consultar como ficou a distribuição das páginas de dados na tabela.

-- Necessário atualizar todo o campo blob simplesmente pra assumir a nova configuração de armazenamento.
update InventarioDB_Varchar set Texto = Texto

-- Realiza o REBUILD pra eliminar eventual fragmentação
alter index all on InventarioDB_Varchar rebuild

-- Por fim, verifica alocação dos dados com novo modelo de armazenamento
exec dbo.GetPagesAllocation @object = 'InventarioDB_Varchar'

Uouuuu! A diferença é bem grande. Antes haviam 262396 páginas IN_ROW_DATA e agora temos 7091. Porém o número de páginas LOB_DATA aumentou para 483605 comparado a 244524. Vamos rodar novamente a consulta e comparar os resultados.

set statistics io, time on

-- Vamos fazer uma consulta para retornar quantidade de objetos por tipo.
select
	TipoObjeto
,	count(TipoObjeto) as [Count]
from
	dbo.InventarioDB_Varchar
group by
	TipoObjeto
order by count(TipoObjeto) desc

Obtivemos uma melhora significativa de performance, principalmente no número de Logical Reads. Conforme comparativo entre as duas execuções ANTES \ DEPOIS.

Logical ReadsCPU Time MsElapsed Time Ms
ANTES267398312143
DEPOIS70767881

OBSERVAÇÕES IMPORTANTES

Pessoal, por favor se atentem as observações abaixo.

  • O nosso exemplo é uma consulta leve, mas considerando um caso com tabelas maiores e alto volume de chamadas simultâneas, isso pode dar um fôlego grande para o ambiente e salvar a sua pele.
  • Cuidado, a consulta apresentada não retorna a coluna TEXTO (varchar(MAX)) e devido a isso ele não acessa as páginas LOB_DATA. Se o seu cenário for diferente, estude muito bem o seu ambiente para saber se essa alteração faz sentido ou não, pois o seu custo pode ficar maior.
  • Faça o mesmo teste com uma coluna TEXT e observe que por default ele já separa os valores LOBs em LOB_DATA. Não estou falando que TEXT é melhor, apenas notem o comportamento default. E não esqueçam: TEXT já está marcado como DEPRECATED.

Alguma dúvida, correção, crítica, sugestão? Por favor respondam nos comentários ou diretamente por email: guilherme@datatuning.com.br

REFERÊNCIAS

https://docs.microsoft.com/pt-br/sql/relational-databases/system-stored-procedures/sp-tableoption-transact-sql?view=sql-server-2017

https://docs.microsoft.com/pt-br/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-2017

https://docs.microsoft.com/pt-br/sql/relational-databases/system-catalog-views/sys-allocation-units-transact-sql?view=sql-server-2017

https://docs.microsoft.com/pt-br/sql/relational-databases/system-catalog-views/sys-partitions-transact-sql?view=sql-server-2017

https://www.microsoftpressstore.com/articles/article.aspx?p=2225060

SQL Server – Understanding Allocation Units – In Row Data, LOB Data & Row Overflow Data

Antigo post do Luti (site da Nimbus que saiu do ar) e tenho apenas ele em modelo PDF, graças ao acervo digital do meu amigo Igor rs.

Próximo Meetup da Data Tuning

Fala galera! Na próxima quinta-feira, 27/06 às 20:00 ocorrerá o próximo Meetup da Data Tuning. Desta vez iremos expor problemas tensos que passamos em algum momento de nossa carreira como DBA SQL Server, como identificamos estes problemas, como analisamos e como resolvemos cada um. Você não pode perder! Clique no link abaixo e se inscreva!

https://www.meetup.com/pt-BR/Data-Tuning-Group/events/262365536/

Dica: removendo arquivos do tempdb

Recentemente ao realizar o acerto dos arquivos do tempdb em instâncias do SQL Server 2012 e superiores, me deparei com uma situação que ainda não tinha visto nas versões anteriores ao SQL Server 2012.

Vamos a simulação do problema!

Ao executar o comando abaixo:

ALTER DATABASE tempdb REMOVE FILE temp03

O erro abaixo é apresentado:

Msg 5042, Level 16, State 1, Line 2
The file ‘temp03’ cannot be removed because it is not empty.

Com a mensagem acima, executei o comando abaixo:

USE tempdb
GO
DBCC SHRINKFILE(temp03,EMPTYFILE)

DBCC SHRINKFILE: Page 3:32 could not be moved because it is a work table page.

Msg 2555, Level 16, State 1, Line 4
Cannot move all contents of file “temp03” to other places to complete the emptyfile operation.

E agora? O SQL Server informa que não é possível remover o arquivo do tempdb porque o mesmo não está vazio e ao tentar realizar o shrink o SQL informa que não consegue mover todo o conteúdo para outro arquivo.
Confesso que a primeira vez que vi o erro fiquei confuso!

Abaixo, eu mostro como está a distribuição dos arquivos do tempdb da instância.

Trace Flag 3608

Para a simulação desse problema, estou utilizando o SQL Server 2017.
Uma das formas que eu encontrei para contornar esse comportamento é utilizando a Trace Flag 3608.
A TF 3608 faz com que não ocorra o start e recovery de nenhuma base de dados, exceto a master. Nessa situação as bases de dados só passarão pelo start e recovery ao serem acessadas e com isso é possível executar o comando de exclusão dos arquivos do tempb sem receber o erro “The file xxx cannot be removed because it is not empty”.

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-2017

Habilitando a Trace Flag 3608

A primeira coisa a fazer é acessar o SQL Server Configuration Manager, ir nas propriedades do serviço do SQL Server e selecionar a aba Startup Parameter.

SQLServerConfigurationManagerStartupParameter

No campo Specify a startup parameter adicione o parâmetro -T3608 e clique no botão Add.

3608

Feito isso, basta realizar stop/start do serviço do SQL Server e com o SQL Server reiniciado, podemos observar no ERRORLOG que a engine fez o recovery apenas na base de dados master.

Com isso podemos observar que ao listar os arquivos do tempdb novamente, apenas os arquivos padrão de quando o SQL Server foi instalado será listado.

tempdb3608

Porém se listarmos os arquivos do tempdb pela sys.master_files iremos observar que os demais arquivos estão configurados, porém como o tempdb não foi realizado o recovery eles não estão em uso.

sysmasterfile3608

Feito isso agora iremos conseguir realizar a exclusão dos arquivos do tempdb com sucesso.

tempdb_removefile

Agora iremos remover a TF 3608 e dar stop/start no serviço do SQL Server para verificarmos se a remoção do arquivo temp03 de fato ocorreu e analisar que sem a TF 3608 todas as bases são startadas e o recovery é executado após o restart do serviço.

tempdb files

tempdb2

ERRORLOG

ERRORLOG após remoção da TF 3608.

Bom pessoal, espero que isso possa ajudá-los no dia a dia. Remover arquivos do tempdb não é uma tarefa que se realiza com frequência nos ambientes, mas é importante sempre checar essas configurações para que o tempdb não esteja configurado de forma errada.
Até a próxima.

SQL Server + Docker – Provisionando uma Instância de Banco de Dados Rapidamente

Fala meu povo! Espero que todos estejam bem. 

Sabe quanto você quer testar um sistema, um código que desenvolveu ou até mesmo uma nova feature de um banco de dados, mas não quer instalar todo o SGBD? Quem já instalou alguns SGBDs (Sistemas Gerenciadores de Banco de Dados) sabe que alguns deles possuem setups extensos como Microsoft SQL Server e Oracle, por exemplo. 

Neste post trago uma alternativa extremamente rápida que você pode utilizar para provisionar uma instância SQL Server em poucos minutos. 

Para isso, antes de tudo, precisamos instalar o Docker, sistema gerenciador de container mais popular existente. Esse tutorial está sendo feito em cima de Linux, mais especificamente o Ubuntu 18.04: 

Instalação do Docker CE no Ubuntu

Agora vamos instalar o Docker Community Edition ou apenas Docker CE.  

1. Atualize o seu repositório apt

$ sudo apt-get update

2. Instale alguns pacotes para que o apt possa utilizar repositórios HTTPS

$ sudo apt-get install \
    apt-transport-https \
    ca-certificates \
    curl \
    gnupg-agent \
    software-properties-common

3. Adicione a chave GPG oficial do Docker:

$ curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -

4. Verifique se a chave corresponde com o fingerprint 9DC8 5822 9FC7 DD38 854A E2D8 8D81 803C 0EBF CD88

5. Com o próximo comando adicionaremos o repositório da versão estável (stable) do Docker CE, para instalar versões que ainda estão em teste utilize o tutorial que deixaremos nos links finais do post. 

Como meu computador possui um sistema operacional 64 bits, utilizarei a versão x86_64, caso seu sistema operacional seja 32 bits, arm entre outros, utilize os repositórios existentes no link que irei deixar no final do post. 

$ sudo add-apt-repository \
   "deb [arch=amd64] https://download.docker.com/linux/ubuntu \
   $(lsb_release -cs) \
   stable"

Após feito os passos acima, vamos então instalar o Docker CE.  

6. Atualize o índice de pacotes do apt

$ sudo apt-get update

7. Agora sim! Vamos instalar a última versão do Docker CE e do containerd: 

$ sudo apt-get install docker-ce docker-ce-cli containerd.io

8. Verifique se a instalação funcionou executando o comando a seguir. Esse comando baixa uma imagem de container chamada Hello World e depois executa esta imagem: 

$ sudo docker run hello-world

A saída do comando acima deverá ser essa: 

Bom, agora que estamos com o Docker devidamente instalado, podemos partir para o provisionamento da instância SQL Server. O objetivo deste post é ser o mais simples possível, voltado para uma utilização controlada da instância de banco de dados, ou seja, um ambiente para testes e talvez, para alguns casos, desenvolvimento de aplicações. Futuramente farei outros posts voltados mais especificamente para ambientes que vão do desenvolvimento até ambientes produtivos altamente escaláveis.

Provisionamento da Instância SQL Server em container

Bora subir um SQL Server 2019 CTP 2.5 então.

1. Baixar a imagem do SQL Server do repositório da Microsoft: 

$ sudo docker pull mcr.microsoft.com/mssql/server:2019-CTP2.5-ubuntu

2. Baixou a imagem? Então agora é só executar um novo container que a magia vai acontecer: 

$ sudo docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=SENHAdoS@2019' \
   -p 1433:1433 --name sqldt \
   -d mcr.microsoft.com/mssql/server:2019-CTP2.5-ubuntu

O que temos no comando acima:

  • run: comando do docker para executar um novo container; 
  • -e 'ACCEPT_EULA=Y': aqui você está aceitando aquele contrato de termos de uso que aparece na instalação do SQL; 
  • -e 'SA_PASSWORD=SENHAdoS@2019': senha do usuário SA, precisa ser uma senha forte; 
  • -p 1433:1433: no comando -p você declara a porta em que o SQL Server ficará disponível, o primeiro número antes do : é a porta que ficará disponível para o host, ou seja, para sua máquina. O número depois do : é a porta que será aberta dentro do container. Calma que mais pra frente ficara mais claro; 
  • --name sqldt: aqui você coloca um nome para o container, é uma boa prática fazer isso pois além de ficar mais fácil de identificar o seu container, também facilita com que outros containers comuniquem com este container. Se você não declara um nome, o próprio Docker seta um nome para o seu container; 
  • -d: esse comando faz com que o container seja executado em backgroud, ou seja, você executa o comando, um hash é printado na saída do comando, esse hash é a identificação de execução do seu container; 
  • mcr.microsoft.com/mssql/server:2019-CTP2.5-ubuntu: aqui temos a imagem que baixamos anteriormente e que é utilizada para executar o container. 

Depois de executar o comando acima apenas apareceu um hash gigante, não é? Então, essa é a identificação de execução do container. Para verificar se o container está rodando, execute o comando abaixo: 

$ docker ps

A saída do comando é essa: 

Como podemos ver, o container está em execução. Sendo assim, vamos conectar na instância. 

Estou utilizando um Linux, logo, vou utilizar o Azure Data Studio para conectar no SQL Server: 

Se estiver tudo certo, o client irá conectar: 

Clique em New Query para que possamos executar algumas consultas interessantes, como:

SELECT @@VERSION

Com este comando podemos ver qual é a versão do SQL que estamos utilizando: 

Como podemos na imagem, o comando trouxe a versão do SQL que escolhemos (2019 – CTP2.5), a edição (Developer – Padrão, podemos especificar qual edição queremos antes de iniciar o container, é só utilizar a opção -e 'MSSQL_PID=Enterprise') e a versão do Linux, que no caso é o Ubuntu 16.04. 

Mas calma, eu havia dito no começo do post que a versão que estou executando no meu computador é a 18.04, então como o container está executando uma versão abaixo da minha? Essa é a magia dos containers. Os containers se beneficiam do LXC (Linux Containers), sistema que abstrai algumas camadas de acesso ao kernel (cgroups e namespace são algumas delas), tornando-o “compartilhado”, desta forma, possibilitando que outras distribuições de Linux usufruam do mesmo kernel do sistema operacional em execução no host. O Docker foi construído para facilitar a execução de containers no Linux e vem revolucionando a forma como fazemos aplicações atualmente. 

Não irei me estender muito sobre Docker neste post, mas deixarei links onde vocês poderão conhecer mais sobre Docker e sobre containers. 

Vamos agora criar um banco de dados e ver se tudo se comporta bem:

CREATE DATABASE test;

Aparentemente o banco de dados foi criado, mas será mesmo que foi? Vamos verificar: 

SELECT 
    name
    ,state_desc
    ,compatibility_level
FROM sys.databases 
WHERE name = 'test';

O banco de dados está lá conforme podemos ver. 

Bom pessoal, esse foi nosso post sobre SQL Server em containers Docker. Deixo alguns links uteis os quais me baseei para fazer este post e outros que utilizei para aprender mais sobre Docker. 

Links Úteis 

https://docs.docker.com/install/linux/docker-ce/ubuntu/