posts - 219, comments - 411, trackbacks - 27

My Links

Advertisement

News

Follow billgraziano on Twitter

Article Categories

Archives

Post Categories

Consulting

SQL Server

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 sp_updatestats 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
, page_count INT
, schemaName NVARCHAR(128) Null
, objectName NVARCHAR(128) Null
, lastUpdateDate DATETIME
, scanDate DATETIME

CONSTRAINT PK_status_tmp
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 database_id > 4


OPEN cDB

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

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

select
DB_ID() as databaseID
, DB_NAME() as databaseName
,t.object_id
,sum(used_page_count) as page_count
,s.[name] as schemaName
,t.[name] AS objectName
, COALESCE(d.stats_date, '
'1900-01-01'')
, GETDATE() as scanDate
from sys.dm_db_partition_stats ps
join sys.tables t on t.object_id = ps.object_id
join sys.schemas s on s.schema_id = t.schema_id
join (
SELECT
object_id,
MIN(stats_date) as stats_date
FROM (
select
object_id,
stats_date(object_id, stats_id) as stats_date
from
sys.stats) as d
GROUP BY object_id
) as d ON d.object_id = t.object_id
where ps.row_count > 0
group by s.[name], t.[name], t.object_id,
COALESCE(d.stats_date, '
'1900-01-01'')
'


SET ANSI_WARNINGS OFF;

Insert #status
EXEC ( @SQL);

SET ANSI_WARNINGS 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, page_count 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

Print | posted on Thursday, December 02, 2010 5:21 AM | Filed Under [ SQL Server Stuff Utilities ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET