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.