Slow Table in SQL Server? Re-Index it!

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:

Tips for Rebuilding Indexes

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:

Useful SQL Server DBCC Commands