Início / dba / Perguntas / 64101
Accepted
crokusek
Asked: 2014-04-29 10:28:40 +0800 CST

Como melhorar a estimativa de linha de 1 linha na junção para dados recém-inserido

  • 772

Existe uma estatística personalizada para a coluna CacheId de uma tabela. Após uma atualização de estatísticas durante a noite:

Statistics for INDEX 'ST_TableName_CacheId'.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Name                            Updated                         Rows                            Rows Sampled                    Steps                           Density                         Average Key Length              String Index                    
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ST_TableName_CacheId Apr 26 2014  2:04AM             121482                          121482                          6                               0                               4                               NO                                                              121482                          

All Density                     Average Length                  Columns                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0.1666667                       4                               CacheId                         

Histogram Steps                 
RANGE_HI_KEY                    RANGE_ROWS                      EQ_ROWS                         DISTINCT_RANGE_ROWS             AVG_RANGE_ROWS                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
39968                           0                               20247                           0                               1                               
40058                           0                               20247                           0                               1                               
40062                           0                               20247                           0                               1                               
40066                           0                               20247                           0                               1                               
40069                           0                               20247                           0                               1                               
41033                           0                               20247                           0                               1

1) Desempenho de uma junção em relação a um conjunto de dados existente nesta tabela onde CacheId = 41033 funciona bem com boas estimativas (23622 vs real de 20247).

2) Em seguida, é realizada uma inserção com CacheId = 41273 de 20247 linhas.

3) Em seguida, uma junção com esse conjunto de dados recém-inserido mostra uma estimativa ruim de 1 linha, resultando em um plano ruim.

4) Uma atualização manual das estatísticas (que originalmente era com fullscan) mostra um novo histograma:

Statistics for INDEX 'ST_TableName_CacheId'.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Name                            Updated                         Rows                            Rows Sampled                    Steps                           Density                         Average Key Length              String Index                    
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ST_TableName_CacheId Apr 28 2014 10:41AM             141729                          141729                          7                               0                               4                               NO                                                              141729                          

All Density                     Average Length                  Columns                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0.1428571                       4                               CacheId                         

Histogram Steps                 
RANGE_HI_KEY                    RANGE_ROWS                      EQ_ROWS                         DISTINCT_RANGE_ROWS             AVG_RANGE_ROWS                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
39968                           0                               20247                           0                               1                               
40058                           0                               20247                           0                               1                               
40062                           0                               20247                           0                               1                               
40066                           0                               20247                           0                               1                               
40069                           0                               20247                           0                               1                               
41033                           0                               20247                           0                               1                               
41274                           0                               20247                           0                               1

5) A execução da mesma consulta de junção novamente para CacheId = 41274 mostra estimativas perfeitas (20247) e bom desempenho.

Q1) Por que matematicamente a estimativa original é tão ruim? Quero dizer, os CacheIds são esparsos, mas não na proporção de 20.000:1.

Q2) À medida que o número de cacheIds aumenta, você esperaria que as estimativas para dados recém-inserido melhorassem naturalmente?

Q3) Existem maneiras (engolir, truques ou outras) para melhorar a estimativa (ou torná-la menos certa de 1 linha) sem ter que atualizar as estatísticas toda vez que um novo conjunto de dados é inserido (por exemplo, adicionando um conjunto de dados falso em um CacheId muito maior = 999999).

Aqui está o número real de linhas para todos os CacheIds na tabela:

CacheId Rows
39968   20247
40058   20247
40062   20247
40066   20247
40069   20247
41033   20247
41274   20247

[Não acho que os QPs sejam necessários para responder a essa pergunta e é um pouco trabalhoso limpá-los. Posso responder a perguntas específicas, se necessário! ]

sql-server statistics

2 respostas

  1. Best Answer

    Q1) Por que matematicamente a estimativa original é tão ruim? Quero dizer, os CacheIds são esparsos, mas não na proporção de 20.000:1.

    Aqui está a regra para acionar a atualização automática das estatísticas Funcionalidade de manutenção estatística (autostats) no SQL Server :

    O algoritmo acima pode ser resumido na forma de uma tabela:

    Tipo de tabela | Condição vazia | Limite Quando Vazio | Limite Quando Não Vazio

    Permanente | < 500 linhas | Nº de Alterações >= 500 | Nº de alterações >= 500 + (20% de cardinalidade)

    Mesmo que o KB aponte para 2000, ainda é verdade até 2012.

    Percorra este cenário e veja por si mesmo.

    PASSO 1

    SET STATISTICS IO OFF;
GO
SET NOCOUNT ON;
GO
-- make sure the Include Actual Execution Plan is off!!!
IF OBJECT_ID('IDs') IS NOT NULL
DROP TABLE dbo.IDs;

CREATE TABLE IDs
(
ID tinyint NOT NULL
)

INSERT INTO IDs
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7;

IF OBJECT_ID('TestStats') IS NOT NULL
DROP TABLE dbo.TestStats;

CREATE TABLE dbo.TestStats
(
 ID tinyint NOT NULL,
 Col1 int NOT NULL,
 CONSTRAINT PK_TestStats PRIMARY KEY CLUSTERED (ID, col1)
);

DECLARE @id int = 1
DECLARE @i int = 1

WHILE @id <= 6
BEGIN
 SET @i = 1

WHILE @i <= 20247
BEGIN
    INSERT INTO dbo.TestStats VALUES(@id,@i);

    SET @i = @i + 1
END

SET @id = @id + 1
END

-- so far so good!
SELECT ID, COUNT(*) AS RowCnt FROM dbo.TestStats GROUP BY ID;

DBCC SHOW_STATISTICS('TestStats',PK_TestStats) WITH HISTOGRAM;

    Agora temos uma tabela com IDs de 1 a 6 e cada ID tem 20247 linhas. As estatísticas parecem boas até agora!

    PASSO 2

    -- now insert another ID = 7 with 20247 rows
DECLARE @i int = 1;

WHILE @i <= 20247
BEGIN
  INSERT INTO dbo.TestStats VALUES(7,@i);

  SET @i = @i + 1
END

-- see the problem with the histogram?
SELECT ID, COUNT(*) FROM dbo.TestStats GROUP BY ID;

DBCC SHOW_STATISTICS('TestStats',PK_TestStats) WITH HISTOGRAM;

    Olhe para a tabela e histograma! A tabela real tem ID = 7 com 20247 linhas, mas o histograma não faz ideia de que você acabou de inserir os novos dados porque a atualização automática não foi acionada. De acordo com a fórmula, você precisa inserir (20247 * 6) * 0,2 + 500 = 24.796,4 linhas para acionar uma atualização automática das estatísticas nesta tabela.

    Assim, se você observar os planos para essas consultas, verá as estimativas erradas:

    -- CTRL + M to include the Actual Execution plan
-- now, IF we run these queries, the Optimizer has no info about ID = 7
-- and the Estimates 1 because it cannot say 0.
SELECT ts.*
FROM dbo.TestStats ts
INNER JOIN dbo.IDs ON IDs.ID = ts.ID
WHERE IDs.ID = 1;

SELECT ts.*
FROM dbo.TestStats ts
INNER JOIN dbo.IDs ON IDs.ID = ts.ID
WHERE IDs.ID = 7;

    Consulta nº 1:

    Consulta nº 1:

    Consulta nº 2:

    Consulta #2

    O Optimize não pode dizer 0 linhas, então mostra apenas 1.

    ETAPA 3

    -- now we manually update the stats
UPDATE STATISTICS dbo.TestStats WITH FULLSCAN;

-- check the histogram
DBCC SHOW_STATISTICS('TestStats',PK_TestStats) WITH HISTOGRAM;

-- rerun the queries
SELECT ts.*
FROM dbo.TestStats ts
INNER JOIN dbo.IDs ON IDs.ID = ts.ID
WHERE IDs.ID = 1;

SELECT ts.*
FROM dbo.TestStats ts
INNER JOIN dbo.IDs ON IDs.ID = ts.ID
WHERE IDs.ID = 7;

    Agora o histograma mostra o ID 7 ausente e os planos de execução também mostram as estimativas corretas.

    Consulta nº 1:

    Consulta #1

    Consulta nº 2:

    Consulta #2

    Q2) À medida que o número de cacheIds aumenta, você esperaria que as estimativas para dados recém-inserido melhorassem naturalmente?

    Sim, assim que você ultrapassar o limite de 20% + 500 do total de linhas. A atualização automática será acionada. Você pode executar esse cenário executando novamente o STEP#1, mas modificar o STEP#2 executando estas consultas:

    -- now insert another ID = 7 with 20247 rows
DECLARE @i int = 1;

WHILE @i <= 20247
BEGIN
   INSERT INTO dbo.TestStats VALUES(7,@i);

   SET @i = @i + 1
END

-- see the problem with the histogram?
SELECT ID, COUNT(*) FROM dbo.TestStats GROUP BY ID;

DBCC SHOW_STATISTICS('TestStats',PK_TestStats) WITH HISTOGRAM;
GO
-- try to insert ID = 8 to trigger the auto update for the stats
DECLARE @i int = 1;

WHILE @i <= 4548
BEGIN
  INSERT INTO dbo.TestStats VALUES(8,@i);

  SET @i = @i + 1
END

-- no update yet
SELECT ID, COUNT(*) FROM dbo.TestStats GROUP BY ID;

DBCC SHOW_STATISTICS('TestStats',PK_TestStats) WITH HISTOGRAM;

    Nenhuma atualização ainda porque o limite é 24.796,4 - 20247 = 4549,4, mas inserimos apenas 4548 linhas para ID 8. Agora insira esta linha e verifique novamente o histograma:

    -- this will trigger the update
INSERT INTO dbo.TestStats VALUES(8,4549);

-- double check
SELECT ID, COUNT(*) FROM dbo.TestStats GROUP BY ID;

DBCC SHOW_STATISTICS('TestStats',PK_TestStats) WITH HISTOGRAM;

    Q3) Existem maneiras (engolir, truques ou outras) para melhorar a estimativa (ou torná-la menos certa de 1 linha) sem ter que atualizar as estatísticas toda vez que um novo conjunto de dados é inserido (por exemplo, adicionando um conjunto de dados falso em um CacheId muito maior = 999999).

    Controlando o comportamento do Autostat (AUTO_UPDATE_STATISTICS) no SQL Server

    No entanto, quando uma tabela se torna muito grande, o limite antigo (uma taxa fixa – 20% das linhas alteradas) pode ser muito alto e o processo Autostat pode não ser acionado com frequência suficiente. Isso pode levar a possíveis problemas de desempenho. SQL Server 2008 R2 Service Pack 1 e versões posteriores introduzem o sinalizador de rastreamento 2371 que você pode habilitar para alterar esse comportamento padrão. Quanto maior o número de linhas em uma tabela, menor será o limite para acionar uma atualização das estatísticas. Por exemplo, se o sinalizador de rastreamento estiver ativado, as estatísticas de atualização serão acionadas em uma tabela com 1 bilhão de linhas quando ocorrerem 1 milhão de alterações. Se o sinalizador de rastreamento não estiver ativado, a mesma tabela com 1 bilhão de registros precisaria de 200 milhões de alterações antes que uma estatística de atualização fosse acionada.

    Espero que isso tenha ajudado você a entender! Boa pergunta!

    • 6

  2. Uma resposta para Q3)

    Q3) Are there any ways (gulp, tricks or otherwise) to improve the estimate (or make it less certain of 1 row) without having to update the statistics every time a new set of data is inserted (e.g. adding a fake data set at a much larger CacheId = 999999).

    In the join, add some confusion using IsNull(), and at the end, add an "optimize for".

     select ... from ... join ...
   where CacheId = IsNull(@cacheId, 0)    
  option (recompile, optimize for (@cacheId = 41274))

    Both seem to be needed. The Id 0 does not really exist. The ID value used within the "optimize for" does not appear to matter, and apparently does not even need to exist.

    Side Note: I had also tried deleting the custom statistics, adding an fresh index on CacheId but its implicit statistics still eventually behaved the same as the explicit custom statistics as far as the update row count thresholds.

    Edit 2014-04-29:

    As estimativas para "chaves ascendentes" provavelmente foram aprimoradas no estimador de cardinalidade aprimorado do SQL Server 2014

    Há também uma solução traceon() para chaves ascendentes desde 2005 SP1 do comentário de Mark Storey-Smith .

    Editar 2015-05-07:

    Alguns casos ainda estavam estimando 1 linha ( às vezes ). Usar desconhecido parece ajudar e então o IsNull() também pode ser removido:

      select ... from ... join ...
   where CacheId = @cacheId
  option (recompile, optimize for (@cacheId = unknown))
    • 1

relate perguntas