June 5, 2013 at 5:24 am
We have a daily process that saves into a table the storage info returned by the SP, but it's procedural, slow, and requires manipulating the character output to get just the numbers. After searching your forums, I've found that the SP references the DMV above.
For a table with a clustered index and no non-clustered indexes, it's very easy to match the output from each; for a table that has non-clustered index(es), it seems more complicated. Although we currently don't use multiple partitions, I suppose that might be a further complication?
Just wondering if anyone has already written a query using the DMV to get the same (single line) output that the SP does (without the "KB" characters)?
Thanks,
~ Jeff
June 7, 2013 at 8:30 am
hi,
there's a quite good select here:
Have a look at it and pick out the data you need 🙂
June 7, 2013 at 9:14 am
How about using SP_MSFORACHTABLE 'EXEC SP_SPACEUSED [?]' and putting into a temp table/variable and running a select on that with a REPLACE on the size columns
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
June 7, 2013 at 9:45 am
Thanks for the replies...I'll try out each and see what suits the situation best (or what I can actually manage to get working!)
~ Jeff
June 7, 2013 at 2:17 pm
I'm using this one. Note that size is in MB.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET LOCK_TIMEOUT 10000
DECLARE @PageSize int
SET @PageSize = 8
SELECT
tbl.object_id,
CASE WHEN SUBSTRING(OBJECT_NAME(tbl.object_id),1,1) = '#' THEN '' ELSE OBJECT_SCHEMA_NAME(tbl.object_id) + '.' END
+ OBJECT_NAME(tbl.object_id) AS TableName,
IsView,
ISNULL(IndexCount, 0) AS [Indexes],
CASE WHEN spaceUsed.[HasClusteredIndex] = 1 THEN 'Y' ELSE 'N' END AS [Clust],
CONVERT(decimal (20, 3), spaceUsed.[SpaceReserved]/1024.) AS [Reserved MB],
CONVERT(decimal (20, 3), spaceUsed.[DataSpaceUsed]/1024.) AS [DataUsed MB],
CONVERT(decimal (20, 3), spaceUsed.[IndexSpaceUsed]/1024.) AS [IndexUsed MB],
CONVERT(decimal (20, 3), spaceUsed.[SpaceUnused]/1024.) AS [Unused MB],
spaceUsed.[RowCount],
spaceUsed.[DS Type] AS [DS Type],
spaceUsed.[DS Name] + ISNULL(' (' + PS.part_column + ')', '') AS [DS Name],
ISNULL(lob_ds.name + ISNULL(' (' + PS.part_column + ')', ''), '') AS [LOB FG Name],
create_date, modify_date
FROM
(
SELECT object_id, lob_data_space_id, CAST(0 AS BIT) AS IsView, create_date, modify_date FROM sys.tables
UNION ALL
SELECT v.object_id, NULL as lob_data_space_id, CAST(1 AS BIT) AS IsView, create_date, modify_date
FROM sys.views v
INNER JOIN sys.indexes i ON v.object_id = i.object_id
) tbl
LEFT JOIN (SELECT object_id, COUNT(*) IndexCount FROM sys.indexes WHERE index_id > 0 GROUP BY object_id) idx ON idx.object_id = tbl.object_id
LEFT JOIN (SELECT
i.object_id,
MAX(CASE i.index_id WHEN 1 THEN 1 ELSE 0 END) AS [HasClusteredIndex],
ISNULL((@PageSize * SUM(a.total_pages)) ,0.0) AS [SpaceReserved],
ISNULL((@PageSize * SUM(a.total_pages-a.used_pages)) ,0.0) AS [SpaceUnused],
ISNULL((@PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)) ,0.0) AS [DataSpaceUsed],
ISNULL((@PageSize * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)),0.0) AS [IndexSpaceUsed],
ISNULL(SUM(CASE WHEN p.index_id < 2 AND a.type = 1 THEN p.rows ELSE 0 END), 0) AS [RowCount],
MAX(CASE WHEN ds.type IN ('FG', 'PS') AND i.index_id < 2 THEN ds.type ELSE '' END) AS [DS Type],
MAX(CASE WHEN ds.type IN ('FG', 'PS') AND i.index_id < 2 THEN ds.name ELSE '' END) AS [DS Name]
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
LEFT JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id
GROUP BY
i.object_id) spaceUsed ON spaceUsed.object_id = tbl.object_id
LEFT JOIN (select ic.object_id, c.name as part_column
from sys.index_columns ic
inner join sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id
where ic.index_id = 1 AND ic.partition_ordinal > 0) PS on PS.object_id = tbl.object_id
LEFT JOIN sys.data_spaces lob_ds ON lob_ds.data_space_id = tbl.lob_data_space_id AND lob_ds.type = 'FG'
ORDER BY TableName
June 10, 2013 at 5:30 am
Thanks for the query, I appreciate you letting me utilize the results of your efforts.
~ Jeff
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply