Sql-Server
sys.allocation_units 和 sp_spaceused 上的空間使用情況
眾所周知，DMV 不保存有關頁數和行數的準確資訊。但是，當您更新統計數據時，我看不出他們為什麼不這樣做。
我正在開發一個監控工具，想知道每個索引和數據的磁碟大小等。最終我想找到正確的填充因子，等等。
我的函式和舊的 sp_spaceused 使用的空間在空間使用方面略有不同，但在記錄數上沒有區別。
你能看看我的選擇中是否缺少任何東西嗎？
這是 sp_spaceused （然後我將數字轉換為 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
但是當我執行我的選擇，下面的程式碼\下面的圖片時，我得到的數字略有不同。
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 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
除了我沒有計算未使用的空間之外，它並沒有那麼遠，真的！
我該怎麼做才能使這個準確？
更改後：
在我將 1024 替換為 1024.00 之後，結果更加準確。我注意到記錄已插入到有問題的表中，顯然統計數據不是最新的，但結果仍然匹配（低於 1 MB 差異 - 這對我來說沒問題）
新的結果集是：
--================================== -- the figures from sp_spaceused --================================== select 318072 /1024.00 AS reserved, 140208 /1024.00 AS data, 177096 /1024.00 AS index_size, 768 /1024.00 AS unused go --================================== -- the figures from my select --================================== select 137.7578125+61.7968750+56.4218750+54.6406250 as reserved, 137.7578125 as data, 61.7968750+56.4218750+54.6406250 as index_size
即使您修復了直接舍入問題，獲取每個對象/索引統計資訊的整體算法也是不正確的。它不能正確處理 LOB 和行溢出數據。它還排除：索引視圖、全文索引、XML 索引和其他一些情況。因此，您可能看不到所有數據。
以下是我在 StackOverflow 上發布的答案（sp_spaceused - 如何測量 SQL 中所有表中的 GB 大小）的程式碼的改編，它處理了所有處理的情況
sp_spaceused。這個 SO 問題只涉及每個對象的統計資訊，而不是每個索引，所以我調整了程式碼以處理索引級別的事情。;WITH agg AS ( -- Get info for Tables, Indexed Views, etc SELECT ps.[object_id] AS [ObjectID], ps.index_id AS [IndexID], NULL AS [ParentIndexID], NULL AS [PassThroughIndexName], NULL AS [PassThroughIndexType], SUM(ps.in_row_data_page_count) AS [InRowDataPageCount], SUM(ps.used_page_count) AS [UsedPageCount], SUM(ps.reserved_page_count) AS [ReservedPageCount], SUM(ps.row_count) AS [RowCount], SUM(ps.lob_used_page_count + ps.row_overflow_used_page_count) AS [LobAndRowOverflowUsedPageCount] FROM sys.dm_db_partition_stats ps GROUP BY ps.[object_id], ps.[index_id] UNION ALL -- Get info for FullText indexes, XML indexes, Spatial indexes, etc SELECT sit.[parent_id] AS [ObjectID], sit.[object_id] AS [IndexID], sit.[parent_minor_id] AS [ParentIndexID], sit.[name] AS [PassThroughIndexName], sit.[internal_type_desc] AS [PassThroughIndexType], 0 AS [InRowDataPageCount], SUM(ps.used_page_count) AS [UsedPageCount], SUM(ps.reserved_page_count) AS [ReservedPageCount], 0 AS [RowCount], 0 AS [LobAndRowOverflowUsedPageCount] FROM sys.dm_db_partition_stats ps INNER JOIN sys.internal_tables sit ON sit.[object_id] = ps.[object_id] WHERE sit.internal_type IN (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222, 236) GROUP BY sit.[parent_id], sit.[object_id], sit.[parent_minor_id], sit.[name], sit.[internal_type_desc] ), spaceused AS ( SELECT agg.[ObjectID], agg.[IndexID], agg.[ParentIndexID], agg.[PassThroughIndexName], agg.[PassThroughIndexType], OBJECT_SCHEMA_NAME(agg.[ObjectID]) AS [SchemaName], OBJECT_NAME(agg.[ObjectID]) AS [TableName], SUM(CASE WHEN (agg.IndexID < 2) THEN agg.[RowCount] ELSE 0 END) AS [Rows], SUM(agg.ReservedPageCount) * 8 AS [ReservedKB], SUM(agg.LobAndRowOverflowUsedPageCount + CASE WHEN (agg.IndexID < 2) THEN (agg.InRowDataPageCount) ELSE 0 END) * 8 AS [DataKB], SUM(agg.UsedPageCount - agg.LobAndRowOverflowUsedPageCount - CASE WHEN (agg.IndexID < 2) THEN agg.InRowDataPageCount ELSE 0 END) * 8 AS [IndexKB], SUM(agg.ReservedPageCount - agg.UsedPageCount) * 8 AS [UnusedKB], SUM(agg.UsedPageCount) * 8 AS [UsedKB] FROM agg GROUP BY agg.[ObjectID], agg.[IndexID], agg.[ParentIndexID], agg.[PassThroughIndexName], agg.[PassThroughIndexType], OBJECT_SCHEMA_NAME(agg.[ObjectID]), OBJECT_NAME(agg.[ObjectID]) ) SELECT sp.SchemaName, sp.TableName, sp.IndexID, CASE WHEN (sp.IndexID > 0) THEN COALESCE(si.[name], sp.[PassThroughIndexName]) ELSE N'<Heap>' END AS [IndexName], sp.[PassThroughIndexName] AS [InternalTableName], sp.[Rows], sp.ReservedKB, (sp.ReservedKB / 1024.0 / 1024.0) AS [ReservedGB], sp.DataKB, (sp.DataKB / 1024.0 / 1024.0) AS [DataGB], sp.IndexKB, (sp.IndexKB / 1024.0 / 1024.0) AS [IndexGB], sp.UsedKB AS [UsedKB], (sp.UsedKB / 1024.0 / 1024.0) AS [UsedGB], sp.UnusedKB, (sp.UnusedKB / 1024.0 / 1024.0) AS [UnusedGB], so.[type_desc] AS [ObjectType], COALESCE(si.type_desc, sp.[PassThroughIndexType]) AS [IndexPrimaryType], sp.[PassThroughIndexType] AS [IndexSecondaryType], SCHEMA_ID(sp.[SchemaName]) AS [SchemaID], sp.ObjectID --,sp.ParentIndexID FROM spaceused sp INNER JOIN sys.all_objects so -- in case "WHERE so.is_ms_shipped = 0" is removed ON so.[object_id] = sp.ObjectID LEFT JOIN sys.indexes si ON si.[object_id] = sp.ObjectID AND (si.[index_id] = sp.IndexID OR si.[index_id] = sp.[ParentIndexID]) WHERE so.is_ms_shipped = 0 --so.[name] LIKE N'' -- optional name filter --ORDER BY ????