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
To update statistics that are out of date, execute the command
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
where database_id > 4
Select @dbID = MIN(databaseID)
While @dbID is not NULL
set @sql = 'Use [' + @db + ']' + CHAR(13) + 'go ' + CHAR(13)
set @sql = @sql + 'exec sp_updatestats' + CHAR(13) + 'go '
drop table #t