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
SELECT
t.NAME AS TableName,
i.name as indexName,
p.[Rows],
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
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
WHERE
t.NAME NOT LIKE 'dt%' AND
i.OBJECT_ID > 255 AND
i.index_id <= 1
GROUP BY
t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
ORDER BY
object_name(i.object_id)
jamesmetcalf74Asked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic 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 : https://technet.microsoft.com/en-us/library/ms190969(v=sql.105).aspx
0
 
Maciej PileckiConnect With a Mentor Database 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.
1
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Look at the index type as well, that might provide useful info.
Also, what does EXEC sys.sp_spaceused show for that table?

SELECT
t.NAME AS TableName,
i.name as indexName,
i.type_desc AS indexType,
p.[Rows],
...
1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Your database might have empty data pages.
0
 
Mark WillsTopic AdvisorCommented:
And, when looking at pages + indexes, here is a handy script
SELECT OBJECT_NAME(p.object_id) AS object_name
       , i.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 https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-partition-stats-transact-sql
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.