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

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