Disabled Indexes Can Hurt You – Badly
Recently I read a white paper put together by Brent Ozar entitled “Six Scary SQL Surprises” at http://www.red-gate.com/products/dba/dba-bundle/entrypage/hard-earned-lessons-3. If you support any database it is worth your time to read. I have been working with databases for decades (more than I want to admit) and I still found ideas I hadn’t considered.
I don’t want to steal Brent’s thunder by telling you what all six Scary SQL Surprises are; you’ll need to read his white paper for that. However, one of his topics hit close to home, and solved a current need.
Recently I was reviewing the query plan for a procedure that should have been using an index for the execution plan. Checking the available index definitions, there was an index perfectly suited (and created) for the problem being solved. But the query optimizer would not use the index unless I forced it in the query statement using a WITH (index=”myindexname”) type syntax.
Worse yet, forcing the index simply caused the query to perform worse than not using it. Brent’s white paper provided me with the solution to the problem. The index I was trying to use had been disabled!
In SQL Server you have the ability to disable an index on a table. This way you can stop maintenance of the index while maintaining the definition of the index. The index can be re-enabled with a REBUILD of the index.
There are lots of reasons for disabling an index. Brent even shares a few. He also provides a technique to get notification when indexes have been disabled and not re-enabled. So, be sure to look at his white paper for more detailed information.
Having found the soution to my query problem I wanted to find out if ther were more databases containing disabled indexes. Here is the query I used to locate all disabled indexes for a complete instance of SQL Server.
IF OBJECT_ID('TempDB..#InstanceDisabledIndex') IS NOT NULL
DROP TABLE #InstanceDisabledIndex
GO
CREATE TABLE #InstanceDisabledIndex
(
DatabaseName SYSNAME NOT NULL
,DisabledIndexCount Int NOT NULL
,CONSTRAINT pk_InstanceDisabledIndexes PRIMARY KEY CLUSTERED (DatabaseName)
)
GO
EXEC master.sys.sp_MSforeachdb
'
INSERT INTO #InstanceDisabledIndex
SELECT ''?'' as DatatbaseName
, COUNT(1) AS DisabledIndexCount
FROM [?].sys.indexes
WHERE is_disabled = 1
'
SELECT
DatabaseName
,DisabledIndexCount
FROM #InstanceDisabledIndex
Have you found disabled indexes to be a problem in the past? Do you have other similar scenarios we should watch out for? Share your experience by dropping an email to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Moving your SSIS package to a new server
Whenever we develop a package in SSIS, we need to make sure that it works in Production environment the same way it works in the development environment. But how do you make sure that it happens? Read further to find out.
Featured White Paper(s)
How to Use SQL Server’s Extended Events and Notifications to Proactively Resolve Performance Issues
read more)