Thursday, March 30, 2017

Does my table look fat in this database

This is a useful query to get the total size of tables and indexes. It gives similar information to the standard report in SQL Server for top tables. (That's where I plagiarised the original query from.) However, I find it really useful to be able to get the information in a query that you can adapt. This query shows the compression factor for the first partition of the table.

with ps as
  (SELECT
    object_id,
    MIN(partition_id) first_partition_id,
    SUM (CASE
         WHEN (index_id<2) THEN row_count
         ELSE 0
         END)
       AS [rows],
    SUM (reserved_page_count) AS reserved,
    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) AS data,
    SUM (used_page_count) AS used
  FROM sys.dm_db_partition_stats
  GROUP BY object_id)
           ,ps2 as
  (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)

SELECT --TOP 1000
    a3.name AS SchemaName,
    a2.name AS TableName,
    CASE a2.type
      WHEN 'U' THEN 'User'
      WHEN 'S' THEN 'System'
      WHEN 'IT' THEN 'Internal'
      ELSE a2.type
      END
    AS TableType,
    ps.rows as [RowCount],
(ps.reserved + ISNULL(ps2.reserved,0))* 8 AS Reserved_KB,
ps.data * 8 AS Data_KB,
    CASE
      WHEN (ps.used + ISNULL(ps2.used,0)) > ps.data
      THEN (ps.used + ISNULL(ps2.used,0)) - ps.data
      ELSE 0
      END * 8
    AS Index_KB,
    CASE
      WHEN (ps.reserved + ISNULL(ps2.reserved,0)) > ps.used
      THEN (ps.reserved + ISNULL(ps2.reserved,0)) - ps.used
      ELSE 0
      END * 8
    AS Unused_KB,
    p.data_compression_desc FirstPartitionCompression
  FROM ps
  LEFT OUTER JOIN ps2
    ON (ps2.parent_id = ps.object_id)
  INNER JOIN sys.all_objects a2
    ON ( ps.object_id = a2.object_id )
  INNER JOIN sys.schemas a3
    ON (a2.schema_id = a3.schema_id)
  LEFT OUTER JOIN sys.partitions p
    ON p.partition_id=ps.first_partition_id
--WHERE a2.type <> N'S'
--and a2.type <> N'IT'
 
 
order by 5 desc