Are Your Database Statistics Fresh?

 

SQL maintains a vast amount of data – or statistics – about the content of each object in a database. The statistics can become stale if they have not been updated very often, or if a large number of changes have occurred within the database. As the statistics become less useful, the time for running queries can increase dramatically.

In Production systems, statistics should be updated during the usual maintenance window to ensure that the metadata is fresh.

To see how fresh the statistics are for one object, run:

DBCC SHOW_STATISTICS ( 'CCAuditSessionType' ,CCAuditSessionType_PK)

 

If you need to see the statistics for all databases, run this instead:

select STATS_DATE(o.object_id,s.stats_id) as StatsDate,o.name as TableName, s.name as StatsName, auto_created, user_created, no_recompute

from sys.stats s

join sys.objects o on s.object_id=o.object_id

where o.type='U'

 

To update statistics that are out of date, execute the command

exec sp_updatestats

on each database on the server that needs to have its statistics updated.

Use the following query to generate a script to update the statistics on all databases

declare @db varchar(30)

, @dbID int

, @sql varchar(max)

 

create table #t

(DbName varchar(30), databaseID int)

 

Insert #t (DbName, databaseID)

select [name], database_id

from sys.databases

where database_id > 4

 

Select @dbID = MIN(databaseID)

from #t

 

While @dbID is not NULL

BEGIN

   select @db=DbName

     from #t

    where databaseID=@dbID

 

   set @sql = 'Use [' + @db + ']' + CHAR(13) + 'go ' + CHAR(13)

   set @sql = @sql + 'exec sp_updatestats' + CHAR(13) + 'go '

 

   PRINT @sql

 

   Select @dbID=min(databaseID)

     from #t

    where databaseID>@dbID

END

 

drop table #t

Copy and paste the printed output from your result set into the query portion of a SQL Server Agent job and this will ensure that the statistics are updated for all databases on a regular schedule. NOTE: the query above excludes the system databases.