Estatisticas incrementais... ou como atualizar estatisticas em base de 100TB (VLDB)

Estou aqui para comentar mais um caso onde as estatisticas não eram atualizadas com frequencia em uma base de 100TB em uma analise de risco feita no servidor de um cliente, então foi sugerido a utilização de estatisticas incrementais (SQL 2014 ou maior)

Segundo a própria documentação (https://msdn.microsoft.com/en-us/library/ms190397(v=sql.120).aspx)

When new partitions are added to a large table, statistics should be updated to include the new partitions. However the time required to scan the entire table (FULLSCAN or SAMPLE option) might be quite long. Also, scanning the entire table isn't necessary because only the statistics on the new partitions might be needed. The incremental option creates and stores statistics on a per partition basis, and when updated, only refreshes statistics on those partitions that need new statistics

Ou seja não precisa ficar atualizando e fazendo FULLSCAN em uma tabela gigantesca todo dia, você pode, por exemplo, atualizar apenas as 2 ultimas partições

Vamos ver abaixo como aplicar isso no seu ambiente

A primeira coisa a fazer é alterar a base para que as próximas estatisticas sejam criadas como incrementais

ALTER DATABASE SANDBOX
SET AUTO_CREATE_STATISTICS ON (INCREMENTAL = ON)

Depois disso, para todas tabelas particionadas onde você deseja fazer esta alteração, você precisará atualizar estatistica completa nas estatisticas, antes de conseguir atualizar por estatistica. Apesar deste passo ser muito pesado ele irá compensar depois.

UPDATE STATISTICS [PartitionTable] ([PK__Partitio__357D0D3ED0CC792E]) WITH FULLSCAN, INCREMENTAL = ON

Para facilitar um pouco, criei alguns scripts para ajudar neste processo. Um deles para fazer de uma tabela apenas, outro para pegar todas tabelas particionadas de um database.

------------------------------------------------------------------------------------------------------------------------------

--GENERATE INCREMENTAL UPDATE STATISTICS FOR 1 TABLE

SELECT 'UPDATE STATISTICS ' + QUOTENAME(object_name(object_id)) + ' (' + QUOTENAME(name) + ') WITH FULLSCAN, INCREMENTAL = ON'
FROM sys.stats
where object_id = object_id('[dbo].[PartitionTable]')
and is_incremental = 0

------------------------------------------------------------------------------------------------------------------------------

--GENERATE INCREMENTAL UPDATE STATISTICS FOR ALL PARTITIONED TABLES

SELECT
'UPDATE STATISTICS ' + QUOTENAME(object_name(S.object_id)) + ' (' + QUOTENAME(s.name) + ') WITH FULLSCAN, INCREMENTAL = ON'
FROM sys.tables T
INNER JOIN sys.indexes I
ON T.object_id = I.object_id
INNER JOIN sys.data_spaces DS
ON I.data_space_id = DS.data_space_id
INNER JOIN sys.stats S
ON I.object_id = S.object_id
WHERE DS.type = 'PS' -- PARTITION_SCHEME -- PARTITION TABLE
AND S.is_incremental = 0

Depois deste primeiro passo mais lento, agora basta você planejar como será atualizado suas partições. No meu cenário o particionamento era por Ano-Mês e as partições antigas são raramente atualizadas. Então iremos atualizar diariamente apenas as 2 ultimas partições

Este script irá gerar 3 tipos de update

1. UPDATE para tabelas não particionadas
2. UPDATE para tabelas particionadas, mas sem incremental
3. UPDATE para tabelas particionadas com incremental (2 ultimas partições)

DECLARE @RESAMPLE BIT = 1

DECLARE @PERCENT_SAMPLE INT = 100 -- IF @RESAMPLE = 0 SET @PERCENT_SAMPLE

DECLARE @PROCESS_LAST_X_NONEMPTY_PARTITIONS INT = 2

------------------------------------------------------------------------------

SELECT
SQL_COMMAND =
'UPDATE STATISTICS ' + QUOTENAME(T.Name) + ' (' + QUOTENAME(S.name) + ') '
+ IIF (@RESAMPLE = 1,
'WITH RESAMPLE',
'WITH SAMPLE ' + CONVERT(VARCHAR(3), @PERCENT_SAMPLE) + ' PERCENT')
+ CHAR(10)
,INFO = ' -- NON PARTITIONED TABLES'
FROM sys.tables T
INNER JOIN sys.indexes I
ON T.object_id = I.object_id
INNER JOIN sys.data_spaces DS
ON I.data_space_id = DS.data_space_id
INNER JOIN sys.stats S
ON I.object_id = S.object_id
WHERE DS.type = 'FG' -- ROWS_FILEGROUP -- NON PARTITION TABLE
AND S.stats_id IS NOT NULL -- FILTER TABLES WITH NO STATISTICS

------------------------------------------------------------------------------

SELECT
SQL_COMMAND =
'UPDATE STATISTICS ' + QUOTENAME(T.Name) + ' (' + QUOTENAME(S.name) + ') '
+ IIF (@RESAMPLE = 1,
'WITH RESAMPLE',
'WITH SAMPLE ' + CONVERT(VARCHAR(3), @PERCENT_SAMPLE) + ' PERCENT')
+ CHAR(10)
,INFO = ' -- PARTITIONED TABLES NON INCREMENTAL'
FROM sys.tables T
INNER JOIN sys.indexes I
ON T.object_id = I.object_id
INNER JOIN sys.data_spaces DS
ON I.data_space_id = DS.data_space_id
INNER JOIN sys.stats S
ON I.object_id = S.object_id
WHERE DS.type = 'PS' -- PARTITION_SCHEME -- PARTITION TABLE
AND S.is_incremental = 0

------------------------------------------------------------------------------

IF (OBJECT_ID ('tempdb..#TEMP_LAST2PARTITIONS') IS NOT NULL)

DROP TABLE #TEMP_LAST2PARTITIONS

;WITH AUX_LAST2PARTITIONS AS
(

SELECT
T.object_id
,TableName = T.Name
,I.index_id
,IX_Name = COALESCE(I.Name,'[HEAP]')
,P.partition_number
,P.rows
,i.data_space_id
,ROW_NUMBER = ROW_NUMBER() OVER ( PARTITION BY T.object_id, I.index_id ORDER BY P.partition_number DESC)

FROM sys.tables T
INNER JOIN sys.indexes I
ON T.object_id = I.object_id
INNER JOIN sys.data_spaces DS
ON I.data_space_id = DS.data_space_id
INNER JOIN sys.partitions P
ON I.object_id = P.object_id
AND I.index_id = P.index_id
WHERE DS.type = 'PS' -- PARTITION_SCHEME -- PARTITION TABLE
AND P.rows > 0 -- filter empty partitions

)

SELECT
* INTO #TEMP_LAST2PARTITIONS
FROM AUX_LAST2PARTITIONS
WHERE [ROW_NUMBER] <= @PROCESS_LAST_X_NONEMPTY_PARTITIONS

CREATE CLUSTERED INDEX #IX_TEMP_LAST2PARTITIONS ON #TEMP_LAST2PARTITIONS (object_id, index_id, partition_number)

;WITH AUX AS
(

SELECT
AUX.object_id
,AUX.TableName
,AUX.index_id
,AUX.IX_Name
,StatsName = S.name
,AUX.partition_number
,AUX.rows
,PARTITION_VALUE = ISNULL(CAST(left_prv.value AS VARCHAR(MAX))+ CASE WHEN pf.boundary_value_on_right = 0 THEN ' > '
ELSE ' >= '
END , '-INF > ')
+ 'X' + ISNULL(CASE WHEN pf.boundary_value_on_right = 0 THEN ' >= '
ELSE ' > '
END + CAST(right_prv.value AS NVARCHAR(MAX)), ' > INF')
FROM #TEMP_LAST2PARTITIONS AUX
INNER JOIN sys.stats S
ON aux.object_id = S.object_id
LEFT JOIN sys.partition_schemes ps
ON aux.data_space_id = ps.data_space_id
LEFT JOIN sys.partition_functions pf
ON ps.function_id = pf.function_id
LEFT JOIN sys.partition_range_values left_prv
ON left_prv.function_id = ps.function_id
AND left_prv.boundary_id + 1 = aux.partition_number
LEFT JOIN sys.partition_range_values right_prv
ON right_prv.function_id = ps.function_id
AND right_prv.boundary_id = aux.partition_number
WHERE S.is_incremental = 1

)

SELECT
SQL_COMMAND = 'UPDATE STATISTICS ' + QUOTENAME(TableName) + ' (' + QUOTENAME(StatsName) + ') WITH RESAMPLE ON PARTITIONS (' + CONVERT(VARCHAR(20), partition_number) + ')' + CHAR(10)
,INFO = ' -- PARTITIONED TABLES INCREMENTAL LAST (' + CONVERT(VARCHAR, @PROCESS_LAST_X_NONEMPTY_PARTITIONS) + ') PARTITIONS ON (' + PARTITION_VALUE + ')'

FROM AUX
ORDER BY TableName, IX_Name, StatsName, partition_number desc

Segue abaixo um exemplo dos comandos

Conclusão

Lembrando que este foi um exemplo, onde eu queria atualizar apenas as 2 ultimas partições, mas cada caso é um caso.

Depois basta pegar estes comandos jogar em uma variável e agendar para executar com [sp_executesql]

No meu cenário para uma das tabelas de 9 TB dados + 18TB indices (117 partições) houve uma redução de tempo de ~3 dias (Update statistics fullscan todas estatisticas da tabela) para apenas ~1h (Update statistics fullscan todas estatisticas da tabela apenas 2 ultima partições).

Para quem quiser testar, seguem os scripts

• SCRIPT UPDATE STATS SET INCREMENTAL - FIRST TIME
• SCRIPT UPDATE STATS INCREMENTAL - MANUAL UPDATES
• SCRIPT UPDATE STATS INCREMENTAL - JOB SCRIPT

This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment.
THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED,
INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute
the object code form of the Sample Code, provided that You agree:
(i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded;
(ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and
(iii) to indentify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits,
including attorneys' fees, that arise or result from the use or distribution of the Sample Code.

Please note: None of the conditions outlined in the disclaimer above will supersede the terms and
conditions contained within the Premier Customer Services Description.