Microsoft – SQLServer – Indexes (Disabled)

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.

Did so

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')

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s