One of my sites has started getting sluggish lately. I thought it was just that the traffic was increasing and so I should optimize my code. Then, in the last couple of weeks, I had a 3AM scheduled task crash a few times. This weekend, I had a simple query with one row and one column take several minutes to execute even from the SQL Server Query Analyzer. This is getting weird!
The query looked something like this:
SELECT TOP 1 myfield FROM mytable
I realize there is no WHERE clause, but this still shouldn't take long.
A little bit of searching later and I run into this handy page:
The upshot is that your database should be reorganized on a regular basis. This site has been running for about 5 years and I have never done this (everyone who knew this already can kindly overlook my vast and impressive ignorance here).
This reminds me of a British reality show I watched recently with my wife wherein a man admits to having lived in his apartment for a few years without ever having cleaned his bathroom. Needless to say, it was not a pretty site. I imagine that my database tables are in an analagously ugly state.
I re-indexed the table using code like this:
DBCC DBREINDEX(maytable,' ',90)
Then I ran the SQL again. It improved from more than 10 minutes to well under one second. Wow!
Needless to I ran the code to re-index all of my tables shortly thereafter (see the link above).
I still have some optimizations that I need to perform on the site, but it is running MUCH faster now. No 3AM crashes any more either.
I cannot believe that I have been programming for around a decade now and I managed to avoid this essential bit of knowledge.
Incidentally, this experience also led me to find another good article: