Can anybody explain SQL Server 2005 table size

Question

I'm working with SQL Server 2005 and have a single table:

int Code1,
int Code2, 
real Val1,
real Val2,
real Val3,

Code1 & Code2 serve as a primary key and are part of the clustered index (only one index). Each parameter occupies 4 bytes (each row occupies 20 bytes).

There are 24.5 million records in the table, fill factor is 100%, the index occupies 2MB, and the page size is 4k.

Assuming that each page is filled by as many records as possible then each page should hold 204 records which are 4080 bytes (%99.6 page fill)

So, I would expect that the size the table occupies on the disk would be around 500MB (20 Bytes * 24.5 M records) but a fact is that the table occupies 773MB.

I tried shrink and reindex but the table size didn't change.

I'm no SQL expert, can anybody help?

Answer 1

First of all, the page size in SQL Server is 8 KB, and it cannot be changed; it's a system setting which you have no control over.

Of these 8192 bytes, you as a user have roughly 8060 at your disposal - the rest is headers and control structures and so forth.

So in your case, with each row occupying 20 bytes, you should be able to get 403 rows per page. So that gives you roughly 60'795 data pages at 8 KB a piece = 486 MB.

However: for performance reasons, SQL Server doesn't allocate each page as its needed - SQL Server will pre-allocate a given size for your database. When you create a new database in SQL Server Management Studio, you'll see that by default, SQL Server allocates 3 MB of space, and will increase by 1 MB when more space is needed. These settings are changeable - you didn't mention what they are.

Also, for performance reasons, SQL Server will typically not "return" unused data pages back to the operating system. That's a rather costly operation, and there's a good chance it might need those again some time. The same goes for index pages - if you may have had another index on that table (even just to try something out) and it used a number of pages, those won't be returned to the OS by default.

Also, depending on how the data was inserted into the tables, there might be some "holes" in the data structure - not all pages might be totally up to 100% filled. And to keep balancing the b-tree, SQL Server might even choose to split pages into two even if they're not 100% full yet.

So all in all: yes, theoretically and mathematically, your database should be roughly 486 MB for data and 2 MB for index - but how bad is it really, if the file is 770+ MB in size instead?? Does it really hurt??

---

With this T-SQL script which checks out the DMV (Dynamic Management Views), you can gain very deep and detailed insight into your table index structures, and how many pages are used on each level of the index, and how the fill factors on your data pages are - very useful and helpful to know!

SELECT 
    t.NAME 'Table name',
    i.NAME 'Index name',
    ips.index_type_desc,
    ips.alloc_unit_type_desc,
    ips.index_depth,
    ips.index_level,
    ips.avg_fragmentation_in_percent,
    ips.fragment_count,
    ips.avg_fragment_size_in_pages,
    ips.page_count,
    ips.avg_page_space_used_in_percent,
    ips.record_count,
    ips.ghost_record_count,
    ips.Version_ghost_record_count,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.avg_record_size_in_bytes,
    ips.forwarded_record_count
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN  
    sys.tables t ON ips.OBJECT_ID = t.Object_ID
INNER JOIN  
    sys.indexes i ON ips.index_id = i.index_id AND ips.OBJECT_ID = i.object_id
WHERE
    T.NAME = 'your-table-name-here'
ORDER BY
    AVG_FRAGMENTATION_IN_PERCENT, fragment_count