Erro – STATISTICS IO em Planos Paralelos.

Fala pessoal, beleza?

Estava otimizando uma query nessa tarde, onde a mesma utiliza uma clausula TOP e o seu respective plano era um plano paralelo. Visando identificar onde estavam as maiores leituras dos objetos dessa query utilizei o comando SET STATISTICS IO ON.

Quando fui avaliar na guia “messages” do SSMS (SQL Server Management Studio) percebi que a quantidade de leituras lógicas retornadas pelo comando SET STATISTICS IO ON era um pouco menor do que eu realmente esperava, simplificando: A minha query retornava “X” registros de uma tabela “Y”, no entanto, a quantidade de leituras eram muito menor do que eu tinha no meu retorno do SSMS.

Nesse meio tempo ouvi uma voz me dizendo: “Thiago tenta colocar o MAXDOP(1) pra ver se a query executa mais rápido”. Levando em consideração o conselho, executei a mesma query com o MAXDOP(1), portanto, esperaria ver a mesma quantidade de leituras lógicas que vi anteriormente, correto? Errado.

Quando executei a query utilizando o MAXDOP(1) percebi que a quantidade de registros eram a mesma, no entanto, as leituras lógicas eram diferentes. Pesquisando sobre esse comportamento no BING, encontrei um “Connect Item” da Microsoft que realmente isso é uma comportamento inesperado dentro do produto, conforme abaixo:

https://www.beta.microsoft.com/SQLServer/feedback/details/767250/statistics-io-under-reports-logical-reads-for-parallel-plans

Execute os comandos abaixo para poder similar o mesmo problema que encontrei:

USE AdventureWorks2012;

GO

SET NOCOUNT ON;

SET STATISTICS IO ON; –Liga as Estatisticas de IO

DBCC SETCPUWEIGHT(1000) WITH NO_INFOMSGS –O comando garante que o plano paralelo seja utilizado.

GO

SELECT TOP 15000 * FROM Sales.SalesOrderHeader WHERE OrderDate < ‘20080101’;

SELECT TOP 15000 * FROM Sales.SalesOrderHeader WHERE OrderDate < ‘20080101’ OPTION (MAXDOP 1);

DBCC SETCPUWEIGHT(1) WITH NO_INFOMSGS;

Mais porque esse comportamento acontece?

Quando um plano paralelo tem uma clásusula TOP, por algum motive o SQL Server pode encerrar “threads” anteriormente se existem linhas suficientes (não possuo o valor desse threshold) para serem retornadas para o usuário. Esse encerramento interfere com a coleta de estatisticas do comando SET STATISTICS IO, ao contrário, do que acontece com plano sem paralelismo. Quando isso acontece, possivelmente nós veremos a quantidade reportada de IO para um plano paralelo menor do que a quantidade de IO reportado para um plano serial

Obs: Esse comportamento foi corrigido no SQL Server 2014.

Espero que tenham gostado.