Here's the stored procedure that I use to update statistics on a database. I don't use sp_updatestats as it doesn't allow you to change the number of rows to sample. I typically run it weekly on the databases that I support.

CREATE PROC isp_UPDATE_STATISTICS
(@dbName sysname, @sample int)
AS

SET NOCOUNT ON

DECLARE @SQL nvarchar(4000)
DECLARE @ID int
DECLARE @TableName sysname
DECLARE @RowCnt int

CREATE TABLE ##Tables
(
TableID INT IDENTITY(1, 1) NOT NULL,
TableName SYSNAME NOT NULL
)

SET @SQL = ''
SET @SQL = @SQL + 'INSERT INTO ##Tables (TableName) '
SET @SQL = @SQL + 'SELECT [name] '
SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysobjects '
SET @SQL = @SQL + 'WHERE xtype = ''U'' AND [name] <> ''dtproperties'''

EXEC sp_executesql @statement = @SQL

SELECT TOP 1 @ID = TableID, @TableName = TableName
FROM ##Tables
ORDER BY TableID

SET @RowCnt = @@ROWCOUNT

WHILE @RowCnt <> 0
BEGIN

SET @SQL = 'UPDATE STATISTICS ' + @dbname + '.dbo.[' + @TableName + '] WITH SAMPLE ' + CONVERT(varchar(3), @sample) + ' PERCENT'

EXEC sp_executesql @statement = @SQL

SELECT TOP 1 @ID = TableID, @TableName = TableName
FROM ##Tables
WHERE TableID > @ID
ORDER BY TableID

SET @RowCnt = @@ROWCOUNT

END

DROP TABLE ##Tables

GO[@more@]

ITPub博客

UPDATE STATISTICS stored procedure

最新文章