4

We are running into an issue where if we look at the avg row size (0.2kb) x number of rows (200) inserted in one of our table, the size should grow by around 40 KB. However the total used space has increased by around 400 KB. I believe there is additional index size to be considered. However it still should not show this big difference. What possibly should we look at to understand this difference and what is happening?

This is happening with couple of our tables which only do inserts related to data collected from a third party system. It does not do any updates on any rows. Also we have an index on the table on the primary key which is an ID (number) field.

migrated from stackoverflow.com Nov 27 '14 at 11:43

This question came from our site for professional and enthusiast programmers.

  • 2
    Post the exact methods you used to measure the row size and the table size. – Remus Rusanu Nov 27 '14 at 11:43
  • 1
    Also, posting the complete DDL for your table including all indices definitely wouldn't hurt in explaining the cause of it. – Roger Wolf Nov 27 '14 at 13:26
  • And, out of curiosity, what is your fill factor on this table? – RLF Nov 27 '14 at 19:05
  • fill factor, index definition, base table size, if compression is enabled, NULLable fields, and a few other factors to consider. Let us know these variables. – Ali Razeghi Nov 10 '15 at 0:32
1
  1. Check that if you have indexes (clustered/nonclustered, full text). Use sp_spaceused 'your_table_name' to check that you can actually discard indexes.
  2. Check what type of table is used. In your example of 0.2KB=205 Bytes you will have 38 rows per data page if your table is heap and 39 rows per data page if it is clustered table.

Please see below the example:

IF EXISTS (SELECT * FROM sys.tables
            WHERE name = 'sparse_pages')
    DROP TABLE sparse_pages;
GO
CREATE TABLE sparse_pages 
(
KeyField SMALLINT --IDENTITY (1,1) PRIMARY KEY
, Filler VARCHAR(8000) null 
)
GO

SET NOCOUNT ON
INSERT INTO sparse_pages( Filler) values ( REPLICATE('a', 192))
GO 39

-- Average row size now 205 Bytes
WAITFOR DELAY '00:00:03';
GO
SELECT 'This is Heap. Note Data Space'
GO
-- Check table size
sp_spaceused 'sparse_pages'
GO


IF EXISTS (SELECT * FROM sys.tables
            WHERE name = 'sparse_pages')
    DROP TABLE sparse_pages;
GO
CREATE TABLE sparse_pages 
(
KeyField SMALLINT IDENTITY (1,1) PRIMARY KEY
, Filler VARCHAR(8000) null 
)
GO

SET NOCOUNT ON
INSERT INTO sparse_pages( Filler) values ( REPLICATE('a', 192))
GO 39

-- Average row size now 205 Bytes
WAITFOR DELAY '00:00:03';
GO
SELECT 'This is Clustered Index. Note Data Space'
GO
-- Check table size
sp_spaceused 'sparse_pages'
GO
  1. Check for random inserts/updates/deletes in your table. This may be an issue as free space is not reclaimed back. The free space may be wasted during page splits too.

Please see below the example with average row size 205 Bytes and 200 rows (just like in your case). Table data size is 1.57 MB:

IF EXISTS (SELECT * FROM sys.tables
            WHERE name = 'sparse_pages')
    DROP TABLE sparse_pages;
GO
CREATE TABLE sparse_pages 
(
KeyField SMALLINT IDENTITY (1,1) PRIMARY KEY
, Filler VARCHAR(8000) null 
)
GO

Enter the data

SET NOCOUNT ON
INSERT INTO sparse_pages( Filler) values ( REPLICATE('a', 8700))
INSERT INTO sparse_pages( Filler) values ( REPLICATE('a', 192))
GO 200

DELETE FROM sparse_pages 
WHERE LEN(Filler)>300
GO
-- Average row size now 205 Bytes
WAITFOR DELAY '00:00:03';
GO
-- Check table size
sp_spaceused 'sparse_pages'
GO
0

Likely I may be interpreting the data incorrectly however this is what I did :

Ran the following query ( referenced from https://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database) between 2 time intervals. Between the two runs of the query, we compared for the table(s) in question number of rows (RowCounts) that were added and the used space (UsedSpace) to get the space consumed by the added rows. For the time interval I compared we had 200 rows that got inserted and the difference in UsedSpace was around 400KB

To get the size per row, we looked at the estimated execution plan for the same table through SQL Server Management Studio which indicated around 0.2KB per row. Per this 200 rows should add only 40KB.

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name
  • 2
    You know this query is measuring the size of the data and all indexes, right? – Aaron Bertrand Nov 27 '14 at 12:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.