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.
