database and tables and table size understanding

I have a query that gives me row count, totalpages, usedpages, totalspace, usedspace, and dataspace of all the tables in my database.

what is the difference between dataspace and usedspace

for example.  i have one table that has the following statistics.  
3.3 gigs of totalspace, 3.3 gigs of usedspace and then only 30 megs of dataspace

why is dataspace so much smaller than used space

below is the query
t.NAME AS TableName, as indexName,
sum(a.total_pages) as TotalPages,
sum(a.used_pages) as UsedPages,
sum(a.data_pages) as DataPages,
(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
(sum(a.data_pages) * 8) / 1024 as DataSpaceMB
sys.tables t
sys.indexes i ON t.OBJECT_ID = i.object_id
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
sys.allocation_units a ON p.partition_id = a.container_id
i.index_id <= 1
t.NAME, i.object_id, i.index_id,, p.[Rows]
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Maciej PileckiDatabase ArchitectCommented:
Total pages = pages reserved by the allocation unit. Due to internal allocation mechanism of SQL Server, not all pages in an allocated extent might actually be used.
Used pages = pages actually used
Data pages = pages storing data, excluding index overhead, IAM pages etc.
Scott PletcherSenior DBACommented:
Look at the index type as well, that might provide useful info.
Also, what does EXEC sys.sp_spaceused show for that table?

t.NAME AS TableName, as indexName,
i.type_desc AS indexType,
Vitor MontalvãoMSSQL Senior EngineerCommented:
Your database might have empty data pages.
Mark WillsTopic AdvisorCommented:
When you create a database, you allocate a size.

That 'size' is basically reserving physical disk space.

To the operating system, the Database is basically a couple of really big files (made up from MDF, LDF, NDF etc)

To SQL Server, it needs somewhere to put data (including indexes) and transaction logs. Thats what the initial allocation of size is all about. And needs to be checked / managed periodically.

All that allocated space is logically divided into pages (8k is a page size) and SQL server reads or writes an entire page.

Those pages (8 contiguous pages) are grouped into Extents. Every Page belongs to (is stored in) an extent.

As you start using your database, adding data, you start using some of that allocated size (space).

When you create a table and add a row, the database engine will grab some of those pages. Initially it will generate a page for the table and an extent (ie first insert gives 9 pages). then as that fills up, it will then grab another extent. It is pretty much demand driven and the Engine will go find some available space.

While the above is a bit of an oversimplification, You need to be aware there are quite a few things that can use a page ... Clustered Index, Large row sizes, Indexes etc.

Thats what SpaceUsed tells us - how much of my allocated disk is actually being used.

But for a table, the more important measure are not the MB columns, they are the Pages Column.

That gives us a more detailed insight into what the Table is doing (along with rowcount) than space used in terms of megabytes.

Now what is dataspaceused - it is the actual data component only

Lets say you create a table with a clustered index. That will store the data on the clustered index page, so number of pages will be more than just 'data'

Lets say your table has a lot of updates, deletes inserts, the old rows are flagged as 'not used' as the new replacement rows are inserted. The 'data' is the new stuff.

Lets say your table has some large data elements, then they may end up in 'ROW_OVERFLOW_DATA' with a pointer to that location back on the original page.

So, there are a lot of different things that can give you a misleading picture of what is happening in a table - you really need to be looking at page counts, and indexes. Use the MB columns as a total to help analyse the DB.

Just have a look at the number of different page types in a SQL Database :

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
And, when looking at pages + indexes, here is a handy script
SELECT OBJECT_NAME(p.object_id) AS object_name
       , AS index_name
       , ps.in_row_used_page_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p  ON ps.partition_id = p.partition_id
INNER JOIN sys.indexes i  ON p.index_id = i.index_id AND p.object_id = i.object_id

Open in new window

Have a read of
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.