SQL Server Performance

DBCC ShowFileStats v. sp_SpaceUsed

Discussion in 'General DBA Questions' started by AspiringGeek, May 26, 2004.

  1. AspiringGeek New Member

    Greetings, I have a situation regarding unused v. unallocated space.

    When viewing my db size in EM TaskPad view, I used Profiler to figure out that the bar graph is being fed data by the undocumented command DBCC ShowFileStats. It#%92s a very cool thing. I was able with relative ease to match up the command#%92s output with the bar graph. The space allocated by the OS to SQL Server matches up with the DB size, and within each filegroup I could see how much unused space was available. That turned out to be about 20% of our 300 GB DB.

    We#%92re going to start manually growing our files, but for now, we use AutoGrow. We do NOT use AutoShrink. Of course, these features work at the filegroup level.

    Then I used sp_SpaceUsed to examine some individual objects. We typically defrag the objects once each week. It turns out that the unused space for some of the objects is several GBs each. Looking at three of our biggest objects, I found several dozen GB of unused space--more than the free space reported by DBCC ShowFileStats for the entire DB.

    Here#%92s what I#%92m thinking is going on. The free space reported by DBCC ShowFileStats is completely independent of the unused space at the object level. Every week when we defrag, purge old rows, etc., the space that#%92s liberated still belongs to the individual objects.

    If we were to use DBCC ShrinkFile and target the filegroups to which these big objects belong, we could recover this object-level free space and increased the free space reported by DBCC ShowFileStats.

    First of all, is my analysis correct?
    Second, can I reclaim space at the object level, or is the file level as granular as I can get (I#%92d like to leave a little free space in the file, but might want to target a few of the big objects).
    Third, is DBCC ShrinkFile contentious; in other words, when space is being reclaimed from a table, is a table lock applied. Is the operations pretty quick for GB of space (like truncating a log) or does it take a while?

    Thanks for your help!




  2. Raulie New Member

    aspiringGeek,<br /><br />Well very breifly TRUNCATING the log does not reduce the size of a physical log file only reduces the logical size of the log, you must issue DBCC SHRINKFILE to reduce the physical size of the transaction log. So you can't get around from using DBCC SHRINKFILE or DATABASE.<br /><br />Like with all DBCC commands you should run them when user activity is at a minimum if possible, as all DBCC commands are resource intensive. <br /><br />By the way when performing your analysis using DBCC ShowFileStats or sp_spaceused did you run DBCC UPDATEUSAGE ???<br /><br />DBCC UPDATEUSAGE BOL: <br />Reports and corrects inaccuracies in the sysindexes table, which may result in incorrect space usage reports by the sp_spaceused system stored procedure. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]
  3. satya Moderator

    I believe SHOWFILESTATS pertains to filegroups that means as a whole.
    And SP_SPACEUSED but this doesn't appear to be showing log space or usage anywhere. In fact, it still mixes log space into the data space, though it does gives a breakdown of data v index space allocation.

    As referred by Lazy you must run DBCC UPDATEUSAGE or SP_SPACEUSED with @UPDATEUSAGE='TRUE' to get optimum results.

    Unallocated space means space in the datafile that has not been paged ready for data or that just hasnt had any data written to it yet. Unallocated space is space within the above that hasn't been assigned a purpose.

    Unused is space allocated to various tables, indexes (objects) ... but available.
    Pages are specific to an object


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. AspiringGeek New Member

    Thank you, gents. I'm aware of the update usage options, but left them out of my discussion for brevity. For my purposes, the exact numbers aren't important; the point is I have objects with a LOT of unused space.

    It appears that my theory of DB free space v. object unused space has been validated. I'm a bit surprised that this point has never been clear to me until I stumbled across it empirically.

    So, is there any "easy" way to re-claim this space other than DBCC ShrinkFile or ShrinkDatabase. Particularly, is there an object level command that I can run? I am not interested in a SELECT * INTO, re-building all the indexes, and swapping names. Thanks!
  5. satya Moderator

    I don't see any other statement which can accomodate the information db space re-claim without index rebuild.

    The unused space is allocated depending upon that object density, for instance a table with different data types and I believe it would be consistent across SP/Triggers etc.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page