Parent,
SQL Exec Stats, Copyright © 2006-2018 Elemental Inc. All rights reserved.
Read Me,
Intro,
Main,
General,
Databases,
File IO,
Tables,
Indexes,
ExecStats,
StoredProcs,
DistStats,
IxOpStats,
Appendix
update in progress 2015-Feb
Columns have been added for the number guid columns (guid) and computed columns (cmc) in each table.
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. New Ix should report the space used in Spatial and Column-Store indexes, but this has not been tested.
The next set of columns below include the tables with clustered indexes, the number of (traditional) non-clustered indexes, unique indexes (& constraints), XML, Spatial and Column Store indexes, then number of statistics. Partitioned should indicate the nunber of indexes that are partitioned. More columns include compression, dataspace id, lob dataspace id, fulltext catalogsm FT size, the number of columns, reference & foreign key, and triggers.
The remaining columns include a set for the index usage stats: Clustered index seeks, total nonclustered index seeks, Lookups, scans, updates, indexes with zero usage, DMV Missing Index entries, create date, number of filtered indexes, disabled and hypothetical.
The query for table space is as follows: (update in progress 2013-Sep)
for databases with very many objects, indexes, etc, I will change to get of the other system tables individually
instead of one big join.
--Table Space used:
DECLARE @dbid int
SELECT @dbid =
DB_ID();
WITH a AS (
SELECT CASE WHEN
o.schema_id = 4
THEN CASE WHEN
o.type =
'S' THEN 1
WHEN o.type
= 'IT' THEN 2 ELSE 3
END
ELSE
o.object_id END
AS object_id,
o.type
AS otype
,
i.index_id,
i.data_space_id,
d.reserved_page_count,
d.used_page_count
,
d.in_row_data_page_count,
d.lob_used_page_count,
d.row_overflow_used_page_count
,
d.row_count,
r.data_compression,
r.partition_number,
i.type itype
, i.is_unique
,
i.is_disabled,
i.is_hypothetical,
i.has_filter
FROM sys.objects
o
WITH(NOLOCK)
INNER JOIN
sys.indexes i
WITH(NOLOCK)
ON i.object_id
= o.object_id
LEFT JOIN
sys.partitions r
WITH(NOLOCK)
ON r.object_id
= i.object_id
AND r.index_id
= i.index_id
LEFT JOIN
sys.dm_db_partition_stats d
WITH(NOLOCK)
ON d.partition_id
= r.partition_id
--AND d.object_id = r.object_id AND d.index_id = r.index_id
--AND d.partition_number = r.partition_number
WHERE o.type
<> 'TF'
), b AS (
SELECT object_id, index_id, otype , data_space_id
,
CASE WHEN COUNT(*) > 1
THEN 1 ELSE 0 END
Part,
COUNT(*) AS Cnt
, 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)
, compressed =
SUM(data_compression)
, Clus = MAX(CASE a.index_id WHEN 1 THEN 1 ELSE 0 END)
, IxCt = MAX(CASE itype WHEN 2 THEN 1 ELSE 0 END)
, XmlC = MAX(CASE itype WHEN 3 THEN 1 ELSE 0 END)
, Uniq = MAX(CASE is_unique WHEN 1 THEN 1 ELSE 0 END)
, disa = MAX(CASE is_disabled WHEN 1 THEN 1 ELSE 0 END)
, hypo = MAX(CASE is_hypothetical WHEN
1 THEN 1 ELSE 0
END)
, filt = MAX(CASE has_filter WHEN 1 THEN 1 ELSE 0 END)
FROM a GROUP BY object_id, index_id, otype , data_space_id
), c AS (
SELECT CASE WHEN otype
IS NULL THEN 'A' ELSE otype END [Type]
, CASE WHEN
b.object_id
IS NULL THEN 0
ELSE b.object_id
END AS object_id
, CASE WHEN
b.object_id
IS NULL THEN 0
WHEN b.object_id
IN (1,2)
THEN b.object_id
ELSE 3 END Ord
,
MIN(data_space_id)
data_space_id
, [Rows] =
SUM(CASE
WHEN b.index_id
< 2 THEN
b.row_count
ELSE 0 END )
, Reserved =
SUM(b.reserved),
Used =
SUM(b.used)
, Data =
SUM(CASE WHEN
(b.index_id
< 2)
THEN (b.in_row_data
+ b.lob_used
+ b.row_overflow_used)
ELSE b.lob_used
+ b.row_overflow_used
END)
, index2 = SUM(CASE WHEN b.index_id > 1 THEN (b.in_row_data) ELSE 0 END)
, in_row_data = SUM(in_row_data), lob = SUM(lob_used), ovr = SUM(row_overflow_used)
, SUM(CASE compressed WHEN 0 THEN 0 ELSE 1 END) Compress
, SUM(CASE WHEN b.object_id > 10 AND Part > 0 THEN 1 ELSE 0 END) AS Part
, MAX(CASE WHEN b.object_id > 10 THEN Cnt ELSE NULL END) AS Parts
, MAX(Cnt) AS Cnt
, SUM(Clus) Clus, SUM(IxCt) IxCnt, SUM(XmlC) XmlIx, SUM(Uniq) UnqIx
, SUM(disa) disa, SUM(hypo) hypo, SUM(filt) filt
FROM b GROUP BY b.object_id, otype --, data_space_id
)
SELECT @dbid [dbid], CASE WHEN u.schema_id IS NULL THEN '' ELSE u.name END [Schema]
, CASE c.object_id WHEN 0 THEN '_Total' WHEN 1 THEN '_sys' WHEN 2 THEN '_IT'
ELSE o.name END [Object]
, c.object_id, c.[Type]
, [Rows], Reserved, Data, lob, ovr, [Index] = index2, Unused = Reserved - Used
, AvRsz = CASE [Rows] WHEN 0 THEN 0 ELSE 1024*[Data]/ [Rows] END
, CASE WHEN c.object_id IN (1,2,3) THEN Cnt ELSE Clus END Clust
, IxCnt, UnqIx, XmlIx, [Stats] -- kct
, CASE Part WHEN 0 THEN NULL ELSE Part END Partitioned
, CASE WHEN Parts > 1 THEN Parts ELSE NULL END Parts
, CASE Compress WHEN 0 THEN NULL ELSE Compress END Compress
,
c.data_space_id dsid
, CASE
t.lob_data_space_id
WHEN 0 THEN NULL
ELSE t.lob_data_space_id
END lds
,
fif.ftct,
fif.ftsz
, cols, rkey, fkey, def, trg
, 0 [CIxSk], 0 [LkUps], 0 [Scans], 0 [Upds], 0 [ZrIx], 0 [MsIx]
, o.create_date
, Filt = CASE filt WHEN 0 THEN NULL ELSE filt END
, disabl = CASE disa WHEN 0 THEN NULL ELSE disa END
, Hypothetical = CASE hypo WHEN 0 THEN NULL ELSE hypo END
FROM c
LEFT JOIN
sys.objects o WITH(NOLOCK) ON o.object_id = c.object_id
--AND c.object_id > 10 -- don't join the lower values
LEFT JOIN sys.tables t WITH(NOLOCK) ON t.object_id = c.object_id
LEFT JOIN sys.schemas u WITH(NOLOCK) ON u.schema_id = o.schema_id
LEFT JOIN (
SELECT
CASE WHEN object_id IS NULL THEN 0 ELSE object_id END object_id
, COUNT(*) [Stats]
FROM sys.stats WITH(NOLOCK) WHERE object_id > 3 -- skip low values
GROUP BY object_id ) s ON s.object_id = c.object_id
LEFT JOIN (
SELECT table_id,
SUM(data_size)/1024
ftsz
,
MAX(row_count)
ftrows,
COUNT(*) ftct
FROM sys.fulltext_index_fragments
WHERE [status] = 4 GROUP BY table_id
) fif ON fif.table_id = c.object_id
--LEFT JOIN ( SELECT object_id, COUNT(*) kct FROM sys.index_columns WITH(NOLOCK)
-- WHERE index_id = 1 GROUP BY object_id ) k ON k.object_id = c.object_id
LEFT JOIN ( SELECT object_id, COUNT(*) cols
FROM sys.columns WITH(NOLOCK) GROUP BY object_id
) e ON e.object_id = c.object_id
LEFT JOIN ( SELECT referenced_object_id,
COUNT(*) rkey
FROM sys.foreign_keys WITH(NOLOCK) GROUP BY referenced_object_id
) r ON r.referenced_object_id
= c.object_id
LEFT JOIN ( SELECT parent_object_id, COUNT(*) fkey
FROM sys.foreign_keys WITH(NOLOCK) GROUP BY parent_object_id
) f ON f.parent_object_id = c.object_id
LEFT JOIN ( SELECT parent_object_id, COUNT(*) def
FROM sys.default_constraints
WITH(NOLOCK) GROUP BY parent_object_id
) d ON d.parent_object_id = c.object_id
LEFT JOIN ( SELECT parent_id, COUNT(*) trg
FROM sys.triggers
WITH(NOLOCK)
WHERE parent_id > 0
GROUP BY parent_id
) g ON
g.parent_id =
c.object_id
ORDER BY
Ord,
Reserved DESC,
[Object]
The previous generation SQL for table space is as follows:
--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