Executar o script no database em que se deseja obter as estatísticas que estão desatualizadas.
O Script leva como base o traceflag 2371
Executar o script no database em que se deseja obter as estatísticas que estão desatualizadas.
O Script leva como base o traceflag 2371
SELECT [schema_name] = sc.[name]
, [object_id] = ob.[object_id]
, [object_name] = ob.[name]
, stats_id = st.stats_id
, stats_name = st.[name]
, auto_created = st.auto_created
, is_incremental = st.is_incremental
, heap_clustered = CASE OBJECTPROPERTY( ob.[object_id], 'TableHasClustIndex')
WHEN 1 THEN 'Clustered' ELSE 'Heap' END
, partition_number = ISNULL(it.partition_number,1)
, last_updated = it.last_updated
, [rows] = it.[rows]
, rows_sampled = it.rows_sampled
, percent_sampled = CONVERT(DECIMAL(5,2), (it.rows_sampled / (it.[rows] * 1.0)) * 100.0)
, steps = it.steps
, unfiltered_rows = it.unfiltered_rows
, modification_counter = it.modification_counter
, th.threshold
FROM sys.stats st WITH(NOLOCK)
INNER JOIN sys.tables ob WITH(NOLOCK) ON ob.[object_id] = st.[object_id]
INNER JOIN sys.schemas sc WITH(NOLOCK) ON sc.[schema_id] = ob.[schema_id]
CROSS APPLY ( SELECT it.partition_number
, it.last_updated
, it.[rows]
, it.rows_sampled
, it.steps
, it.unfiltered_rows
, it.modification_counter
FROM sys.dm_db_stats_properties_internal(st.[object_id],st.stats_id ) it
WHERE ( st.is_incremental = 0 )-- estatísticas não particionadas
OR ( st.is_incremental = 1 AND it.partition_number IS NOT NULL )-- linha detalhe da estatística particionada
) it
CROSS APPLY ( SELECT threshold = CONVERT(INT, CASE WHEN ISNULL(it.[rows],0) <= 25000
THEN (ISNULL(it.[rows],0) * 0.2 ) + 500 -- threshold PADRÃO
ELSE SQRT(ISNULL(it.[rows],0) * 1000.0 ) -- TRACEFLAG 2371 (https://www.virtual-dba.com/sql-server-statistics-trace-flag-2371/)
END
)
) th
WHERE ob.is_ms_shipped = 0
AND it.modification_counter > th.threshold
AND it.rows > 500