SQL Exec Stats, Copyright © 2006-2012 Elemental Inc. All rights reserved.
Read Me, Intro, Main, General, Databases, File IO, Tables, Indexes, Exec Stats, Stored Procs, Dist Stats, Appendix
Table Space
The tab Table Space lists space usage by table (and views) for each selected database. The first colums is dbid to distinguish database. Object id is included in case it is needed. The type distinguishes between user tables, and views. The average byte per row is computed based on the number of data leaf pages only. Non-clustered indexes and upper level clustered index pages are not included.
Alternate view, tpch database tables
The query for table space is as follows: (needs to be updated)
--Table Space used:
DECLARE @dbid int SELECT @dbid = DB_ID()
SELECT @dbid dbid,
t.name [Schema],
o.name [Object],
x.object_id,
o.type [Type] ,
CASE [Rows] WHEN 0
THEN 0
ELSE 1024*Data/Rows
END AvRsz ,
[Rows], Reserved,
Data, [Index] =
index2,
Unused = Reserved - Used ,
Clust, IxCnt,
XmlCnt, StatCnt,
Hypothetical, Partitioned,
Compress, o.create_date,
lob_used lob, row_overflow_used ovr
FROM (
SELECT
ISNULL
(i.object_id,0)
object_id,
Reserved =
SUM(p.reserved),
Used =
SUM(p.used),
Data =
SUM(CASE
WHEN
(p.index_id < 2)
THEN
(p.in_row_data
+ p.lob_used
+ p.row_overflow_used)
ELSE p.lob_used
+ p.row_overflow_used
END ),
index2 =
SUM(CASE
WHEN i.type
=2 THEN
(p.in_row_data)
ELSE 0 END) ,
Rows =
SUM(CASE
WHEN i.type<2
THEN p.row_count
ELSE 0 END ) ,
Clust =
SUM(CASE
WHEN i.type=1
THEN 1 ELSE 0
END) ,
IxCnt =
SUM(CASE
WHEN i.type =2
THEN 1 ELSE 0
END) ,
XmlCnt =
SUM(CASE
WHEN i.type = 3
THEN 1
ELSE 0 END) ,
StatCnt =
(SELECT
COUNT(*)
FROM sys.stats s
WHERE s.object_id
= i.object_id) ,
Hypothetical =
SUM(CASE i.is_hypothetical
WHEN 1 THEN 1
ELSE 0 END) ,
Partitioned =
SUM(CASE
WHEN ISNULL(Partitions,1)
= 1 THEN 0
ELSE 1 END)
,
in_row_data =
SUM(in_row_data),
lob_used =
SUM(lob_used),
row_overflow_used =
SUM(row_overflow_used) ,
Compress =
SUM(CASE compress
WHEN 0 THEN 0
ELSE 1 END)
FROM
sys.indexes i
WITH (NOLOCK)
-- JOIN sys.objects o WITH (NOLOCK) ON o.object_id = i.object_id AND o.type NOT IN ('S', 'IT', 'TF')
LEFT JOIN
(
SELECT
d.object_id,
d.index_id,
COUNT(*)
AS Partitions ,
reserved
=
8*SUM(reserved_page_count),
used =
8*SUM(used_page_count),
in_row_data =
8*SUM(in_row_data_page_count),
lob_used =
8*SUM(lob_used_page_count) ,
row_overflow_used =
8*SUM(row_overflow_used_page_count) ,
row_count =
SUM(row_count) ,
compress = SUM(q.data_compression)
FROM
sys.dm_db_partition_stats d
WITH (NOLOCK)
INNER JOIN sys
.partitions q
WITH (NOLOCK)
ON q.partition_id
= d.partition_id
GROUP BY
d.object_id ,
d.index_id
) p
ON i.object_id
= p.object_id
AND i.index_id
= p.index_id
GROUP BY
i.object_id
-- WITH ROLLUP HAVING i.object_id IS NOT NULL OR i.object_id IS NULL
) x
LEFT JOIN
sys.objects o
WITH (NOLOCK)
ON o.object_id
= x.object_id
LEFT JOIN
sys.schemas t
WITH (NOLOCK)
ON t.schema_id
= o.schema_id
WHERE o.type
NOT IN
('S',
'IT',
'TF')
ORDER BY Data DESC