Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table with 490 M rows and 55 GB of table space, so about 167 bytes per row. The table has three columns: a VARCHAR(100), a DATETIME2(0), and a SMALLINT. The average length of the text in the VARCHAR field is about 21.5, so the raw data should be around 32 bytes per row: 22+2 for the VARCHAR, 6 for the DATETIME2, and 2 for the 16-bit integer.

Note that the space above is data only, not indices. I'm using the value reported under Properties | Storage | General | Data space.

Of course there must be some overhead, but 135 bytes per row seems like a lot, especially for a large table. Why might this be? Has anyone else seen similar multipliers? What factors can influence the amount of extra space required?

For comparison, I tried creating a table with two INT fields and 1 M rows. The data space required was 16.4 MB: 17 bytes per row, compared to 8 bytes of raw data. Another test table with an INT and a VARCHAR(100) populated with the same text as the real table uses 39 bytes per row (44 K rows), where I would expect 28 plus a little.

So the production table has considerably more overhead. Is this because it's larger? I'd expect index sizes to be roughly N * log(N), but I don't see why the space required for actual data to be non-linear.

Thanks in advance for any pointers!

EDIT:

All of the fields listed are NOT NULL. The real table has a clustered PK on the VARCHAR field and the DATETIME2 field, in that order. For the two tests, the first INT was the (clustered) PK.

If it matters: the table is a record of ping results. The fields are URL, ping date/time, and latency in milliseconds. Data is constantly appended, and never updated, but data is deleted periodically to cut it down to just a few records per hour per URL.

EDIT:

A very interesting answer here suggests that, for an index with much reading and writing, rebuilding may not be beneficial. In my case, the space consumed is a concern, but if write performance is more important, one may be better off with flabby indices.

share|improve this question
1  
Heap or clustered and if clustered, which column is the cluster key? –  Mark Storey-Smith Sep 6 '11 at 21:07
1  
Adding to Aaron list of questions... What's the fill factor of the clustered index? Is the table a heap? –  mrdenny Sep 6 '11 at 21:47
3  
No polite way to put this... that's a phenomenally ugly choice for a clustering key. –  Mark Storey-Smith Sep 7 '11 at 15:22
1  
Depends on whether you have any non-clustered indexes and if the choice of key causes fragmentation and/or page splits. Add the stats from sys.dm_db_index_physical_stats to your question, average page density is probably very low. Note it may take a while to run. –  Mark Storey-Smith Sep 7 '11 at 15:58
1  
Done. Out of interest, what was the average page density figure? –  Mark Storey-Smith Sep 7 '11 at 21:21

3 Answers 3

up vote 10 down vote accepted

After discussions in the comments on the original question, it appears in this case the lost space is caused by the choice of clustered key, which has led to massive fragmentation.

Always worth checking the state of fragmentation via sys.dm_db_index_physical_stats in these situations.

Edit: Following update in comments

The average page density (prior to rebuild of the clustered index) was 24%, which fits perfectly with the original question. The pages were only 1/4 full, so the total size was 4x the raw data size.

share|improve this answer

Have the data types changed over time? Have variable-length columns been removed? Have the indexes been defragmented often but never rebuilt? Have a lot of rows been deleted or have a lot of variable-length columns been updated significantly? Some good discussion here: http://sqlblog.com/blogs/kalen_delaney/archive/2006/10/13/alter-table-will-not-reclaim-space.aspx

share|improve this answer
    
I am 97% confident that I haven't changed a data type or removed a field. If I did, it would have been really early on when the table had far fewer rows. There are no deletions or updates, data is only ever appended. –  Jon of All Trades Sep 7 '11 at 15:11
    
Correction: there are deletes, and quite a bit. The table has considerable net growth, so I'd imagine that this space would be quickly reused, though. –  Jon of All Trades Sep 7 '11 at 15:21
    
With lots of deletes the data may or may not be reused. What's the clustering key of the table? Are inserts in the middle of the table or at the end? –  mrdenny Sep 7 '11 at 22:10
    
The clustered key is compound, on the VARCHAR and DATETIME2 fields, in that order. Inserts will be evenly distributed for the first field. For the second field, new values and will always be greater than any existing. –  Jon of All Trades Sep 7 '11 at 22:52

The on-disk structures have overhead:

  • row header
  • null bitmap + pointer
  • variable length column offsets
  • row version pointers (optional)
  • ...

Taking 2 x 4 bytes int columns, you have

  • 4 bytes row header
  • 2 byte pointer to NULL bitmap
  • 8 bytes for 2 int columns
  • 3 bytes NULL bitmap

Wow 17 bytes!

You can the same for your second test table which has more overhead like your original one:

  • 2 bytes for the count of variable-length columns
  • 2 bytes per variable length column

Why the difference? In addition (I won't link to these)

  • have you ever rebuilt indexes to defragment them?
  • deletes do not reclaim space
  • data pages will split if you insert into the middle
  • updates may cause forward pointers (leaves a gap)
  • row overflow
  • removed varchar column without index rebuild or DBCC CLEANTABLE
  • heap or table (heap has no clustered index = records scattered all over)
  • RCSI isolation level (extra 14 bytes per row)
  • trailing spaces (SET ANSI_PADDING is ON by default) in varchar. Use DATALENGTH to checl, not LEN
  • Run sp_spaceused with @updateusage = 'true'
  • ...

See this: SQL Server: How to create a table that fills one 8 KB page?

From SO:

share|improve this answer
    
The 2x4 byte int column sample isn't 100% correct. You'll have 4 byte row header (2 status bytes and 2 bytes for the fixed length data size). Then you'll have 2x4 bytes for the data. Two bytes for the column count and a single byte for the null bitmap, giving a total record length of 15 bytes, not 17. –  Mark S. Rasmussen Sep 7 '11 at 12:07
    
@Mark S. Rasmussen: Where do you get "2 bytes for the fixed length data size"? MSDN? And the null bitmap is always 3 bytes: sqlskills.com/blogs/paul/post/… + msdn.microsoft.com/en-us/library/ms178085%28v=sql.90%29.aspx –  gbn Sep 7 '11 at 13:22
    
Wow, great detail! I accounted for the length field of the VARCHARs in my estimate above, but not for the count of columns. This table has no NULLable fields (should have mentioned that), does it still allocate bytes for them? –  Jon of All Trades Sep 7 '11 at 15:13
    
Would rebuilding indices affect the data part of the space required? Perhaps rebuilding the clustered index would. Inserts do happen in the middle, a lot, though if I swapped the order of the clustering fields that would stop. Most of the rest shouldn't apply in this case, but it's great reference for the general case. I will check out your links. Good stuff! –  Jon of All Trades Sep 7 '11 at 15:16
1  
@gbn The 2 bytes for the fixed length data size is part of the 4 byte row header that you mention. This is the pointer that points to the end of the fixed data length portion / beginning of column count/null bitmap. The NULL bitmap is not always three bytes. If you include the column count, then it will be a minimum of three bytes, but may be more - I split the bitmap and the column count in my description. Also, the NULL bitmap is not always present, though it will be in this case. –  Mark S. Rasmussen Sep 7 '11 at 18:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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