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!