3. The Mystery of the Missing Index

  Sam had been in the office for just a week, but his thick-rim glasses and calm grin had already become a symbol of hope — and mild chaos. After his crazy first day, he thought he’d seen it all.

He was wrong.

The morning was calm. Sam was enjoying his coffee in the break room, listening to the gentle hum of the servers.

Ravi walked in. “Sam, got a minute?”

Sam raised an eyebrow. “Depends. Do you want me to have a minute?”

Ravi laughed. “We’ve got a mystery. A report that used to take five seconds now takes five minutes. And the customer is not happy.”

Sam’s eyes twinkled. “Alright. Let’s see what’s going on.”

Sam fired up SSMS and took a look at the execution plan. It was ugly — a giant table scan that screamed for help.

He leaned back and said, “Well, there’s the first clue.”

The team gathered around.

Priya frowned. “This report was never this slow. What changed?”

Sam typed quickly, checking the missing index DMVs and index usage stats. He even looked at the query text to see if someone had rewritten it overnight.

David, calm as always, said, “If it’s an index problem, we’ll find it.”

Finally, in the query stats, there was a little note from SQL Server — almost like a polite tap on the shoulder:

“Missing Index: Create a nonclustered index on [Orders] (OrderDate). Estimated Improvement: 99.9%.”

Sam chuckled. “A 99.9% improvement? That’s not a hint. That’s SQL Server practically begging us to fix it!”

The room laughed, but Sam knew there was more.

“Wait,” said Asha, peeking over her notes. “Didn’t we already have that index last week?”

Sam paused. “You’re right. Let’s check the change logs.”

He pulled up the schema change logs and found the culprit. A junior developer had dropped the index during some table cleanup. His note read: “Removed unused index for performance.”

Sam burst out laughing. “Removed for performance… and now the performance is gone!”

Sam recreated the missing index with a simple name: “IX_Orders_OrderDate.” No fuss.

He ran the report again. Five seconds. Just like before.

Manager Mike strolled by and asked, “Mystery solved?”

Sam grinned. “Index found, report fast, everyone happy. I think we’re good.”

The Lesson Learned

Sam took a sip of his coffee, now cold, and turned to the team.

“Here’s the thing,” he said, adjusting his glasses. “When something slows down, always start with the execution plan. Table scans are usually a big clue.”

“Then check the missing index DMVs and index usage stats to see if the server is telling you what’s missing.”

“If the index was there before, check the change logs. Sometimes it’s just one ‘harmless’ drop that causes a big mess.”

“Finally, recreate the index with a good, clear name — and watch your queries fly again.”

A Few Tips for Readers

• Use clear index names. Avoid stuff like “Index1.”
• Don’t drop indexes without checking if they’re actually used.
• A missing index can turn a quick seek into a long table scan, slowing everything down
• Before creating any index, especially if there’s no obvious clue like the missing index being dropped accidentally (as in this story), and if there’s a separate development team in your environment, always confirm with them before proceeding. This ensures you’re aligned with their design decisions and won’t accidentally create duplicate or conflicting indexes.
• And if you’re not sure, always ask for a second opinion — or let your friendly neighborhood DBA help out. 😀

Technical Tips and Assignment

Dear reader, In this story, the following DMVs and commands were key tools for investigating and fixing the missing index issue. Below is the list and their purpose. Take some time to explore these in your own environment — see how they can help when you face similar performance mysteries!

• sys.dm_db_missing_index_details — Lists missing index suggestions from SQL Server’s internal monitoring.
  
  
• sys.dm_db_index_usage_stats — Shows how existing indexes are being used, to spot missing or unused indexes.
  
  
• Execution Plan in SSMS — Visually confirms if queries are doing table scans instead of index seeks.
  
  
• CREATE NONCLUSTERED INDEX — Command to create a missing index and improve performance.
  
  
• fn_dblog — (if available) Checks recent schema changes in the transaction log to spot dropped indexes.