MS SQL Server - Indexing

Author

Indexing

Chris Huddle

2006-04-04, 1:23 pm

If I need to add indexes to a large number of SQL Server 2005
tables/databases tables, can I do it during the day when the database is in
use? Or is this something that I should do after hours? Adding and
dropping indexes seems pretty benign (assuming you pick the right fields),
but I'm new at this, if you can't tell. Thanks!

Tibor Karaszi

2006-04-04, 1:23 pm

You can add indexes using the ONLINE option (some restrictions, see Books Online, the updated
version, CREATE INDEX). ONLINE mean that SQL Server will not acquire a lock on the data while the
index is added. ONLINE is only available on Enterprise Edition.

If you don't use ONLINE, the table will be locked by shared lock if you create non-clustered index,
or exclusive lock if you create clustered index. This will obviously have an impact of those who try
to access the tables while the index is being created.

Apart from the locking and blocking situation, the will be an obvious resource usage while the index
is being created.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

"Chris Huddle" < chuddle@NOSPAMtimepl
us.com> wrote in message
news:OMlTwkAWGHA.5148@TK2MSFTNGP12.phx.gbl...
> If I need to add indexes to a large number of SQL Server 2005 tables/databases tables, can I do
> it during the day when the database is in use? Or is this something that I should do after hours?
> Adding and dropping indexes seems pretty benign (assuming you pick the right fields), but I'm new
> at this, if you can't tell. Thanks!
>

Doug

2006-04-06, 8:23 pm

ummm, adding indexes during the day pretty much locks out the users
from accessing that table while the indexes are being made.

if the users don't mind, then neither do I !!!!!!!!!!

Sponsored Links