Now the funny thing about it, not a story without it, but the story is mine and it ended just fine…Happily Ever After

Today I have a Vendor Application that has WAY too many indexes and they need to rebuild them all tonight in a very tight window.  I am disabling all the unused indexes to allow for a faster rebuild and then will rebuild the disabled indexes tomorrow night after all the heavy work is done.  The Vendor is nervous about us disabling indexes that haven’t been used in over a month.  The good news is that you can simply disable them and then rebuild them if they are needed.  I know the rebuild will take some time, but we have all agreed that if they haven’t been used in over a month, they most likely won’t be used in the next 24 hours.  Now the good part. Running this t-sql code not only tells you what indexes have not been used since the last reboot, but it also gives you a rebuild indexes script that can easily be changed to a disable script. The scripts can all be run at once and will save you oodles of time.

SELECT OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName,
OBJECT_NAME(I.OBJECT_ID) AS ObjectName,
I.NAME AS IndexName,
'ALTER INDEX [' + I.Name + '] ON ' + OBJECT_NAME(I.OBJECT_ID) + ' ReBuild' --' Disable'
AS SQLToRebuildOrDisable
FROM sys.indexes I
WHERE /* only get indexes for user created tables*/
OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1
/* making sure it is not a primary key*/
and i.is_primary_key = 0
and i.is_unique = 0
/* If the are returned in this table, they are in use so we are getting everything not in this table.*/
AND NOT EXISTS (
SELECT index_id
FROM sys.dm_db_index_usage_stats
WHERE OBJECT_ID = I.OBJECT_ID
AND I.index_id = index_id
-- This will limit to the db you are on.
AND database_id = DB_ID())
-- The Index must have a name for this to work.
AND I.name IS NOT NULL
ORDER BY SchemaName, ObjectName, IndexName

I hope it helps you as much as it has helped me today!
Have a magical day!