Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Used Pages vs Data Pages Expand / Collapse
Posted Thursday, November 15, 2012 4:44 AM

Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129

I executed the following code to determine the size that a certain table takes no the database:
select AS Table_Name,
i.index_id, i.type as i_type, p.rows,
a.total_pages, a.used_pages, a.data_pages, a.type alloc_type
from sys.objects o
inner join sys.indexes i on o.object_id = i.object_id
inner join sys.partitions as p on i.object_id = p.object_id and i.index_id = p.index_id
inner join sys.allocation_units as a on p.partition_id = a.container_id
where o.is_ms_shipped = 0 and 'AlertasUtilizadorLog'
order by, i.type

exec sp_spaceused 'AlertasUtilizadorLog'

I also executed the sp_spaceused to compare both results.
The output was:
Table_Name		index_id	i_type	rows	        total_pages	        used_pages	        data_pages	alloc_type
AlertasUtilizadorLog 0 0 319 57 54 53 1
AlertasUtilizadorLog 0 0 319 209 208 0 2
AlertasUtilizadorLog 2 2 319 4 4 2 1

name rows reserved data index_size unused
AlertasUtilizadorLog 319 2160 KB 2088 KB 40 KB 32 KB

the reserved size is the sum of total_pages.
the data size is the sum of data_pages for alloc_type = 1 and used_pages for alloc_type = 2 and index_type <= 1 (CLUSTERED OR HEAP).
the unused size is the sum of total_pages less the sum of used_pages.
now comes my "problem"... the sum of used_pages for indexes (i_type > 1) is 4 and sp_spaceused says 5...
How is that?! I can get that value, just subtract used_pages to total_pages and I get that value but why hasn't the sum of all indexes have that value?!
Just one more... total_pages or reserved is the size the data occupies on disk... but used_pages isn't suppose to be the data space or does it have "trash" and the real data is data_pages?


If you need to work better, try working less...
Post #1385087
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse