Simple • Straightforward

So easy a Caveman
Programmer can do it.

›› Let's Get Started

Rebuilding Indexes

First You Make Them, Now Maintain Them

In our previous article, we discussed keeping the statistics updated. That task usually gets encompassed every night when you rebuild your indexes. Why might you need to rebuild your indexes though?

An interesting issue you may come across is when all of a sudden, some queries start running slowly. Not all of them, just like 1-2. This usually happens on queries that use large tables, often tables that have several updates happening to them. I have seen some import queries suddenly go from taking 5 minutes to run, up to an hour… just all of a sudden. After doing some research, we found that the indexes were not being rebuilt and statistics were not getting updated daily. As a matter of fact, those processes had not run for several months.

An easy way to solve this is to rebuild your database indexes nightly, as seen in the screen shot below.

Maintenance Plan Wizard

When click on the Reorganize data and index pages, you will notice that the Update Statistics checkbox grays out. That is because the statistics will be updated as part of rebuilding the indexes.



Small vs. Large Databases

You might find that rebuilding all of the indexes during the night takes too long when you have a large database 100GB+. If your database is that large, you can consider rebuilding the necessary indexes throughout the day. Here is the syntax to rebuild indexes while not locking up the table (This only works for Enterprise Editions of SQL Server):

For a single index: ALTER INDEX idx_name ON table_name REBUILD WITH(ONLINE = ON)
For all indexes on a table: ALTER INDEX ALL ON table_name REBUILD WITH(ONLINE = ON)

If you don't have an Enterprise edition, run the same syntax without the REBUILD WITH(ONLINE = ON) piece. Be mindful that it will lock the table though.





Comments

Name:
Title:   
Commenting Currently Disabled


Ahmad
8/25/2012 6:53:51 AM
eDLwczVcxQ


This is a wide topic.The documents can be sterod in a variety of ways. One of them is storing them as binary data in MS SQL. This offers advantages and disadvantages. The advantages are offered in terms of security and the fact that SQL Server offers transaction support. The disadvantage is that retrieving the data will be slow since SQL Server pages data in 8 KBs, hence retrieving a document from SQL Server will result in lots of input / output being generated.Another approaches is store the documents on an FTP and simply store references of your documents in the DB. FTP's are much faster they are built exactly to support file transport.There are lots of products that offer document management workflow management. You can also try to use one instead of trying to reinvent the wheel.Hope this helps.
Keisha
8/4/2011 1:37:45 AM



Got it! Thanks a lot again for hlepnig me out!
Jason
7/10/2011 3:47:49 PM
Necessary Clarification


I bolded the necessary statement, just so it is clear.
Gail Shaw
7/10/2011 10:51:05 AM
Online yes, but...


I wouldn't suggest rebuilding during business hours if there isn't enough time overnight to rebuild everything. It may be mostly online (though even on Enterprise there are limitations), but it still takes some locks and causes massive IOs. It will impact performance. On larger databases you simply shouldn't be rebuilding everything every time. Even ignoring the time, if my DB is 1 TB rebuilding everything every night will generate easily 1 TB of log space which may need to be backed up, log shipped, mirrored, etc. Blanket rebuild of everything is a poor idea unless you're working with tiny databases (400GB or so). Rebuild just what needs rebuilding, not everything. There are many good scripts out there that do just that. http://ola.hallengren.com/ and http://sqlfool.com/2011/06/index-defrag-script-v4-1/ being two that come immediately to mind.
Jason
7/7/2011 5:39:40 PM
Small vs Large DBs


Hi Gail - great point! I have added a section that explains how to rebuild a single index.
Gail Shaw
7/7/2011 2:31:56 PM
Fine for smaller DBs...


Rebuilding everything nightly works fine on smaller databases. Once you start playing with several hundred GB or a couple TB it generally takes too long and uses far too much log. Selective index rebuilds are a far better idea. Rebuild what needs rebuilding, leave the rest alone. There are several good scripts available that will do all that.