statistics _WA_

  • 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

  • Hi there

    Check sysindexes carefully and the statblob column.

    Cheers

    Ck

    Chris Kempster

    http://www.chriskempster.com

    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"

  • 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

  • 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