January 30, 2013 at 4:41 am
Hi,
I've been trying to replace sysobjects and sysindexes by sys.objects and sys.indexes in the well known query to get table sizes for all tables in a DB. However, I can't seem to find a decent way to join them !
For now I'm using the query below, but I'm afraid that it might have a tipping point after which it would become quite heavy. I'm fiddling around with windowing to get the sp_msforeachtable out of there, but no such luck up to know...
declare @tableusage table(
namevarchar(128),
rowschar(11),
reservedvarchar(18),
datavarchar(18),
index_sizevarchar(18),
unusedvarchar(18))
insert into @tableusage
exec sp_MSforeachtable 'exec sp_spaceused"?"'
select * from @tableusage
order by CONVERT(int, REPLACE(reserved,'KB','')) DESC
January 30, 2013 at 6:21 am
hows this:
SELECT
OBJECT_NAME(object_id) AS ObjectName
, object_id
, SUM(rows) AS rows
, SUM(data_pages) data_pages
, CONVERT(DECIMAL(18,2), SUM(data_pages) * 8.0 / 1024 ) MBs
FROM
sys.partitions p
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE
index_id IN (0, 1) and
OBJECT_NAME(object_id) NOT LIKE 'sys%'
AND OBJECT_NAME(object_id) NOT LIKE 'queue%'
AND object_name(object_id) <> 'dtproperties'
GROUP BY
object_id
HAVING
SUM(rows) > 0
order by data_pages desc -- or rows
---------------------------------------------------------------------
January 30, 2013 at 10:17 am
or this is the SQL used by SSMS when running disk usage report and gives you output similar to sp_spaceused (i.e. it includes index space)
SELECT TOP 1000
(row_number() over(order by (a1.reserved + ISNULL(a4.reserved,0)) desc))%2 as l1,
a3.name AS [schemaname],
a2.name AS [tablename],
a1.rows as row_count,
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS 'reserved KB',
a1.data * 8 AS 'data KB',
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS 'index_size KB',
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS 'unused KB'
FROM
(SELECT
ps.object_id,
SUM (
CASE
WHEN (ps.index_id < 2) THEN row_count
ELSE 0
END
) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (
CASE
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> 'S' and a2.type <> 'IT'
---------------------------------------------------------------------
January 30, 2013 at 2:36 pm
FYI, in case you want it, here's the query I use to get an overview of tables, indexes and their sizes:
SELECT
SCHEMA_NAME(o.schema_id) AS Schema_Name,
o.name AS Table_Name, --dps.index_id AS Index_Id,
CEILING(SUM(dps.reserved_page_count) / 128.0) AS Table_Plus_Indexes_MB,
CEILING(SUM(CASE WHEN dps.index_id IN (0, 1) THEN dps.reserved_page_count ELSE 0 END) / 128.0)
AS Table_MB,
SUM(CASE WHEN dps.index_id IN (0, 1) THEN row_count ELSE 0 END) AS Total_Rows_Table,
SUM(row_count) AS Total_Rows_Table_Plus_Indexes,
COUNT(DISTINCT CASE WHEN dps.index_id > 0 THEN dps.index_id END) AS Total_#_Of_Indexes,
MAX(CASE WHEN dps.index_id = 1 THEN 'Yes' ELSE 'No' END) AS [Has_Clus_Index],
MAX(FILEGROUP_NAME(au.data_space_id)) AS File_Group,
o.create_date
FROM sys.dm_db_partition_stats dps WITH (NOLOCK)
INNER JOIN sys.partitions p WITH (NOLOCK) ON
p.partition_id = dps.partition_id
INNER JOIN sys.allocation_units au WITH (NOLOCK) ON
au.container_id = CASE WHEN au.type in(1,3) THEN p.hobt_id ELSE p.partition_id END
INNER JOIN sys.objects o WITH (NOLOCK) ON
o.object_id = dps.object_id
WHERE
o.type = 'U' AND --user tables only
o.name NOT IN (N'dtproperties') AND
o.is_ms_shipped = 0
GROUP BY
SCHEMA_NAME(o.schema_id), o.name, o.create_date
ORDER BY
Schema_Name, Table_Name --Name Order
--SUM(dps.reserved_page_count) / 128.0 DESC --Size Order, from largest to smallest total size
COMPUTE SUM(CEILING(SUM(dps.reserved_page_count) / 128.0))
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 1, 2013 at 9:35 am
Great stuff guys,
Thanks a bundle ! What I did notice though is that sp_spaceused often shows some index usage when queries using the index ID (0,1) don't.
Would that be some index metadate that sp_spaceused adds ?
February 1, 2013 at 10:22 am
this is the meat of sp_spaceused when used against a single table so their are differences in how it is calculating it
/*
** Now calculate the summary data.
* Note that LOB Data and Row-overflow Data are counted as Data Pages.
*/
SELECT
@reservedpages = SUM (reserved_page_count),
@usedpages = SUM (used_page_count),
@pages = SUM (
CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
),
@rowCount = SUM (
CASE
WHEN (index_id < 2) THEN row_count
ELSE 0
END
)
FROM sys.dm_db_partition_stats
WHERE object_id = @id;
/*
** Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table
*/
IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204,211,212,213,214,215,216)) > 0
BEGIN
/*
** Now calculate the summary data. Row counts in these internal tables don't
** contribute towards row count of original table.
*/
SELECT
@reservedpages = @reservedpages + sum(reserved_page_count),
@usedpages = @usedpages + sum(used_page_count)
FROM sys.dm_db_partition_stats p, sys.internal_tables it
WHERE it.parent_id = @id AND it.internal_type IN (202,204,211,212,213,214,215,216) AND p.object_id = it.object_id;
END
SELECT
name = OBJECT_NAME (@id),
rows = convert (char(11), @rowCount),
reserved = LTRIM (STR (@reservedpages * 8, 15, 0) + ' KB'),
data = LTRIM (STR (@pages * 8, 15, 0) + ' KB'),
index_size = LTRIM (STR ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0) + ' KB'),
unused = LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')
---------------------------------------------------------------------
February 1, 2013 at 10:28 am
rooierus (2/1/2013)
Great stuff guys,Thanks a bundle ! What I did notice though is that sp_spaceused often shows some index usage when queries using the index ID (0,1) don't.
Would that be some index metadate that sp_spaceused adds ?
Index ID (0,1) are the 'heap' or clustered index - i.e. the table itself, and are not counted in index usage.
Nonclustered indexes (ID 2 or above) are extra on top and are returned as index usage by sp_spaceused.
Edit: looking at the code for sp_spaceused posted by george then the index usage value is worked out slightly differently than just ID >= 2 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply