
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;">> </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.
Any questions?
Please reach out to info@inspari.dk or +45 70 24 56 55 if you have any questions. We are looking forward to hearing from you.
Relaterede Posts
Filegroup confusion
Where did I put my Sales table?
Are you wasting storage space?
Do you see tables taking up much more space than expected? And when looking at the table design and...
New string conversion functions
SQL Server 2012 contains two new string conversion functions. PARSE and TRY_PARSE. These two...