SQL Server Performance

why does this table take up so much space?

Discussion in 'General DBA Questions' started by korzon, Apr 14, 2005.

  1. korzon New Member

    I have the following table:
    <code>
    CREATE TABLE [dbo].[TB_SSHSignature] (
    [ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
    [ContentID] [int] NOT NULL ,
    [Signature] [decimal](20, 0) NOT NULL ,
    [Deleted] [bit] NOT NULL
    ) ON [PRIMARY]
    GO

    CREATE INDEX [IX_TB_SSHSignature] ON [dbo].[TB_SSHSignature]([ContentID]) WITH FILLFACTOR = 90 ON [PRIMARY]
    GO

    CREATE INDEX [IX_TB_SSHSignature_1] ON [dbo].[TB_SSHSignature]([Signature]) WITH FILLFACTOR = 90 ON [PRIMARY]
    GO
    </code>

    It has 9,939,359 rows and takes up 1,449,064K which is about 145 bytes per row. As I understand it, it should only take up about 22 bytes per row, as that the the amount of data contained in each row.

  2. satya Moderator

  3. korzon New Member

    Thanks. I ran that stored procedure and these were the results:
    TB_SSHsignature762.55MB

    Half as much as it actually takes.
  4. mmarovic Active Member

    Your table is on heap and may be fragmented. Create clustered index on ID column and let us know if allocation changed. Also check index fragmentation.

Share This Page