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
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.
Republished from geniiius.com [45 clicks].
Read the original version here [32134 clicks].