performance

[T-SQL Tip #01] Como filtrar colunas datetime?

Marcel Inowe5 Comments

Nesse primeiro post da série T-SQL Tips iremos abordar como realizar filtros em colunas do tipo datetime. Esse é um tema relativamente simples, porém pode causar muitos problemas de performance na execução das queries se não forem feitos da forma correta.

É muito comum o uso do campo datetime na modelagem das tabelas para armazenar registros com data e hora como “2019-01-01 23:59:59.997”, porém também é muito comum o desenvolvedor não querer utilizar a hora armazenada na coluna para realizar o filtro e para isso ele acaba realizando algum tipo de conversão para remover/truncar a hora da coluna em tempo de execução da query e esse é o principal ponto que iremos abordar, pois isso pode causar alguns problemas de performance.

Antes de ir para os exemplos, é necessário entender que qualquer tipo de conversão em uma coluna que é utilizada em where ou join, pode fazer com que o predicado de busca se torne um predicado non-sarg(non search argument) e com isso o SQL Server pode optar por um Index Scan ao invés de um Index Seek ,o que normalmente é o mais indicado para ler menos páginas do índice/tabela.
O recomendado é que sempre tenhamos predicados de busca SARG(Search Argument) e para isso devemos evitar ao máximo as conversões ou uso de funções nas buscas. Falaremos um pouco mais sobre SARG/Nonsarg em um post futuro, mas durante os exemplos abaixo ficará mais claro o que é um predicado SARG e um Non-sarg e eu usarei esses termos para referenciar uma query “boa” de uma query “ruim”.

Tabela com coluna datetime

Abaixo uma tabela modelada com coluna datetime.

Tabela: Sales.SalesOrderHeaderEnlarged

A tabela acima faz parte do banco de dados AdventureWorks e foi criada utilizando o script de enlarged para deixá-la com uma massa de dados maior. Abaixo link para download do script:
https://www.sqlskills.com/blogs/jonathan/enlarging-the-adventureworks-sample-databases/

A tabela está com pouco mais de 1.290.000 de registros. Abaixo pequena amostragem de distribuição dos registros.

Amostra de dados

Para uma melhor performance eu criei um índice na tabela acima pela coluna OrderDate e adicionei a coluna CustomerID como include, pois serão essas colunas utilizadas nos exemplos.

CREATE NONCLUSTERED INDEX IX_SalesOrderHeaderEnlarged_OrderDate 
ON Sales.SalesOrderHeaderEnlarged(OrderDate) INCLUDE(CustomerID)

Vamos aos exemplos

Filtrando registros de um dia específico

Já presenciei em muitos casos onde o desenvolvedor deseja filtrar os dados de um determinado dia e a coluna por estar armazenando a hora, o desenvolvedor resolveu o problema da seguinte maneira:

SET STATISTICS IO, TIME ON
SELECT SalesOrderID, CustomerID, OrderDate 
FROM Sales.SalesOrderHeaderEnlarged
WHERE CONVERT(CHAR(8),OrderDate,112) = '20110531'

--Ou assim:

SET STATISTICS IO, TIME ON
SELECT SalesOrderID, CustomerID, OrderDate 
FROM Sales.SalesOrderHeaderEnlarged
WHERE YEAR(OrderDate) = 2011 
AND MONTH(OrderDate) = 5 
AND DAY(OrderDate) = 31

OK! zero problemas com relação a essa abordagem, ela FUNCIONA mas qual o problema dela? Vamos analisar o plano de execução dessa query.

Execution plan – Queries non-sarg

Como podemos notar em ambos os casos o operador com maior custo foi um Index Scan no índice criado.
Abaixo vamos ver como ficaram as estatísticas de leitura e tempo da query. Para isso eu utilizei SET STATISTICS IO, TIME ON antes da execução das queries.

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(41 rows affected)
Table 'SalesOrderHeaderEnlarged'. Scan count 1, logical reads 3522, 
physical reads 0, read-ahead reads 0, lob logical reads 0, 
lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 281 ms,  elapsed time = 371 ms.

(41 rows affected)
Table 'SalesOrderHeaderEnlarged'. Scan count 1, logical reads 3522, 
physical reads 0, read-ahead reads 0, lob logical reads 0, 
lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 94 ms,  elapsed time = 116 ms.

Como podemos ver em ambos os casos tivemos 3522 páginas lidas e tempos de 371ms e 116ms para apenas uma execução. Considerando que cada página de dados equivale a 8KB então tivemos 21,51MB de páginas lidas em cada execução.

Reescrevento a query e melhorando a performance

Conforme citamos acima, temos que evitar ao máximo a conversão da coluna que será utilizada como predicado de busca, para evitar que esse predicado se torne um predicado non-sarg, portanto o ideal é executarmos o filtro sempre respeitando o tipo do dado que foi definido para a coluna como no exemplo abaixo, onde eu farei a comparação de data acrescentando a hora das 00:00:00.000 até 23:59:59.997 para que eu possa retornar todos os registros do dia 31/05/2011.

SET STATISTICS IO, TIME ON
SELECT SalesOrderID, CustomerID, OrderDate 
FROM Sales.SalesOrderHeaderEnlarged
WHERE OrderDate BETWEEN '20110531 00:00:00.000' AND '20110531 23:59:59.997'

Abaixo o plano de execução de estatísticas de leitura e tempo de uma execução.

Execution Plan – Query SARG
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(41 rows affected)
Table 'SalesOrderHeaderEnlarged'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Analisando o plano de execução podemos notar que o operador de leitura do índice mudou para o Index Seek o que de cara pode me indicar uma performance melhor e a leitura de página caiu de 3522 para apenas 3 páginas lidas, ou seja, estamos falando de 27,51 MB contra apenas 24 KB e o tempo de 116 ms no melhor caso do exemplo anterior para 0 ms.
Bom…116 ms no melhor caso para 0 ms fica claro que tivemos 100% de melhor no tempo de execução da query, mas vamos continuar com mais alguns exemplos e comparativos abaixo.

Filtrando registros de um determinado mês

Abaixo alguns exemplos de abordagens que eu presencio constantemente de como desenvolvedores normalmente fazem para filtrar os registros de um determinado mês.

SET STATISTICS IO, TIME ON
SELECT SalesOrderID, CustomerID, OrderDate
FROM Sales.SalesOrderHeaderEnlarged
WHERE CONVERT(CHAR(8),OrderDate,112) BETWEEN '20110701' and '20110731'

-- ou assim quando o dev não quer se preocupar com quantos dias tem o mês (y)
-- meu coração chega explode de alegria quando eu vejo isso!!!

SET STATISTICS IO, TIME ON
SELECT SalesOrderID, CustomerID, OrderDate 
FROM Sales.SalesOrderHeaderEnlarged
WHERE LEFT(CONVERT(CHAR(8),OrderDate,112),6) = '201107'

Vejamos como ficaram os planos de execução e estatísticas de leitura e tempo dos exemplos acima.

Exectution Plan – Queries non-sarg
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(609 rows affected)
Table 'SalesOrderHeaderEnlarged'. Scan count 1, logical reads 3522, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 719 ms,  elapsed time = 746 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(609 rows affected)
Table 'SalesOrderHeaderEnlarged'. Scan count 1, logical reads 3522, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 437 ms,  elapsed time = 488 ms.

Mais uma vez como podemos ver o Index Scan foi o operador com o maior custo nos planos acima e a leitura de páginas assim como nos exemplos non-sarg anteriores foi de 3522 páginas lidas, ou seja, 27,52 MB de dados lidos da memória.

Reescrevento a query e melhorando a performance

Mais uma vez vamos reescrever a query utilizando todo o range possível de hora para filtramos todos os registros que estiverem em um determinado range de data.

SET STATISTICS IO, TIME ON
SELECT SalesOrderID, CustomerID, OrderDate 
FROM Sales.SalesOrderHeaderEnlarged
WHERE OrderDate BETWEEN '20110701 00:00:00.000' AND '20110731 23:59:59.997'

Vejamos como ficaram os planos de execução e estatísticas de leitura e tempo dos exemplos acima.

Execution Plan – Query SARG
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(609 rows affected)
Table 'SalesOrderHeaderEnlarged'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Como podemos ver acima, mais uma vez o operador de leitura do índice mudou de Index Scan para Index Seek, o tempo de execução caiu de 488 ms no melhor caso para 0 ms e as leituras de páginas de 3522 páginas para apenas 6 páginas lidas(estamos lendo um mês inteiro, por isso o aumento é natural).

Estressando o ambiente

Vocês podem estar pensando: ok, mas 488 ms é um tempo baixo para a execução de uma query. Eu poderia dizer que sim é um tempo relativamente baixo para a execução de uma query, dependendo de sua importância dentro de um sistema. Agora imagine se essa query é responsável por capturar o valor das ações em tempo real da bovespa? Eu posso afirmar que 488 ms para o retorno do valor de uma ação é um problema para um sistema.

Abaixo vamos fazer uma pequena comparação de tempo utilizando o aplicativo SQL Query Stress com 100 execuções de cada query.

100 execuções da query non-sarg retornando dados de um mês
Query non-sarg
100 execuções da query Sarg retornando dados de um mês
Query sarg

Agora ficou claro, provar que uma query com tempo na casa dos 400 ms é um problema. Com apenas 100 execuções da query utilizando conversão levou-se 42 segundos, contra apenas 593 ms das mesmas 100 execuções da query escrita de forma correta.

Conclusão

Podemos ver que é muito mais fácil filtrar as datas utilizando a conversão para truncar a hora ou funções como DAY, MONTH e YEAR para não ter que se preocupar se o dia tem 28, 30 ou 31 dias, mas toda a facilidade tem seu preço.

Eu gosto sempre de pensar que não é porque eu posso fazer algo, que eu devo fazer algo e isso eu aplico nas minhas análises de performance do dia a dia.

Evitem ao máximo o uso das conversões ou funções para facilitar você chegar a um objetivo por mais cômodo/fácil que seja e tenham sempre em mente que:

O jeitinho/gambiarra de hoje será seu problema de amanhã.

SQL Server 2019 IQP – Parte 1 – Table Variable Deferred Compilation

guiftorresLeave a comment

Olá pessoal, vem aí uma série de posts sobre o IQP, recém lançado com o SQL Server 2019.

O IQP ( Intelligent Query Processing) é uma família de features lançadas no SQL Server 2019 para endereçar uma série de problemas conhecidos de performance. Com um comportamento By Default da Engine, há um esforço mínimo (migrar para o SQL 2019) ou inexistente para começar a utilizá-lo.

Iniciando hoje no primeiro Post sobre IQP, a feature Table Variable Deferred Compilation.

Só lembrando que no SQL Server 2017 foi lançado o AQP (Adaptative Query Processing), que hoje faz parte do IQP, mas tudo que for discutido é relativo ao SQL Server 2019.

Table Variable Deferred Compilation

Essa feature do IQP permite a geração de melhores planos de execução em consultas que usam Table Variable. Durante a compilação e otimização do plano de execução será feito um Count Rows da Table Variable, que será utilizado para escolher os melhores operadores ao gerar o plano de execução.

O famoso problema do Estimated = 1

Mas qual é o risco ao utilizar Table Variables? Em versões anteriores ao SQL Server 2019 estima-se apenas 1 linha para a Table Variable ao gerar o plano de execução, que pode causar alguns problemas, principalmente quando se insere milhares de linhas na Table Variable e faz-se joins com outras tabelas. Vamos demonstrar no exemplo abaixo.

Obs: Para o nosso lab utilizamos a base do AdventureWorks.

Vamos criar também um índice para auxiliar o nosso lab, alterar o nível de compatibilidade para o SQL Server 2017 e simular a chamada de uma procedure que utiliza Table Variable. 

/* CONSIDERANDO O COMPATIBILITY DO SQL SERVER 2017 */
ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 140
GO

/* INDICE AUXILIAR */
USE [AdventureWorks]
GO
CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_OrderQty]
ON [Sales].[SalesOrderDetail] ([OrderQty])
GO

/* PROCEDURE PARA RETORNAR VENDAS */
create procedure RetornaVendasQtde (
	@OrderQty int
) as
begin

	declare @TempIDs as table (SalesOrderId int)

	insert into @TempIDs
	select SalesOrderID
	from Sales.SalesOrderDetail
	where OrderQty > @OrderQty

	select oh.SalesOrderID, OrderDate, PurchaseOrderNumber, AccountNumber, SubTotal
	from Sales.SalesOrderHeader oh
	inner join @TempIDs t on oh.SalesOrderID = t.SalesOrderId

end
go

A procedure utilizada como exemplo recebe o parâmetro OrderQty, que representa a quantidade de itens na venda. Para simular o problema será feito duas chamadas, uma com um parâmetro mais restritivo (40) e outra com um parâmetro menos restritivo (1). O SELECT abaixo representa a parte do código onde inserimos os registros na table variable. Observe que passando o parâmetro 40 serão inseridos 2 registros na Table Variable, enquanto com o valor 1, serão 46363 registros.

select  (  select COUNT(SalesOrderID)
           from Sales.SalesOrderDetail
           where OrderQty > 40 ) as PrimeiraChamada
,       (  select COUNT(SalesOrderID)
           from Sales.SalesOrderDetail
           where OrderQty > 1 ) as SegundaChamada

Agora executando a procedure com o parâmetro @OrderQty = 40 e analisando o seu plano de execução.

Obs: Estamos usando a opção Include Actual Execution Plan para coletar o plano de execução estimado e atual; e também o SET STATISTICS IO, TIME ON para coletar as estatísticas de execução de logical reads e cpu time.

set statistics io, time on
exec dbo.RetornaVendasQtde @OrderQty = 40
Procedure retornou 2 registros com o parâmetro @OrderQty = 40

Pelo tempo de resposta, muito bom! 0 ms o tempo de resposta fazendo ao todo apenas 7 logical reads. O único porém aqui é o Estimated Number of Rows = 1. É o comportamento antigo discutido acima. Para toda Table Variable estima-se 1 linha até o SQL Server 2017.

Vamos fazer outro teste então, com outro parâmetro menos restritivo, @OrderQty = 1.

Procedure retornou 46363 registros com o parâmetro @OrderQty = 1

Apesar do retorno da procedure ser em 0.5 segundos, há um problema grave. Foi estimado de 1 linha onde foram retornadas 46363, o que fez o otimizador optar pelo Nested Loops e provocou um alto número de Logical Reads na tabela SalesOrderHeader, maior até mesmo que o número de páginas da própria tabela.

Obs: pra quem não lembra como funciona os algoritmos de joins, acessar esse link.

Como funciona no SQL Server 2019

Agora, executando novamente o último exemplo no SQL Server 2019, com o compatibility level = 150.

/* CONSIDERANDO O COMPATIBILITY DO SQL SERVER 2019 */
ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 150
GO

set statistics io, time on
exec dbo.RetornaVendasQtde @OrderQty = 1
Retornou os mesmos 46363. Nenhuma alteração de código foi realizada.

Com a nova feature Table Variable Deferred Compilation, o comportamento é outro. Agora é feito a estimativa correta do número de linhas na Table Variable e permite usar um algoritmo mais adequado para a situação. No novo plano de execução, é utilizado o Hash Match ao invés do Nested Loops, o que gerou um ganho expressivo na quantidade de Logical Reads e custo de CPU.

Abaixo tabela comparativa com os custos das execuções da procedure.

Compatibility LevelCPU Time MsElapsed Time MsLogical Reads
140 (SQL 2017)203454139164
150 (SQL 2019)313441454

Os resultados acima consideram apenas uma chamada única. O próximo passo é simular um cenário de várias chamadas simultâneas com o SQLQueryStress. Serão feitas 1000 chamadas através de 50 iterações com 20 threads em paralelo.

Abaixo os resultados com execuções no COPATIBILITY_LEVEL 140 e 150.

32 segundos pra executar 1000 chamadas. Tempo médio de 1.15 segundos por chamada.
20 segundos pra executar 1000 chamadas. Tempo médio de 0.71 segundos por chamada.

Abaixo o gráfico do Perfmon considerando CPU Time e Batch Requests/sec. Destaque para a média de Batch Requests/sec que chegou a 760 no COMPATIBILITY_LEVEL 140 e a 1000 no COMPATIBILITY_LEVEL 150. Levando esses números para um cenário pesado, significa dizer maior poder de processamento e ganho de performance com a nova feature habilitada.

Como habilitar \ desabilitar?

Por DEFAULT essa feature vem habilitada no SQL Server 2019, porém é possível desabilitar a nível de database ou a nível de query.

-- Database Scope
ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = OFF;
ALTER DATABASE SCOPED CONFIGURATION SET DEFERRED_COMPILATION_TV = ON; 

-- Query Scope -> OPTION (USE HINT('DISABLE_DEFERRED_COMPILATION_TV'));
use [AdventureWorks]
go

declare @TempIDs as table (SalesOrderId int)

insert into @TempIDs
select SalesOrderID
from Sales.SalesOrderDetail
where OrderQty > 1

select oh.SalesOrderID, OrderDate, PurchaseOrderNumber, AccountNumber, SubTotal
from Sales.SalesOrderHeader oh
inner join @TempIDs t on oh.SalesOrderID = t.SalesOrderId
OPTION (USE HINT('DISABLE_DEFERRED_COMPILATION_TV'))

Referência

Documentação Microsoft

Webinar IQP with Joe Sack

IQP Custumer Questions and Answers

Ignite Tour SP – PPT Fabiano Amorim

Performance com LOBs no SQL Server

guiftorresLeave a comment

É 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 text, ntext, or image strings are stored directly in the data row. All existing BLOB (binary large object: text, ntext, 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.

1º Meetup da Data Tuning

admindatatuningLeave a comment

Fala galera! No dia 30/05 às 20Hrs estaremos apresentando nosso primeiro Meetup online! Falaremos um pouco sobre as novas Features de Performance que estão vindo no Microsoft SQL Server 2019. Todos estão convidados para participar deste evento que é totalmente gratuito e online! Para se inscrever e ver mais detalhes é só clicar no link abaixo:

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

Introdução a Algoritmos de Joins no SQL Server

guiftorres2 Comments

Pessoal, o post de hoje foi feito para explicar de maneira bem rápida os operadores de joins existentes no SQL Server. Entender cada tipo de join é essencial para uma boa análise de performance, pois os tempos de execução podem variar bastante de acordo com cada cenário.
Muitas pessoas perguntam. Qual o melhor operador de join? Não há. Cada um tem características específicas e são utilizados em cenários distintos. Por isso a importância de conhecer cada um deles.
Existem 3 operadores de JOIN no SQL Server: Nested Loops, Hash Join e Merge Join.

NESTED LOOPS

Como o próprio nome sugere, utiliza a ideia de loop. Para cada valor de uma tabela (outer input), faz-se uma busca de igualdade na outra tabela (inner input). É muito efetivo quando utilizado em cenários onde um input possui poucos registros e o inner input possui índice para a condição de igualdade.
No exemplo abaixo o Query Optimizer resolveu utilizar o NESTED LOOPS como solução para o INNER JOIN.

select top 1000
  SalesOrderID   = sor.SalesOrderID
, LineTotal      = sor.LineTotal
, OrderDate      = soh.OrderDate
, ShipDate       = soh.ShipDate
, TrackingNumber = sor.CarrierTrackingNumber
from       Sales.SalesOrderDetail sor
inner join Sales.SalesOrderHeader soh 
           on sor.SalesOrderID = soh.SalesOrderID
where    sor.CarrierTrackingNumber = '6431-4D57-83'
order by sor.SalesOrderID

Perceba que o número de linhas estimadas é pequeno, porque nesse caso foi utilizado o filtro “where sor.CarrierTrackingNumber = ‘6431-4D57-83′”.

Nesse cenário o plano de execução se mostrar muito bom, uma vez que necessitou de apenas 9 Logical Reads pra retornar o result set em microssegundos.
Table ‘SalesOrderHeader’. Scan count 0, logical reads 6 Table ‘SalesOrderDetail’. Scan count 1, logical reads 3.
CPU time = 15 ms, elapsed time = 0 ms.

MERGE JOIN

O Merge Join geralmente é utilizado para grandes volumes de dados. Ele necessita de dois inputs ordenados pelo valor de comparação (ON clauses), onde é feito o teste de igualdade linha a linha de cada output. Sua condição de parada é ao término dos dados do menor input.
Retirando a condição de busca por TrackingNumber, verifica-se uma alteração no plano de execução, onde o Query Optimizer opta por utilizar o MERGE JOIN.

select top 1000
  SalesOrderID   = sor.SalesOrderID
, LineTotal      = sor.LineTotal
, OrderDate      = soh.OrderDate
, ShipDate       = soh.ShipDate
, TrackingNumber = sor.CarrierTrackingNumber
from       Sales.SalesOrderDetail sor
inner join Sales.SalesOrderHeader soh 
           on sor.SalesOrderID = soh.SalesOrderID
order by sor.SalesOrderID

Como mencionado acima, o Merge Join utiliza inputs ordenados. No exemplo utilizado as duas tabelas possuem índices ordenados pela coluna SalesOrderID.

HASH JOIN

O Hash Join é geralmente utilizado para grandes volumes de dados, condições não indexadas, entre outros. É composto por duas fases: Build e Probe. Na primeira fase (Build), o Query Engine escolhe o menor input e gera uma Hash Table baseado nas Hash Keys. Na segunda fase (Probe), toda linha do segundo input é transformado em hash e comparado com a Hash Table gerado na fase Build, retornando assim todas as igualdades. Importante lembrar que se o Memory Grant da consulta não for suficiente, o SQL Server irá utilizar a TEMPDB para armazenar a Hash Table.

No exemplo abaixo, retirando-se o TOP 1000 e alterando a ordenação para ShipDate, o Query Optimizer decidiu por utilizar o Hash Match.

select 		
  SalesOrderID   = sor.SalesOrderID
, LineTotal      = sor.LineTotal
, OrderDate      = soh.OrderDate
, ShipDate       = soh.ShipDate
, TrackingNumber = sor.CarrierTrackingNumber
from       Sales.SalesOrderDetail sor
inner join Sales.SalesOrderHeader soh 
           on sor.SalesOrderID = soh.SalesOrderID
order by soh.ShipDate

FORCE JOIN (CUIDADO!)

É possível forçar a utilização de um algoritmo de join no seu código T-SQL, mas cuidado! Isso pode gerar dor de cabeça se não for bem utilizado. Basta colocar o nome do algoritmo entre o INNER | LEFT | RIGTH | FULL e o JOIN.
Exemplo:

select top 1000
  SalesOrderID   = sor.SalesOrderID
, LineTotal      = sor.LineTotal
, OrderDate      = soh.OrderDate
, ShipDate       = soh.ShipDate
, TrackingNumber = sor.CarrierTrackingNumber
from             Sales.SalesOrderDetail sor
inner MERGE join Sales.SalesOrderHeader soh 
                 on sor.SalesOrderID = soh.SalesOrderID
where    sor.CarrierTrackingNumber = '6431-4D57-83'
order by sor.SalesOrderID

É isso aí pessoal, se tiverem alguma dúvida por favor deixar um comentário.
Obs: os exemplos foram feitos em cima da base AdventureWorks, disponível nesse link.

REFERÊNCIA

Tipo de Joins no SQL Server

https://docs.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-2017