Início / dba / Perguntas / 205833
Accepted
Frederik Vanderhaegen
Asked: 2018-05-05 04:44:09 +0800 CST

O plano de consulta não é invalidado após as estatísticas de atualização

  • 772

Estou executando alguns testes para descobrir quando os planos de consulta são invalidados após uma atualização de estatísticas. A máquina que estou usando para teste é uma SQL Server Developer 2016 SP1 CU7.

Encontrei um artigo no BrentOzar.com como posso rastrear as recompilações, mas nunca consigo uma recompilação. Auto Update Statisticse Auto Create Statisticsambos estão habilitados

Este é o meu teste:

/* Create a table and put over 1k rows in it (to get past the 500 row stats threshold) */
CREATE TABLE dbo.MyTable (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, StringField VARCHAR(50));
GO
INSERT INTO dbo.MyTable(StringField) 
SELECT 'Stuff' FROM sys.all_objects;
GO

/* Start a trace monitoring recompiles */
exec sp_BlitzTrace @Action='start', @TargetPath='c:\temp\', @SessionId=@@SPID, @TraceRecompiles=1;
GO
SELECT * FROM dbo.MyTable WHERE StringField = 'NoRecordsHere';
GO
/* Increment the row mod counters to encourage a stats update */
BEGIN TRAN
DELETE dbo.MyTable;
GO
ROLLBACK
GO
UPDATE STATISTICS dbo.MyTable WITH fullscan
GO

/* We don't strictly need to wait, but makes different executions easier to see: */
WAITFOR DELAY '00:00:10';
GO
SELECT * FROM dbo.MyTable WHERE StringField = 'NoRecordsHere';
GO
EXEC sp_BlitzTrace @Action='stop'
GO
EXEC sp_BlitzTrace @Action='read'
GO

Pelo que entendi, o plano deve ser invalidado quando você liga UPDATE STATISTICS WITH FULLSCANe há linhas alteradas. Ou eu estou esquecendo de alguma coisa?

sql-server-2016 statistics

1 respostas

  1. Best Answer

    Seu código de exemplo original não era executável novamente (a criação da tabela não estava lá, ou a população de linhas), então eu o reescrevi para torná-lo reexecutável.

    O problema é que sua consulta é trivialmente simples: não importa se as estatísticas são atualizadas ou não, ela produzirá o mesmo plano. Esse ajuste fará com que sua consulta SELECT ignore a otimização trivial:

    SELECT * FROM dbo.MyTable WHERE StringField = 'NoRecordsHere' AND 1 = (SELECT 1);

    Tente o teste novamente com isso:

    /* Start a trace monitoring recompiles */
exec sp_BlitzTrace @Action='start', @TargetPath='c:\temp\', @SessionId=@@SPID, @TraceRecompiles=1;
GO
SELECT * FROM dbo.MyTable WHERE StringField = 'NoRecordsHere' AND 1 = (SELECT 1);
GO
/* Increment the row mod counters to encourage a stats update */
BEGIN TRAN
DELETE dbo.MyTable;
GO
ROLLBACK
GO
UPDATE STATISTICS dbo.MyTable WITH fullscan
GO

/* We don't strictly need to wait, but makes different executions easier to see: */
WAITFOR DELAY '00:00:10';
GO
SELECT * FROM dbo.MyTable WHERE StringField = 'NoRecordsHere' AND 1 = (SELECT 1);
GO
EXEC sp_BlitzTrace @Action='stop'
GO
EXEC sp_BlitzTrace @Action='read'
GO

    E agora você obtém recompilações - observe a terceira linha que mostra recompile_cause = "Estatísticas alteradas"

    Recompila

    Esta é uma das razões pelas quais sp_BlitzCache (disclaimer: sou um autor muito pequeno nesse script de código aberto) mostra avisos para planos triviais - você não obtém todas as coisas legais que obtém em planos totalmente otimizados, como índice ausente recomendações.

    Certamente não estou dizendo que adicionar "WHERE 1 = (SELECT 1)" é uma boa ideia em suas consultas - apenas dizendo que ao criar cenários de reprodução, você deve tomar cuidado com consultas triviais. Para saber se sua consulta é trivialmente simples, visualize o plano de execução, clique com o botão direito do mouse no nó raiz (como o SELECT), clique em Propriedades e observe o Nível de Otimização. Se estiver escrito Trivial, você não está recebendo toda a genialidade desse pessoal da Microsoft.

    • 7

relate perguntas