SQL Server Performance

Table using too much space before shrinking

Discussion in 'Performance Tuning for DBAs' started by evgeny, Jan 24, 2008.

  1. evgeny New Member

    Hi,

    I have a SQL 2000 table that stores two UNIQUEIDENTIFIERs and 1 SMALLINT. New rows are frequently inserted into this table and over time the size grows dramatically. I recently ran the "table space usage" script and it showed about 11,500,000 rows using about 2000 MB. That's about 174 bytes per row. After running DBCC SHRINKDATABASE(..., 10) this table uses a much more reasonable 46 bytes/row - but still, shouldn't it be 34 bytes?
    Could someone explain what's going on here and how I can prevent all this space from being wasted by the table? There are other tables with similar problems, this is just the worst one.

    Thanks in advance for any help!

    Evgeny

  2. alzdba Member

    - Can you post the table's ddl ? (including index definitions, unique constraints)
    - Is there a clustering index declared ? (page splits [:S])
  3. evgeny New Member

    Thanks for the replies.
    Yes, turns out there is a clustered index. It's on a GUID column, so should I replace it with a non-clustered one? Here's what Enteprise Manager generates:
    CREATE TABLE [dbo].[JobSearchResult]
    (
    [setId] [uniqueidentifier] NOT NULL ,
    [rank] [smallint] NOT NULL ,
    [jobAdId] [uniqueidentifier] NOT NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[JobSearchResult] WITH NOCHECK ADD
    CONSTRAINT [PK_JobSearchResult] PRIMARY KEY CLUSTERED
    (
    [setId],
    [rank]
    ) WITH FILLFACTOR = 90 ON [PRIMARY]
    GO

    ... plus a couple of FK constraints, which I'm guessing shouldn't make a difference.

    I ran DBCC UPDATEUSAGE and got this:
    DBCC UPDATEUSAGE: sysindexes row updated for table 'JobSearchResult' (index ID 1):
    DATA pages: Changed from (110087) to (71607) pages.
    USED pages: Changed from (110706) to (72074) pages.
    RSVD pages: Changed from (110705) to (72081) pages.
    The reported space usage then dropped dropped from 864 MB to 563 MB - about 47 bytes/row, which is pretty good. How do I prevent this table from blowing out again, though? I shouldn't have to run DBCC SHRINKDATABASE all the time, right?

  4. alzdba Member

    So your space info was due to bad usage statistics.
    ...It's on a GUID column, so should I replace it with a non-clustered one? ...
    No, just keep in mind a uniqueidentifier doesn't grow linear. New values will hop around in your existing set, so cause page splits.
    Keep in mind to rebuild on a "regular" basis, depenging on how much insert/delete activity there is on the table.
    If heavy load, you may want to consider using a non-default fillfactor.
    Check BOL !
    Also keep in mind to run extra sp_updatestatistics, dbcc updateusage a couple of times a year.
    According to the corrections it makes, you can increase/decrease the frequency for this.
  5. evgeny New Member

    I don't think it was all just bad statistics, though. Initially, the reported usage was over 2000 MB and the actual DB file and backups were much larger, too. After DBCC SHRINKDATABASE both the reported usage and the backup file size reduced drastically. I then I let it run for a few days and then after DBCC UPDATEUSAGE it the reported usage reduced a little more. (The backup size remained about the same, but of course the rest of the DB is changing, too.) It appears to me that it's the SHRINKDATABASE that did most of the work. So the question is, what should I do to avoid having to run that all the time? I'll read about the fillfactor, but anything else? Apart from giving me an accurate report, would DBCC UPDATEUSAGE or sp_updatestatistics actually help keep the data file size and backup size down?
    Thanks again for the help.
  6. alzdba Member

    - A shrinkdatabase is something you don't want to do often on a production system.
    Your database has grown for a reason, sqlserver must have needed the space.
    If you have deleted a mass of data, then maybe it's worth doning so if you run short on disk space.
    -Acutaly the statistics have no influence for the file size at all, but will let sqlserver choose a more optimal access path to your data !
    -It is in your best interest (performance wize) to have up to date statistics.
    Generaly SQLServer will try to serve your query in the most optimal way, based on the available statistics.
    If there are none, or they are way different from the actual distribution of data, you may end up with a very slow system in relation to the data.
    As you can see, after a shrink operation, you should rerun all statistics to have them updated.
    - DBCC DBREINDEX or sp_indexdefrag (less intrusive, but less optimal) will result in speeding up your system. They may also help in keeping your files as small as needed.
    - If you suspect your db is growing more than you predicted, use SQLProfiler to see what's going on.
  7. satya Moderator

    Welcome to the forum!
    Have you updated the statistics on teh table in order to get that figure of space occupied by the rows, as it needs to be done periodically such as DBCC UPDATEUSAGE in order to refresh the values.
    One of the KB articles refers that:
    When you estimate the size of a table or of a database, consider the following information:
    •Each row in the table has an overhead of 6 bytes.
    •Each column in the table has an overhead of 1 byte, plus 1 byte for every 256 bytes of row storage.
    •Fixed-length data types have an overhead of 1 byte for each column, and the overhead is rounded to the next higher byte.
    •Zero-length string columns occupy 1 byte in the row
  8. satya Moderator

Share This Page