Microsoft – SQLServer – Indexes <Disabled>
Playing a bit with tables that have quite a bit of indexes and so wanted to measure Performance Implications of disabling some of them.
And, then later on needed to run some queries and observed\reviewed the Query Plan.
Saw that SQL Server suggested some indexes, but wondered about one I thought would have being used.
Issued sp_helpindex <object-name>
And, it still had those Indexes.
So went ahead and added the with index hint
select * from dbo.tblMine with (INDEX=myIndex, NOLOCK)
And, system came back with :
Msg 315, Level 16, State 1, Line 21 Index <index-name> on table <table-name> (specified in the FROM Clause is disabled or resides in a filegroup which is not online).
So, yes let us go re-enable that index, as least until we complete this important query:
ALTER INDEX <index-name> ON <table-name> REBUILD with ( ONLINE = ON ) ;
This is a sample query that lists all the indexes on a table, along with the enable status:
select object_name(tblIndex.object_id) as objectName , tblIndex.name , tblIndex.type_desc , tblIndex.is_disabled , case when (is_disabled =0) then 'Yes' else 'No' end as [Enabled] from sys.indexes tblIndex where object_name(tblIndex.object_id) in ('ObjectName')