Tables, Queues, and Indices as a Percent of DB Size

,

I've been writing service broker code recently, and wanted a utility that returned the size of the service broker queue. In addition, I've been using a utility (got it from sqlservercentral.com) for some years that returns each table as a percentage of the database size, so I thought it would be nice to get that as well, both for tables and for the queues. Finally, I wanted quick results, and I wanted to be able to do the entire thing as a view which could be referenced in a join.

Because of rounding errors, SELECT SUM(PercentOfDB) FROM database_stats will give you an answer consistently reliable to about the fifth spot right of the decimal - 99.99999xxx, or 100.00000xxx ... not much to be done about that.

Execution cost is about .67 on my dual core xeon dev server, and it runs in one or two tenths of a second.

You can get slightly more accurate results by issuing a CHECKPOINT prior to calling this.

rowData, lobData, and indexData are in KB. Everything else is in 8K pages.

Some samples:

-- Tables, Queues, and XML Indexes sorted by PercentOfDB

SELECT * FROM database_stats (nolock)

ORDER BY PercentOfDB DESC

-- Queues only sorted by PercentOfDB

SELECT * FROM database_stats

WHERE type = 'QUEUE_MESSAGES'

ORDER BY PercentOfDB DESC

-- Tables without records

SELECT * FROM database_stats

WHERE TYPE = 'USER_TABLE' AND

row_count = 0

ORDER BY objectName

-- Percent of DB by entity type

SELECT

type, SUM(PercentOfDB)

FROM database_stats

GROUP BY type

-- Percent of DB by schema

SELECT

schemaName, SUM(PercentOfDB)

FROM database_stats

GROUP BY schemaName

~~~

This second view is similar to the table stats view, but focuses on index stats. It joins to sys.dm_db_index_usage_stats to calculate an efficiency stat -- (user_seeks + user_scans + user_lookups) - user_updates. If you wanted to keep the stats through reboot (sys.dm_db_index_usage_stats restarts on each reboot) logging them to a table would be trivial.

Execution cost is about .2 on my dual core xeon dev server, and it runs in about six hundredths of a second.

Some samples:

-- Untouched since last reboot

SELECT * FROM database_index_stats (nolock)

WHERE user_seeks = 0 AND

user_scans = 0

AND user_lookups = 0

ORDER BY PercentOfDB DESC

-- Storage allocated to objects untouched since last reboot

SELECT SUM(percentOfDB)

FROM database_index_stats (nolock)

WHERE user_seeks = 0 AND

user_scans = 0

AND user_lookups = 0

On one of my production servers this was nearly 30% ....

-- All the heaps

SELECT * FROM database_index_stats

WHERE indexType = 0

Heaps are bad, mm'kay?

-- Indexes more written than read

SELECT * FROM database_index_stats

WHERE efficiency < 0

ORDER BY efficiency

(Obviously the efficiency stat is just a marker -- some relatively inefficient indices may be critical to your user experience. Use with caution.)

You can also use the view to ORDER DESC by user_scans and user_lookups -- ideally you want many more user_seeks than user_scans or user_lookups. (Of course, you can get this same functionality directly out of sys.dm_db_index_usage_stats.)

~~~~~

Note: this is a slight fix to the previous version. It handles some bugs in the schema names and explicitly identifies a few of the service broker system tables by the views through which they're accessible.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW database_stats
AS
SELECT
 CASE
   WHEN O2.object_ID IS NOT NULL THEN O2.object_id
   ELSE O.object_id
 END AS objectID,
 CASE
   WHEN S2.Name IS NULL THEN S.name
   ELSE S2.name
 END AS schemaName,
 CASE
   WHEN O2.object_id IS NULL THEN 
     CASE
       WHEN O.name = 'sysconvgroup' THEN 'sysconvgroup (conversation_groups)'
       WHEN O.name = 'sysdesend' THEN 'sysdesend (conversation_endpoints)'
       WHEN O.name = 'sysdercv' THEN 'sysdercv (conversation_endpoints)'
       WHEN O.name = 'sysxmitqueue' THEN 'sysxmitqueue (transmission_queue)'
     ELSE O.name
   END
   ELSE O2.name
 END AS objectName,
 CASE
   WHEN O2.object_id IS NULL THEN O.type_desc
   ELSE IT.internal_type_desc
 END AS 'type',
 (CONVERT(DECIMAL(20,5),(DE1.used_page_count)) / 
 (SELECT SUM(used_page_count) FROM sys.dm_db_partition_stats)) 
 * 100 AS percentOfDB,
 CASE
   WHEN DE1.used_page_count = 0 THEN 0
   ELSE (CONVERT(DECIMAL(20,5),used_page_count) / CONVERT(DECIMAL(20,5),reserved_page_count)) * 100
 END as PercentPagesFull,
 DE1.row_count,
 DE2.tableData - (DE1.lob_used_page_count * 8) AS rowData,
 (DE1.lob_used_page_count * 8) AS lobData,
 ISNULL(DE3.indexData,0) indexData,
 DE1.in_row_data_page_count,
 DE1.in_row_used_page_count,
 DE1.in_row_reserved_page_count,
 DE1.lob_used_page_count,
 DE1.lob_reserved_page_count,
 DE1.row_overflow_used_page_count,
 DE1.row_overflow_reserved_page_count,
 DE1.used_page_count,
 DE1.reserved_page_count
FROM
 sys.objects O
INNER JOIN
 sys.schemas S ON O.schema_id = S.schema_id
INNER JOIN
 (
 SELECT
   PS1.Object_ID objectID,
   MAX(PS1.row_count) AS row_count,
   SUM(PS1.in_row_data_page_count) in_row_data_page_count,
   SUM(PS1.in_row_used_page_count) in_row_used_page_count,
   SUM(PS1.in_row_reserved_page_count) in_row_reserved_page_count,
   SUM(PS1.lob_used_page_count) lob_used_page_count,
   SUM(PS1.lob_reserved_page_count) lob_reserved_page_count,
   SUM(PS1.row_overflow_used_page_count) row_overflow_used_page_count,
   SUM(PS1.row_overflow_reserved_page_count) row_overflow_reserved_page_count,
   SUM(PS1.used_page_count) used_page_count,
   SUM(PS1.reserved_page_count) reserved_page_count
 FROM 
   sys.dm_db_partition_stats PS1
 GROUP BY
   OBJECT_ID
  ) DE1 ON O.object_ID = DE1.objectID
INNER JOIN
 (
 SELECT
   OBJECT_ID objectID,
   SUM(used_page_count) * 8 AS tableData
 FROM 
   sys.dm_db_partition_stats PS
 WHERE
   index_id IN (0,1) -- Heap, Cluster
 GROUP BY
   OBJECT_ID
 ) DE2 ON DE1.objectID = DE2.objectID
LEFT OUTER JOIN
 (
 SELECT
   OBJECT_ID objectID,
   SUM(used_page_count) * 8 AS indexData
 FROM 
   sys.dm_db_partition_stats PS
 WHERE
   index_id NOT IN (0,1) -- Heap, Cluster
 GROUP BY
   OBJECT_ID
 ) DE3 ON DE1.objectID = DE3.objectID
LEFT OUTER JOIN
 sys.objects O2 ON O.parent_object_id = O2.object_id
 AND O.type = 'IT'
LEFT OUTER JOIN
 sys.schemas S2 ON O2.schema_id = S2.schema_id
LEFT OUTER JOIN 
  sys.internal_tables IT ON O.object_id = IT.object_id
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW database_index_stats
AS
SELECT
    O.object_id AS objectID,
    S.name AS schemaName,
    O.name AS objectName,
    CASE
        WHEN SI.name IS NULL THEN 'Heap'
        ELSE SI.name
    END AS indexName,
    SI.type AS indexType,
    (CONVERT(DECIMAL(20,5),(PS.used_page_count)) / 
        (SELECT SUM(used_page_count) FROM sys.dm_db_partition_stats)) 
        * 100 AS percentOfDB,
    CASE
        WHEN PS.used_page_count = 0 THEN 0
        ELSE (CONVERT(DECIMAL(20,5),PS.used_page_count) / CONVERT(DECIMAL(20,5),PS.reserved_page_count)) * 100
    END as PercentPagesFull,
    PS.used_page_count,
    PS.reserved_page_count,
    US.user_seeks,
    US.user_scans,
    US.user_lookups,
    US.user_updates,
    ((US.user_seeks + US.user_scans + US.user_lookups) - US.user_updates) AS efficiency
FROM sys.objects O
INNER JOIN sys.schemas S ON O.schema_id = s.schema_id
INNER JOIN sys.dm_db_partition_stats PS ON O.object_id = PS.object_id
INNER JOIN sys.indexes SI ON PS.object_id = SI.object_id
AND PS.index_id = SI.index_id
INNER JOIN sys.dm_db_index_usage_stats US ON US.object_id = SI.object_id
AND US.index_id = SI.index_id
AND US.database_id = db_id()
WHERE O.is_ms_shipped = 0

Rate

1 (1)

Share

Share

Rate

1 (1)