I don't have a precise answer for your direct question, however I was recently faced with a similar dilemma. Our tables are quit a lot smaller, however they have enough records that the auto-statistics update was almost never working.
We are interested in updating statistics after a minimum number of records are changed, not a minimum percent of the records. For instance, if more than 1,000,000 records have been added or modified, we rebuild the statistics.
I accomplished this by adding a TIMESTAMP field (the database-wide auto-incrementing BINARY(8) style timestamp) to the table(s) in question.
I created a stored procedure to update statistics that looks at the StatsThreshold table to determine which tables to update. The SP is similar to:
CREATE PROCEDURE AutoUpdateStats
(
@TestOnly bit = 1 /* Defaults to TEST-ONLY mode */
, @FullScan bit = 0 /* Defaults to NON FULLSCAN mode */
)
AS
BEGIN
/*
Executes UPDATE STATISTICS against tables with more than x number of changed rows
By: Max Vernon
Date: 2012-10-02
*/
SET NOCOUNT ON;
/* if StatsThreshold Table does not exist, create it */
IF COALESCE((SELECT name FROM sys.tables WHERE name = 'StatsThreshold'),'')=''
BEGIN
CREATE TABLE StatsThreshold
(
StatsThreshold INT NOT NULL PRIMARY KEY CONSTRAINT PK_StatsThreshold IDENTITY(1,1)
, TableName nvarchar(255)
, LastTimeStamp BINARY(8) CONSTRAINT DF_StatsThreshold_LastTimeStamp DEFAULT((0x0000000000000001))
, Threshold INT NOT NULL CONSTRAINT DF_StatsThreshold_Threshold DEFAULT((0))
, StatsThresholdTimeStamp TIMESTAMP
);
/* Create a starting point for StatsThreshold */
/* StatsThreshold will need to be manually updated as new tables are brought online */
INSERT INTO StatsThreshold (TableName, LastTimeStamp, Threshold)
SELECT name, 0x0000000000000001, 10 /* 10 is the minimum # of changes necessary */
FROM sys.tables;
END
DECLARE @cmd nvarchar(max);
DECLARE @params nvarchar(max);
DECLARE @Table nvarchar(255);
DECLARE @Threshold int;
DECLARE @Count int;
DECLARE @TSField nvarchar(max);
DECLARE cur cursor local forward_only for
SELECT TableName, Threshold
FROM StatsThreshold;
OPEN cur;
FETCH NEXT FROM cur INTO @Table, @Threshold;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TSField = (
select top(1) c.name
from sys.columns c
inner join sys.tables t on c.object_id = t.object_id
inner join sys.types ty on c.system_type_id = ty.system_type_id
where t.name = @Table
and ty.name = 'timestamp'
);
IF COALESCE(@TSField,'')=''
BEGIN
/* create a timestamp field in the current table */
SET @TSField = @Table + 'TimeStamp';
SET @cmd = 'ALTER TABLE ' + QUOTENAME(@Table) + ' ADD ' + quotename(@TSField) + ' TIMESTAMP;';
RAISERROR (@cmd, 0, 1) WITH NOWAIT;
IF @TestOnly = 0
BEGIN
EXEC sp_executesql @cmd;
SET @TSField = @Table + 'TimeStamp';
END
ELSE
BEGIN
SET @TSField = '';
END
END
IF @TSField <> ''
BEGIN
SET @Count = 0;
IF @TestOnly = 1 RAISERROR (@TSField, 0, 1) WITH NOWAIT;
/* Get the number of new/changed rows */
SET @cmd = 'SET @CountChanged = (SELECT COUNT(*) FROM ' + quotename(@Table) + ' WHERE ' + quotename(@TSField) + ' > (SELECT LastTimeStamp FROM StatsThreshold WHERE TableName = ''' + @Table + '''));';
IF @TestOnly = 1 RAISERROR (@cmd, 0, 1) WITH NOWAIT;
SET @params = '@CountChanged int OUTPUT';
EXEC sp_executesql @cmd, @params, @CountChanged = @Count OUTPUT;
IF @TestOnly = 1
BEGIN
SET @cmd = '@Count = ' + CAST(COALESCE(@Count,0) as nvarchar(max)) + ', @Threshold = ' + CAST(@Threshold as nvarchar(max));
RAISERROR (@cmd, 0, 1) WITH NOWAIT;
END
/* If the # of new/changed rows is over the threshold, do the STATISTICS UPDATE */
IF @Count >= @Threshold
BEGIN
SET @cmd = 'UPDATE STATISTICS ' + quotename(@Table) + CASE WHEN @FullScan = 1 THEN ' WITH FULLSCAN' ELSE '' END + ';';
RAISERROR (@cmd, 0, 1) WITH NOWAIT;
IF @TestOnly = 0 EXEC sp_executesql @cmd;
SET @cmd = 'UPDATE StatsThreshold SET LastTimeStamp = (SELECT MAX(' + quotename(@TSField) + ') FROM ' + Quotename(@Table) + ') WHERE TableName = ''' + @Table + ''';';
IF @TestOnly = 1 RAISERROR (@cmd, 0, 1) WITH NOWAIT;
IF @TestOnly = 0 EXEC sp_executesql @cmd;
END
ELSE
BEGIN
SET @cmd = @Table + ' does not require UPDATE STATISTICS';
RAISERROR (@cmd, 0, 1) WITH NOWAIT;
END
END
ELSE
BEGIN
SET @cmd = @Table + ' does not have a TIMESTAMP field.';
RAISERROR (@cmd, 0, 1) WITH NOWAIT;
END
FETCH NEXT FROM cur INTO @Table, @Threshold;
END
CLOSE cur;
DEALLOCATE cur;
END
GO
This stored procedure could be added to a SQL Server Agent job and ran nightly, or weekly or whatever fits your needs.
I have tested the above code on a test database, however you assume all risks and agree to only USE AT YOUR OWN RISK! Specifically, the code will add a TIMESTAMP field to ALL tables in your database. This MAY CAUSE YOU GREAT PAIN if your system does not allow fields to be added to tables without reworking other code.
