Skip to content

Tables and space used by them

by on April 28, 2011

This came up when a dear colleague of mine came to her boss and told him that even though the table she just created had only one row, “is taking up much more space than needed” – this is the actual quote.
Let see what she meant.

CREATE
TABLE dbo.Table_1
(

large_column CHAR(8000)
NULL

)


ON

[PRIMARY]

GO

I must apologize for the very creative names I’m giving to my tables in my examplesJ. Okay, so now I have one table with no data in it. In other words, it should not hold any space in the database, right?

There’s one way to tell this (more, actually, but one this is the one that I’m presenting here, since it’s not commonly used), by using sp_spaceused. If I use this stored procedure, I get the following:

EXEC
sp_spaceused
Table_1

And the result:

Now let’s insert exactly one record:

INSERT
INTO Table_1 VALUES (REPLICATE
(‘1’, 8000));

Now let’s execute sp_spaceused again:

EXEC
sp_spaceused
Table_1

And for the result:

So I inserted roughly 8 KB and I already used 16 KBJ.

There’s nothing mysterious here, really, and the main idea is this: the basic unit of storage for SQL Server – and any RDBMS for that matter is a page, which, for SQL Server, can hold roughly 8 KB of data. A logical grouping of 8 pages is called an extent, and the extent comes in two flavors: a mixed extent (at least two objects share pages from that extent) and a uniform extent (all the pages belong to a particular object).

Armed with this knowledge, this is what happens when you actually insert a row in the table:

  1. The table will need a special page, called IAM page (Index Allocation Map). This particular page will keep track of all the pages that will be allocated to the table, and it is the index_size column in our case (see here for further details).
    Truth be told, in our particular case it does not hold any information about indexes, since there are no indexes present on our table, but that’s ok, since our “table” is not quite a table, but a structure called heap.
  2. Only after that IAM page is created, we can actually insert a record into our “table”. Of course, this means that another page is needed.

Now you know why your table (heap, really) holds much more storage than actually there’s data.

Please bear in mind that this is a mini-post, so to speak, since I did not cover anything related to the allocation itself (GAM, SGAM, PFS pages, and so on), but I’ll cover that in a not too distant post.

From → structures

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: