How much space are you wasting?
The Below script would provide us 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 WastedMB
AS
(
SELECT
t1.database_id,
t1.object_id,
t1.index_id,
t2.name,
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,
name,
COUNT(*)
AS numofpartitions,
SUM(used_page_count) AS
UsedPages,
(SUM(used_page_count) * 8) / 1024 AS spacewasteinmb
FROM
WastedMB
GROUP BY database_id, object_id, index_id,name
ORDER BY
1,2,3
COMPUTE SUM((SUM(used_page_count) * 8) / 1024)
As always be careful when you drop indexes – better check an extra time
before dropping