jeudi 4 décembre 2014

space usage on sys.allocation_units and sp_spaceused


It is a known fact that the DMVs dont hold accurate information regarding number of pages and count of rows. However, when you have the stats updated, I can't see why they wouldn't.


I am working on a monitoring tool, want to know disk size of each index and data, etc. Eventually I would like to find the right fill factor, and other things etc.


The space used by my function and the old sp_spaceused differs a little bit on the space usage, but not on record count.


Can you see if there is anything missing in my select?


this is the sp_spaceused (then I convert the numbers in MB):



sp_spaceused 'tblBOrderRelationship'
go

select 318008/1024.00 AS reserved,
140208/1024.00 AS data,
177048/1024.00 AS index_size,
752/1024.00 AS unused


enter image description here


But when I run my select, code below\picture below, I get slightly different figures.



SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
schema_name(t.schema_id) as SchemaName,
t.NAME AS TableName,
t.type_desc,
t.is_ms_shipped,
t.is_published,
t.lob_data_space_id,
t.filestream_data_space_id,
t.is_replicated,
t.has_replication_filter,
t.is_merge_published,
t.is_sync_tran_subscribed,
--t.is_filetable,
i.name as indexName,
i.type_desc,
i.is_unique,
i.is_primary_key,
i.is_unique_constraint,
i.fill_factor,
i.is_padded,


sum(p.rows) OVER (PARTITION BY t.OBJECT_ID,i.index_id) as RowCounts,
sum(a.total_pages) OVER (PARTITION BY t.OBJECT_ID,i.index_id) as TotalPages,
sum(a.used_pages) OVER (PARTITION BY t.OBJECT_ID,i.index_id) as UsedPages,
sum(a.data_pages) OVER (PARTITION BY t.OBJECT_ID,i.index_id) as DataPages,

(sum(a.total_pages) OVER (PARTITION BY t.OBJECT_ID,i.index_id) * 8) / 1024 as TotalSpaceMB,
(sum(a.used_pages) OVER (PARTITION BY t.OBJECT_ID,i.index_id) * 8) / 1024 as UsedSpaceMB,
(sum(a.data_pages) OVER (PARTITION BY t.OBJECT_ID,i.index_id) * 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 T.NAME = 'tblBOrderRelationship'


the figures


the figures


the bigger picture, including the index names the bigger picture, including the index names


Now doing some calculations to check the results:



--==================================
-- the figures from sp_spaceused
--==================================
select 318008/1024.00 AS reserved,
140208/1024.00 AS data,
177048/1024.00 AS index_size,
752/1024.00 AS unused

--==================================
-- the figures from my select
--==================================
select 137+61+56+54 AS reserved,
137 AS data,
61+56+54 AS index_size


enter image description here


It is not so far off, really, apart the fact I did not calculate the unused space!


What can I do to make this accurate?





Aucun commentaire:

Enregistrer un commentaire