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

How much space are you wasting?

A few weeks back a client of mine asked me to write a script that could tell him the space used by write only indexes. The usage stats from the indexes is pretty easy to get from ‘sys.dm_db_index_usage_stats’ and if you join the DMV ‘sys.dm_db_partition_stats’ onto that, you can from the column ‘used_page_count’ calculate the number of bytes that the particular indexes is wasting.

Here is the script, that I ended up with:

with cte as
from sys.dm_db_index_usage_stats t1
inner join sys.indexes t2 on (t1.object_id = t2.object_id) AND (t1.index_id = t2.index_id)
inner join sys.dm_db_partition_stats t3 on (t1.object_id = t3.object_id) AND (t1.index_id = t3.index_id)
    database_id = DB_ID() and
    user_seeks = 0 and user_scans = 0 and user_lookups = 0 and
    OBJECTPROPERTY(t1.[object_id],'IsUserTable') = 1 and t2.index_id > 1 and
    t2.is_unique = 0 and t2.is_unique_constraint = 0
COUNT(*) as numofpartitions,
SUM(used_page_count) as UsedPages,
(SUM(used_page_count) * 8) / 1024 as spacewasteinmb
from cte
group by database_id, object_id, index_id
order by 1,2,3

if you add a COMPUTE statement at the end, you will get the total sum of all the write only indexes is your database. That COMPUTE statement could look like this:

COMPUTE SUM((SUM(used_page_count) * 8) / 1024)

Putting it all together, and running the query gives me on one of my databases a output looking like this.


as the pictures shows, I could save almost 300 MB if I would drop the three write only indexes from the list above. As always be careful when you drop indexes – better check an extra time before dropping.

Please post your savings as blog comments, and I’ll reward the biggest number with a Xmas present from Geniiius. The game is on.



Posted by Jason Brimhall on 8 November 2011

nice script.

Posted by Robert Pearl on 9 November 2011

+1 Agreed!  Thanks for sharing!

Leave a Comment

Please register or log in to leave a comment.