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.

Query to Pull Database File Information

This query will list the name, size and location of all files for all databases. This is handy for checking and documenting database server configuration to confirm whether the server follows our recommended best practices.

set nocount on

declare @sql varchar(max), @sql2 varchar(max), @name varchar(100)

if object_id('tempdb..#t') is not null drop table #t

create table #t (DbName varchar(30), LogicalName varchar(30), FileName varchar(100), sizeMB int, UsedSpaceMB int, growthMB int, is_percent_growth bit)

set @sql = ' substring(name,1,30) as LogicalName,substring(physical_name,1,75) as FileName,'

set @sql = @sql + 'size * 8192./1024/1024 as SizeMB,sum(a.total_pages * 8192./1024/1024 ) as UsedSpaceMB, '

set @sql = @sql + 'growth * 8192./1024/1024 as growthMB, is_percent_growth from '

declare c cursor for

select name from sys.databases where database_id > 4

open c

fetch next from c into @name

while @@fetch_status=0begin

set @sql2 = @sql + @name + '.sys.database_files df left join '

set @sql2 = @sql2 + @name + '.sys.allocation_units a on df.data_space_id=a.data_space_id left join '

set @sql2 = @sql2 + @name + '.sys.partitions p on p.partition_id = a.container_id '

set @sql2 = @sql2 + 'group by df.name, df.physical_name, growth, is_percent_growth, df.size'

begin try exec ('insert #t select ''' + @name + ''' as DbName, ' + @sql2 ) end try begin catch end catch

fetch next from c into @name

end

close c

deallocate c

select * from #t

I’m sure that there are other ways to pull this data, however, in some environments your permissions may restrict you from using any method other than this to pull the data.