What Maintenance task am I missing?

  • Hello

    I have a few tables that seem to be using an excessively high amount of disk space

    They also return results inefficiently

    An example:

    I have a table called E_DIM_POLICY

    This has around 450k rows in it

    It has 8 columns all varchar(255) datatypes

    If I look at the datasize (sp_spaceused) it's coming in at 11.8GB!

    If I copy this table to another database, the size is (a more realistic) 176MB

    Question is, why is it taking up so much space?

    Also, am I missing something from my standard, fairly simplistic, maintenance activities

    I have 2 maintenances plans

    One that that verifies DB Integrity and Backs up DAILY

    One that rebuilds indexes (handles indexes and statistics) WEEKLY

    Thanks

    Damian.

    - Damian

  • What indexes does that table have?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • In this instance, there are no indexes on the table

    - Damian

  • Then that's your problem, not the maintenance. With no indexes at all, no clustered index, the table is a heap. Firstly index rebuilds won't touch heaps, second deletes don't deallocate empty pages unless the delete runs with a table lock. Hence you have a table that's mostly full of empty space.

    I've seen an empty table (0 rows) that when read processed 600MB of pages.

    At minimum put a clustered index on that, and every other table in your DB. Consider other indexes too.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks for the advice Gail. I'll take a look at the indexes

    - Damian

  • Perfect

    Added a clustered index

    Rebuilt stats for that table

    Looked at space used and it's what I would expect

    Will look at other tables

    Thanks

    Damian.

    - Damian

  • You don't need to update statistics. A freshly built index will have up to date stats already.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply