SQL Server Performance Forum – Threads Archive
why does this table take up so much space?I have the following table:
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]
</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.
http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_02_2248.asp http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=4924 …for your information. Satya SKJ
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.
Thanks. I ran that stored procedure and these were the results:
TB_SSHsignature762.55MB Half as much as it actually takes.
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.