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

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
this sort of things can also be done by creating a database maintenance plan.
# Posted By dfguy | 6/19/08 5:02 AM
Yes, that is the normal setup. We double checked the plan and the option to reindex was not selected, but it is now!
# Posted By Tim Jackson | 6/22/08 7:47 AM
Oh yes this is a revelation to figure this out. I had the same experiences a year ago :)

I found out some other stuff helping me with some of my very large tables. You can see that on my blog:
# Posted By SQLGott | 9/29/11 1:56 AM
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.