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
(
select
t1.database_id,
t1.object_id,
t1.index_id,
t3.partition_number,
t3.used_page_count
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)
where
    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
)
select
database_id,
object_id,
index_id,
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.

@geniiiuscom