November 7, 2005 at 2:20 pm
HI. What is the easiest method to run update statistics on an entire database?
Thank you,
Juanita
November 7, 2005 at 2:36 pm
USE MyDB
EXEC sp_updatestats
November 7, 2005 at 2:38 pm
THANKS !!!! PERFECT!
Juanita
November 8, 2005 at 7:29 am
That is true but please be aware that sp_updatestats does not do a fullscan of the rows - it instead does sampling of the records. For tables and indexes that undergo a lot of changes, you would be better off by selecting a higher sampling ratio. Look at the "UPDATE STATISTICS" command in BOL for more information if you need to do it. You can then wrap it in a loop and run based on conditional logic as you deem fit.
Something like:
CREATE PROC USP_UPD_STATS
(
@PCNT VARCHAR(3)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @TABLE_CATALOG VARCHAR(50), @TABLE_SCHEMA VARCHAR(20), @TABLE_NAME VARCHAR(50), @COMMAND NVARCHAR(400)
DECLARE SQLSTMT CURSOR FOR SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME
OPEN SQLSTMT
FETCH NEXT FROM SQLSTMT INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @COMMAND = 'UPDATE STATISTICS '+@TABLE_CATALOG+'.'+@TABLE_SCHEMA+'.['+@TABLE_NAME+'] WITH SAMPLE '+@PCNT+' PERCENT, ALL'
EXEC SP_EXECUTESQL @COMMAND
FETCH NEXT FROM SQLSTMT INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
END
CLOSE SQLSTMT
DEALLOCATE SQLSTMT
SET NOCOUNT OFF
END
GO
And then schedule it via a job and pass in the percentage that you want to pass in. If this needs to be done for selective tables, modify your SQL statement accordingly.
Hth
November 8, 2005 at 8:24 am
If you want a thorough 'cleaning' run this against each db:
SET ARITHABORT ON EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'
November 8, 2005 at 10:50 am
What about adding:
EXEC sp_MSforeachtable 'sp_recompile ?'
Otherwise your execution plans will not take advantage of the new statistics ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
January 19, 2006 at 7:24 am
One thing about this, and to the experts it may go without saying. Make sure you allow plenty of time for this to run, as depending on the size of your tables, the FULLSCAN parameter will take quite a while.
I'm having performance problems with a database I restored from an old SQL Server to a new one (both running 2000) and am trying the UPDATE WITH FULLSCAN on the entire database as an alternative to the auto update statistics switch in the database (which is also turned on). The database is almost 5GB in size, maybe not huge in the grand scheme of things, but it's at 23 minutes and still going, and I would expect, from reading other articles here and in deja.com, that it will take much, much longer, but it's not a production system yet, and it appears to be well worth my time.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply