Archive for August, 2011

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