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

Table Space

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.

 

Table Space SQL

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

 

Prev        Next