How often should you update your stats? What is "too seldom"? How often is "too often"?
The answer is "it depends" on your database, users, data, etc.
So I've tried to log what our stats look like over time, in two tables. Here they are:
DROP TABLE /*IF EXISTS */ dbo.dm_db_stats_histogram
DROP TABLE /*IF EXISTS */ dbo.dm_db_stats_properties
go
CREATE TABLE dbo.dm_db_stats_properties(
dm_db_stats_propertiesID INT IDENTITY(1,1) NOT NULL constraint PK_dm_db_stats_properties PRIMARY KEY CLUSTERED,
DatabaseId INT NOT NULL,
object_id int NOT NULL,
stats_id int NOT NULL,
last_updated DATETIME2 NOT NULL,
rows BIGINT NOT NULL,
rows_sampled BIGINT NOT NULL,
steps int NOT NULL,
unfiltered_rows BIGINT NOT NULL,
modification_counter BIGINT NOT NULL,
persisted_sample_percent FLOAT NULL
, SampleDate DATETIME2 NOT NULL CONSTRAINT df_dm_db_stats_properties_SampleDate DEFAULT SYSUTCDATETIME()
)
GO
ALTER TABLE dbo.dm_db_stats_properties ADD StatsName NVARCHAR(128) NOT NULL CONSTRAINT df_dm_db_stats_properties_StatsName DEFAULT ('')
GO
CREATE TABLE dbo.dm_db_stats_histogram(
dm_db_stats_histogramID INT IDENTITY(1,1) NOT NULL constraint PK_dm_db_stats_histogram PRIMARY KEY CLUSTERED,
dm_db_stats_propertiesID INT NOT NULL,
object_id int NOT NULL,
stats_id int NOT NULL,
step_number int NOT NULL,
range_high_key sql_variant NOT NULL,
range_rows real NOT NULL,
equal_rows real NOT NULL,
distinct_range_rows bigint NOT NULL,
average_range_rows REAL NOT NULL
)
go
ALTER TABLE dbo.dm_db_stats_histogram ADD CONSTRAINT fk_dm_db_stats_properties FOREIGN KEY(dm_db_stats_propertiesID) REFERENCES dbo.dm_db_stats_properties(dm_db_stats_propertiesID)
ALTER TABLE dbo.dm_db_stats_histogram ALTER COLUMN range_high_key SQL_VARIANT NULL
GO
And here is the code I use to log our stats:
SET NOCOUNT ON
BEGIN TRY
DROP TABLE #Stat_Header
END TRY
BEGIN CATCH
END CATCH
CREATE TABLE #Stat_Header (Name sysname, Updated DATETIME, Rows BIGINT, Rows_Sampled BIGINT, Steps SMALLINT, Density REAL, AverageKeyLength INT, StringIndex varchar(10)
, FilterExpression varchar(8000), unfiltered_rows bigint, persisted_sample_percent float)
BEGIN TRY
DROP TABLE #Histogram
END TRY
BEGIN CATCH
END CATCH
CREATE TABLE #Histogram (Step_Number INT IDENTITY(1,1), range_high_key SQL_VARIANT, range_rows REAL NOT NULL, equal_rows REAL NOT NULL, distinct_range_rows BIGINT NOT NULL, average_range_rows REAL NOT NULL)
DECLARE TableCursor CURSOR LOCAL STATIC FOR
SELECT t.name AS TableName, sc.name AS SchemaName
FROM sys.tables t
INNER JOIN sys.schemas sc ON sc.schema_id = t.schema_id
ORDER BY sc.name, t.name
DECLARE @sql NVARCHAR(MAX) = '', @TableName VARCHAR(100), @SchemaName VARCHAR(100), @loopCounter INT =0
SELECT GETDATE() AS StartDate
OPEN TableCursor
WHILE 1 =1 BEGIN
FETCH TableCursor INTO @TableName, @SchemaName
IF @@fetch_status <> 0 BREAK
SELECT @sql = 'declare @Scope_Identity int = 0, @RowCount int
SET NOCOUNT ON' + CHAR(13)
SELECT @sql += '
TRUNCATE TABLE #Stat_Header
TRUNCATE TABLE #Histogram
INSERT INTO #Stat_Header(Name, Updated, Rows, Rows_Sampled, Steps, Density, AverageKeyLength, StringIndex, FilterExpression, unfiltered_rows/*, persisted_sample_percent*/)
exec (''DBCC SHOW_STATISTICS ("' + @SchemaName + '.' + @TableName + '", "' + s.name +'") with STAT_HEADER'')
INSERT INTO dbo.dm_db_stats_properties(databaseid, object_id, stats_id, last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter, persisted_sample_percent, SampleDate, StatsName)
SELECT db_id(), ' + LTRIM(t.object_id) + ', ' + LTRIM(s.stats_id) +', coalesce(sh.Updated, ''2000-01-01''), isnull(sh.rows,0), isnull(sh.Rows_Sampled,0), isnull(sh.steps,0), isnull(sh.unfiltered_rows,0), 0, sh.persisted_sample_percent, cast(''' + LTRIM(SYSUTCDATETIME()) + ''' as datetime2(7)), ''' + s.name + '''
FROM #Stat_Header sh
LEFT JOIN dbo.dm_db_stats_properties sp ON sp.object_id=' + LTRIM(t.object_id) + ' AND sp.stats_id=' + LTRIM(s.stats_id) + ' AND sh.Updated=sp.last_updated
WHERE sp.dm_db_stats_propertiesID IS NULL
SELECT @Scope_Identity = SCOPE_IDENTITY(), @RowCount=@@ROWCOUNT
IF @RowCount>0 BEGIN
--raiserror (''here'', 10, 1) with nowait
INSERT INTO #Histogram(range_high_key, range_rows, equal_rows, distinct_range_rows, average_range_rows)
exec (''DBCC SHOW_STATISTICS ("' + @SchemaName + '.' + @TableName + '", "' + s.name +'") with HISTOGRAM'')
INSERT INTO dbo.dm_db_stats_histogram(dm_db_stats_propertiesID, object_id, stats_id, step_number, range_high_key, range_rows, equal_rows, distinct_range_rows, average_range_rows)
SELECT @Scope_Identity, ' + LTRIM(t.object_id) + ', ' + LTRIM(s.stats_id) +', h.Step_Number, h.range_high_key, h.range_rows, h.equal_rows, h.distinct_range_rows, h.average_range_rows
FROM #Histogram h
END
raiserror (''table = ' + @TableName + ', ' + s.name + ', rc= %i '', 10, 1, @RowCount) with nowait
waitfor delay ''00:00:01''
'
FROM sys.stats AS s
INNER JOIN sys.tables t ON t.object_id = s.object_id
INNER JOIN sys.schemas sc ON sc.schema_id = t.schema_id
WHERE t.name=@TableName
AND sc.name = @SchemaName
IF @loopCounter < 1 EXEC dbo.LongPrint @String=@sql
SET @loopCounter +=1
EXEC sp_executesql @sql
--BREAK
END
DEALLOCATE TableCursor
SELECT GETDATE() AS StopDate
I also have a solution that uses the new DMV's on SQL Server 2016+17
exec sp_foreachdb @command = N'
use ?
DECLARE @SampleDate DATETIME2 = SYSUTCDATETIME()
INSERT INTO master.dbo.dm_db_stats_properties(DatabaseID, object_id, stats_id, last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter, persisted_sample_percent, SampleDate, StatsName)
SELECT db_id() as DatabaseID, s.object_id, s.stats_id, sp.last_updated, sp.rows, sp.rows_sampled, sp.steps, sp.unfiltered_rows, sp.modification_counter, sp.persisted_sample_percent, @SampleDate, s.name
FROM ?.sys.stats AS s
INNER JOIN ?.sys.tables t ON t.object_id = s.object_id
INNER JOIN ?.sys.schemas sc ON sc.schema_id = t.schema_id
CROSS APPLY ?.sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
LEFT JOIN master.dbo.dm_db_stats_properties T1 ON T1.object_id = s.object_id AND T1.stats_id = s.stats_id AND T1.last_updated=sp.last_updated
WHERE sp.last_updated IS NOT NULL
AND T1.last_updated IS NULL
select @@rowcount as r1
INSERT INTO master.dbo.dm_db_stats_histogram(dm_db_stats_propertiesID, object_id, stats_id, step_number, range_high_key, range_rows, equal_rows, distinct_range_rows, average_range_rows)
SELECT sp.dm_db_stats_propertiesID, sp.object_id, sp.stats_id, hist.step_number, hist.range_high_key, hist.range_rows, hist.equal_rows, hist.distinct_range_rows, hist.average_range_rows
FROM master.dbo.dm_db_stats_properties sp
CROSS APPLY ?.sys.dm_db_stats_histogram(sp.[object_id], sp.stats_id) AS hist
WHERE sp.SampleDate = @SampleDate
select @@rowcount as r2
', @exclude_list='tempdb, model', @print_dbname=1
My real question
is now, how do I write queries, based on the data that I've collected, that will show me
a) which tables, indexes, columns that do (not) change a lot over my sampling period?
b) which tables, index, columns that benefit from a WITH FULLSCAN command?
