Database Madness – Day 2

 

Thursday morning rolled around. In the words of Arthur Dent, fictional character of Hitchhiker’s Guide to the Galaxy: “This must be Thursday. I never could get the hang of Thursdays.”

 

 I take a train into work, so I get up early and arrive by just after 6am. I have two custom jobs that send me an email when they complete, so I’m used to seeing two emails every morning. This particular morning, there was only one. This could be one of two things. Either we didn’t receive the file from the client, or something was awry with the processor. We did indeed receive the file, so that ruled out the first possibility. The processor had picked up the job, so that ruled out the second possibility. Very strange. There was a third possibility that I hadn’t originally considered.

 

I checked the list of tasks the system was processing, expecting to see the normal 4 or 5 at this time of day. There were nearly 150. Yikes!

 

In that list was a very odd thing. One client sent 16 import jobs through our user interface so they were all processing simultaneously. Well, surely that would be the culprit. I stopped the importer, but things didn’t get any better. I ran a few simple queries against the database and they were coming back with good speed, so no problems there. I checked for blocking queries, and there were some, but a refresh showed them going away quickly.

 

As the morning progressed, tensions rose. Folks started ambling into the office and awareness that we have a problem quickly entered their coffee-starved minds. This was going to be a long day. By 10am, the situation was beginning to get dire. Publishing jobs were going so slow, and normally fast queries were taking a very long time to run. There was nothing weird with the database except for a very high CPU utilization that we were accustomed to seeing because of one client who had intense jobs. However, it was higher than normal – 98 to 100%. I ran a DBCC SHOWCONTIG on a suspect table and it had 36% fragmentation. We usually re-index this table evey 5 weeks or so because it has a tendency to get fragmented easily.

 

Executives were there, and I recommended restarting the server since we couldn’t pinpoint anything. Even SQL Server Profiler wasn’t showing us any singular query that was a suspect. All agreed, and so we stop SQL Server, and for good measure, rebooted the box. I also recommended taking the scheduled maintenance window for that evening to reindex this table. All agreed. The database came back up, was slow, but still running. We’d have to limp along until evening.

 

Thursday evening came around and we reindexed that table and one other that had a high fragmentation percentage as well. We brought the system back up and things were running, but we noticed an unusual slowness in several areas. But things were running. We were tired, and we decided to leave it until morning.