Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

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

By Dan Moran-347932,

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.

Total article views: 2954 | Views in the last 30 days: 4
 
Related Articles
FORUM

Columns order in the index

Columns order in the index

ARTICLE

Column Order in an Index

This short article shows the importance of order of columns in an index.

FORUM

Index Vs Select criteria sequence

Index Vs Select criteria sequence

FORUM

Physical and Logical Ordering of an index

Physical and Logical Ordering of an index

FORUM

Forcing a Select to use a specific index

Forcing a Select to use a specific index

Tags
indexing    
service broker    
table size    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones