Friday, July 29, 2011

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, as TableName, 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
   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

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.

No comments:

Post a Comment

Power BI: M Query Basics (Part 2)

This is the second part of my series on Power BI M Query Basics. In Part 1 , I defined M Query and talked about the structure of how an M Qu...