Update Statistics to Improve performance

You can improve query performance by using UPDATE STATISTICS or the stored proceduresp_updatestats to update statistics more frequently than the default updates.


Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile. We recommend not updating statistics too frequently because there is a performance tradeoff between improving query plans and the time it takes to recompile queries. The specific tradeoffs depend on your application. UPDATE STATISTICS can use tempdb to sort the sample of rows for building statistics.


USE AdventureWorks2008R2;

GO
EXEC sp_updatestats 

TO CREATE STATISTICS SCRIPT FOR ALL DATABASES on a DATABASE SERVER:

CREATE PROCEDURE 
SPUpdateStats 
AS 

Set Nocount on 
Declare db Cursor For 

Select distinct(Databasename) as Name FROM T_TableReindexing(nolock) 

--Select name from master.dbo.sysdatabases where name in ('dbmonitor') and 
name 
--not in ('master','TempDB', 'msdb', 'model') 

Declare @dbname 
varchar(60) 
Declare @execmd nvarchar(150) 

Open db 
Fetch Next 
from db into @dbname 
While @@Fetch_status=0 
begin 
if @dbname is null 

Begin 
Print 'null Value' 
Return 
end 
else 
Begin 
PRINT 
'###########################################################################' 

PRINT 'Update Statistics in ' + @dbname 
SELECT @execmd = 'USE ' + 
@dbname + ' EXEC sp_updatestats' 
Print (@execmd) 
PRINT '' 
End 

Fetch Next from db into @dbname 
end 
Close db 
Deallocate 
db