Indexing

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!You can add indexes using the ONLINE option (some restrictions, see Books On
line, 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 crea
te 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 resour
ce 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@.NOSPAMtimeplus.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/data
bases 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 rig
ht fields), but I'm new
> at this, if you can't tell. Thanks!
>|||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 !!!!!!!!!!