SQL Server Performance

auto stats sizes from sp_msindexspace

Discussion in 'Performance Tuning for Hardware Configurations' started by hkim1, Dec 10, 2003.

  1. hkim1 New Member

    Folks,

    I am very puzzled by the auto stats size values that are being given from 'sp_msindexspace'.

    I have a very small table (ie. 896KB data and 632KB index size), however the auto stats of which I have 4, each state that they have size = 114575168KB!

    This doesn't make any sense to me -- can anyone tell me what this size means? Also, can someone tell me how much space these auto stats take-up in the database and where they are allocated?

    Much thanks!
    Hei-Jung.

    hkim
  2. ChrisFretwell New Member

    The sp_msxxxxx procs are considered undocumented and use at your own risk comments (we all use them). this is one of the ones that could do with an update.

    In the sysindexes table, autostat tables all have reserved and used values of 2097236. I dont know why, but they do. this is why they will inflate your results. If you dont need the size of an individual index, then use sp_spaceused on your table. The other option is to modify the sp_msindexspace to remove the statistic indexes from the calculation. Your results would not be 100% accurate, but its better than what you're getting.

    chris

Share This Page