Script to gather and update all Statistics for all the indexes for given databases

Posted: August 17, 2011 in Sqlserver

This script is to collect the stats information for a given database on all the indexes part. This query will help you to debug the performance issue related to slowness of database.

The very first step we do when it comes to database tunning is to check the updated statistics on all the indexes and update them if they are old.

——————————————Script——————————————————
Use
GO
Select db_id() as dbid,
case
when indid IN (0, 1) then convert (char (12), rows)
else (select rows from dbo.sysindexes i2 where i2.id = i.id and i2.indid in (0,1))
end as rowcnt,
case
when indid IN (0, 1) then rowmodctr
else convert (bigint, rowmodctr) + (select rowmodctr from dbo.sysindexes i2 where i2.id = i.id and i2.indid in (0,1))
end as row_mods,
case rows when 0 then 0 else convert (bigint,
case
when indid IN (0, 1) then convert (bigint, rowmodctr)
else rowmodctr + (select convert (bigint, rowmodctr) from dbo.sysindexes i2 where i2.id = i.id and i2.indid in (0,1))
end / convert (numeric (20,2), (select case convert (bigint, rows) when 0 then 0.01 else rows end from dbo.sysindexes i2 where i2.id = i.id and i2.indid in (0,1))) * 100)
end as pct_mod,
convert (nvarchar, u.name + ‘.’ + o.name) as objname,
case when i.status&0x800040=0x800040 then ‘AUTOSTATS’
when i.status&0x40=0x40 and i.status&0x800000=0 then ‘STATS’
else ‘INDEX’ end as type,
convert (nvarchar, i.name) as idxname, i.indid,
stats_date (o.id, i.indid) as stats_updated,
case i.status & 0x1000000 when 0 then ‘no’ else ‘*YES*’ end as norecompute,
o.id as objid , rowcnt, i.status
from dbo.sysobjects o, dbo.sysindexes i, dbo.sysusers u
where o.id = i.id and o.uid = u.uid and i.indid between 1 and 254 and o.type = ‘U’
order by pct_mod desc, convert (nvarchar, u.name + ‘.’ + o.name), indid
GO
———————————-Check the Outpu————————————

Once you see old statistics from the above query then you have to update them idvidually or all at once.

To update all the Stats at one shot please use the below query.

———————————Script to update all Stats————————-

EXEC sp_MSforeachtable ‘UPDATE STATISTICS ? WITH FULLSCAN’

————————————–END———————————————

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s