Make sure your nonclustered indexes are enabled

I know this post might sound obvious. This is a very rare problem. But this actually happened to me, and it might happen to you!

Suddenly, all queries on a test system were running slowly

One morning, I was called into a system where every single query was running slowly. System resources like CPU and memory were over-utilized, and it looked like the entire system was struggling to keep up.

I checked wait statistics, PerfMon, and sp_WhoIsActive. These just further reinforced the idea that the system was experiencing a heavier workload than normal.

How it was discovered

There’s actually a really simple query to find out if your indexes are disabled.

USE [Your-database-here]
GO
SELECT name from sys.indexes WHERE is_disabled = 1

Unfortunately, enabling indexes isn’t as easy

Here’s the snippet from SQL Server Management Studio on disabling/enabling indexes:

That’s right, in order to re-enable an index, you have to rebuild it. Ouch.

How did the indexes become disabled?

Well, to disable an index, you have to run an alter index script on each index. In my situation, a script that disabled indexes ran on the wrong environment.

Thanks for reading! Hopefully this rare issue doesn’t occur to you. Stay tuned!


By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close