How to reduce the database size - Part 3

In the second part of this series, I explained how to use SQL Server data compression to reduce your database size. Here I explain how index tuning can help you to reduce your database size too.

Removing Unused Indexes

The downside to having too many indexes is that SQL Server has to maintain all of these indexes which can hurt DML performance and indexes also require additional storage. The script below can be used to identify the indexes possibly unused exist in the current database.

select
object_name(i.object_id) as ObjectName,
i.name as [Unused Index],
i.is_unique,
i.is_primary_key,
CASE i.index_id WHEN 0 THEN 'heap' WHEN 1 THEN 'Clustered' ELSE 'Non-clustered' END as is_clustered,
s.user_updates,
SUM(p.[used_page_count]) * 8 / 1024 AS indexSizeMB,
STATS_DATE(i.object_id, i.index_id) AS statistics_update_date
from sys.indexes i
left join sys.dm_db_index_usage_stats s on s.object_id = i.object_id and i.index_id = s.index_id and s.database_id = db_id()
join sys.dm_db_partition_stats p on i.object_id = p.object_id and i.index_id = p.index_id
where objectproperty(i.object_id, 'IsIndexable') = 1
and objectproperty(i.object_id, 'IsIndexed') = 1
and (
s.index_id is null -- and dm_db_index_usage_stats has no reference to this index
or (s.user_updates > 0 and s.user_seeks = 0 and s.user_scans = 0 and s.user_lookups = 0)
) -- index is being updated, but not used by seeks/scans/lookups
group by i.object_id, i.index_id, i.name, s.user_updates, i.is_unique, i.is_primary_key
order by indexSizeMB desc

Rather than running the script on a testing server which I mostly suggested, this script should only be RUN ON PRODUCTION server, as only the Production server statistics are accurate. Bear in mind that the statistics resets when the SQL Server service is restarted. In SQL Server 2012 and 2014, statistics resets when the index is rebuilt. Below figure illustrates the output of this script:

As you can see, the output includes table name, index name, whether it's a unique/primary key index, clustered or non-clustered, index size in MB, and the last statistics update time (remember that index rebuild also includes statistics update). A unique index may be used for a unique constraint, you must take attention to prevent violating business requirements for the system before considering to remove it. From the output of this unused index script, if you found an index is large, it's a non-clustered index (as clustered index is the table itself, dropping a clustered index make all the non-clustered indexes must be rebuilt, so you must think very carefully before dropping clustered index), and index rebuild time is quite a long time ago, then you can consider to drop that index. Keep in mind that you always need to carefully test your system when you drop any indexes making sure that there is no performance impact after.