How much space are you wasting?

A few weeks back I was visiting a client who 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:

<span style="color:blue;">with </span>cte <span style="color:blue;">as
</span><span style="color:gray;">(
</span><span style="color:blue;">select
</span>t1<span style="color:gray;">.</span>database_id<span style="color:gray;">,
</span>t1<span style="color:gray;">.</span><span style="color:magenta;">object_id</span><span style="color:gray;">,
</span>t1<span style="color:gray;">.</span>index_id<span style="color:gray;">,
</span>t3<span style="color:gray;">.</span>partition_number<span style="color:gray;">,
</span>t3<span style="color:gray;">.</span>used_page_count
<span style="color:blue;">from </span><span style="color:green;">sys</span><span style="color:gray;">.</span><span style="color:green;">dm_db_index_usage_stats </span>t1
<span style="color:gray;">inner join </span><span style="color:green;">sys</span><span style="color:gray;">.</span><span style="color:green;">indexes </span>t2
<span style="color:blue;">on </span><span style="color:gray;">(</span>t1<span style="color:gray;">.</span><span style="color:magenta;">object_id </span><span style="color:gray;">= </span>t2<span style="color:gray;">.</span><span style="color:magenta;">object_id</span><span style="color:gray;">) AND (</span>t1<span style="color:gray;">.</span>index_id <span style="color:gray;">= </span>t2<span style="color:gray;">.</span>index_id<span style="color:gray;">)
inner join </span><span style="color:green;">sys</span><span style="color:gray;">.</span><span style="color:green;">dm_db_partition_stats </span>t3
<span style="color:blue;">on </span><span style="color:gray;">(</span>t1<span style="color:gray;">.</span><span style="color:magenta;">object_id </span><span style="color:gray;">= </span>t3<span style="color:gray;">.</span><span style="color:magenta;">object_id</span><span style="color:gray;">) AND (</span>t1<span style="color:gray;">.</span>index_id <span style="color:gray;">= </span>t3<span style="color:gray;">.</span>index_id<span style="color:gray;">)
</span><span style="color:blue;">where
    </span>database_id <span style="color:gray;">= </span><span style="color:magenta;">DB_ID</span><span style="color:gray;">() and
    </span>user_seeks <span style="color:gray;">= </span>0 <span style="color:gray;">and </span>user_scans <span style="color:gray;">= </span>0 <span style="color:gray;">and </span>user_lookups <span style="color:gray;">= </span>0 <span style="color:gray;">and
    </span><span style="color:magenta;">OBJECTPROPERTY</span><span style="color:gray;">(</span>t1<span style="color:gray;">.</span>[object_id]<span style="color:gray;">,</span><span style="color:red;">'IsUserTable'</span><span style="color:gray;">) = </span>1 <span style="color:gray;">and </span>t2<span style="color:gray;">.</span>index_id <span style="color:gray;">&gt; </span>1 <span style="color:gray;">and
    </span>t2<span style="color:gray;">.</span>is_unique <span style="color:gray;">= </span>0 <span style="color:gray;">and </span>t2<span style="color:gray;">.</span>is_unique_constraint <span style="color:gray;">= </span>0
<span style="color:gray;">)
</span><span style="color:blue;">select
</span>database_id<span style="color:gray;">,
</span><span style="color:magenta;">object_id</span><span style="color:gray;">,
</span>index_id<span style="color:gray;">,
</span><span style="color:magenta;">COUNT</span><span style="color:gray;">(*) </span><span style="color:blue;">as </span>numofpartitions<span style="color:gray;">,
</span><span style="color:magenta;">SUM</span><span style="color:gray;">(</span>used_page_count<span style="color:gray;">) </span><span style="color:blue;">as </span>UsedPages<span style="color:gray;">,
(</span><span style="color:magenta;">SUM</span><span style="color:gray;">(</span>used_page_count<span style="color:gray;">) * </span>8<span style="color:gray;">) / </span>1024 <span style="color:blue;">as </span>spacewasteinmb
<span style="color:blue;">from </span>cte
<span style="color:blue;">group by </span>database_id<span style="color:gray;">, </span><span style="color:magenta;">object_id</span><span style="color:gray;">, </span>index_id
<span style="color:blue;">order by </span>1<span style="color:gray;">,</span>2<span style="color:gray;">,</span>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:

<span style="color:blue;">COMPUTE </span><span style="color:magenta;">SUM</span><span style="color:gray;">((</span><span style="color:magenta;">SUM</span><span style="color:gray;">(</span>used_page_count<span style="color:gray;">) * </span>8<span style="color:gray;">) / </span>1024<span style="color:gray;">)
</span>

Putting it all together, and running the query gives me a result 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.

Important

This code is written for SQL Server 2008 R2 – COMPUTE has been removed with the release of SQL Server 2012, so do not use this feature in your work.