Script to UPDATE STATISTICS with time window

I recently spent some time troubleshooting odd query plans and came to the conclusion that we needed better statistics.  We’ve been running spupdatestats but apparently it wasn’t sampling enough of the table to get us what we needed.  I have a pretty limited window at night where I can hammer the disks while this runs. 

The script below just calls UPDATE STATITICS on all tables that “need” updating.  It defines need as any table whose statistics are older than the number of days you specify (30 by default).  It also has a throttle so it breaks out of the loop after a set amount of time (60 minutes).  That means it won’t start processing a new table after this time but it might take longer than this to finish what it’s doing.  It always processes the oldest statistics first so it will eventually get to all of them.  It defaults to sample 25% of the table.  I’m not sure that’s a good default but it works for now.  I’ve tested this in SQL Server 2005 and SQL Server 2008.  I liked the way Michelle parameterized her re-index script and I took the same approach.

CREATE PROCEDURE dbo.UpdateStatistics
(

@timeLimit smallint = 60
,@debug bit = 0
,@executeSQL bit = 1
,@samplePercent tinyint = 25
,@printSQL bit = 1
,@minDays tinyint = 30
)
AS
/*************************************************************
*
* Copyright Bill Graziano 2010
*
**************************************************************/

SET NOCOUNT ON;

PRINT '[ ' + CAST(GETDATE() AS VARCHAR(100)) + ' ] ' + 'Launching...'


IF OBJECT
ID('tempdb..#status') IS NOT NULL
DROP TABLE #status;

CREATE TABLE #status
(
databaseID INT
, databaseName NVARCHAR(128)
, objectID INT
, pagecount INT
, schemaName NVARCHAR(128) Null
, objectName NVARCHAR(128) Null
, lastUpdateDate DATETIME
, scanDate DATETIME

CONSTRAINT PK
statustmp
PRIMARY KEY CLUSTERED(databaseID, objectID)
);

DECLARE @SQL NVARCHAR(MAX);
DECLARE @dbName nvarchar(128);


DECLARE @databaseID INT;
DECLARE @objectID INT;
DECLARE @schemaName NVARCHAR(128);
DECLARE @objectName NVARCHAR(128);

DECLARE @lastUpdateDate DATETIME;
DECLARE @startTime DATETIME;

SELECT @startTime = GETDATE();

DECLARE cDB CURSOR
READ
ONLY
FOR select [name] from master.sys.databases where databaseid > 4


OPEN cDB

FETCH NEXT FROM cDB INTO @dbName
WHILE (@@fetch
status <> -1)
BEGIN
IF (@@fetchstatus <> -2)
BEGIN

SELECT @SQL = '
use '
+ QUOTENAME(@dbName) + '

select
DB
ID() as databaseID
, DBNAME() as databaseName
,t.object
id
,sum(usedpagecount) as pagecount
,s.[name] as schemaName
,t.[name] AS objectName
, COALESCE(d.stats
date, ''1900-01-01'')
, GETDATE() as scanDate
from sys.dmdbpartitionstats ps
join sys.tables t on t.object
id = ps.objectid
join sys.schemas s on s.schema
id = t.schemaid
join (
SELECT
object
id,
MIN(statsdate) as statsdate
FROM (
select
objectid,
stats
date(objectid, statsid) as statsdate
from
sys.stats) as d
GROUP BY object
id
) as d ON d.objectid = t.objectid
where ps.rowcount > 0
group by s.[name], t.[name], t.object
id,
COALESCE(d.statsdate, '
'1900-01-01'')
'


SET ANSI
WARNINGS OFF;

Insert #status
EXEC ( @SQL);

SET ANSIWARNINGS ON;


END
FETCH NEXT FROM cDB INTO @dbName
END

CLOSE cDB
DEALLOCATE cDB




DECLARE cStats CURSOR
READ
ONLY
FOR SELECT
databaseID
, databaseName
, objectID
, schemaName
, objectName
, lastUpdateDate
FROM #status
WHERE DATEDIFF(dd, lastUpdateDate, GETDATE()) >= @minDays
ORDER BY lastUpdateDate ASC, pagecount desc, [objectName] ASC


OPEN cStats

FETCH NEXT FROM cStats INTO @databaseID, @dbName, @objectID,
@schemaName, @objectName, @lastUpdateDate

WHILE (@@fetch
status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN

IF DATEDIFF(mi, @startTime, GETDATE()) > @timeLimit
BEGIN
PRINT '[ ' + CAST(GETDATE() AS VARCHAR(100)) + ' ] ' +
'* Time Limit Reached *';
GOTO DONE;
END

SELECT @SQL = 'UPDATE STATISTICS ' +
QUOTENAME(@dBName) + '.' +
QUOTENAME(@schemaName) + '.' +
QUOTENAME(@ObjectName) +
' WITH SAMPLE ' + CAST(@samplePercent AS NVARCHAR(100)) + ' PERCENT;';

IF @printSQL = 1
PRINT '[ ' + CAST(GETDATE() AS VARCHAR(100)) + ' ] ' +
@SQL +
' (Last Updated: ' + CAST(@lastUpdateDate AS VARCHAR(100)) + ')'

IF @executeSQL = 1
BEGIN
EXEC (@SQL);
END


END
FETCH NEXT FROM cStats INTO @databaseID, @dbName, @objectID,
@schemaName, @objectName, @lastUpdateDate
END

DONE:

CLOSE cStats
DEALLOCATE cStats

PRINT '[ ' + CAST(GETDATE() AS VARCHAR(100)) + ' ] ' + 'Completed.'
GO