September 11, 2003 at 8:08 am
hi all,
I have many questions on sql server statistics.
When I drop these statistics, the data and index storage don't decrease. (result of sp_spaceused command)
I use the dbpages column of sysindexes table to get the amount of data in KB used by _WA_ statistics. I don't understand where are stored these data.
Where are stored the sql server statistics '_WA_..' ?
thanks for your help
September 11, 2003 at 7:13 pm
Hi there
Check sysindexes carefully and the statblob column.
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
September 12, 2003 at 2:00 am
hi,
To know the amount of statistics '_WA_', I use :
select o.name as Nom_Table, i.name as Nom_Index, i.indid, i.dpages * 8 as Volume_Ko
from sysobjects o, sysindexes i
where i.name like '_WA%'
and o.id = i.id and o.type = 'U'
order by o.name
COMPUTE SUM(i.dpages * 8 )BY o.name
COMPUTE SUM(i.dpages * 8 )
that is correct ?
thanks for your help
September 29, 2003 at 1:26 pm
quote:
To know the amount of statistics '_WA_', I use :
select o.name as Nom_Table, i.name as Nom_Index, i.indid, i.dpages * 8 as Volume_Ko
from sysobjects o, sysindexes i
where i.name like '_WA%'
and o.id = i.id and o.type = 'U'
order by o.name
COMPUTE SUM(i.dpages * 8 )BY o.name
COMPUTE SUM(i.dpages * 8 )
that is correct ?
No. The best approximation I can think of is:
SELECT SUM(DATALENGTH(statblob))/1024 KBytes
FROM sysindexes
WHERE status & 0x800000 = 0x800000
This will be an approximation, as you don't really know how the data is chunked into pages.
--Jonathan
--Jonathan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply