Index REBUILD question

Hey all,
One of the guys here (Tibor Karaszi) kindly pointed me to a script in
BOL that checks the fragmentation of indexes in a db, then depending on
the amount of fragmentation it REBUILDS or REORGANIZES the index. The
script I want to use is as it appears in BOL, but I've tweaked it to
specify a FILLFACTOR of 95 for the REBUILDS.
So my question is, if I have this script say running in a maintenance
plan at a 24/7 site, and it decides to REBUILD an index, if someone
indirectly accesses the table via our application would the application
receive an error as the table has been locked by the REBUILD?
Or will the query from the user wait until the lock on the table is
returned by the REBUILD process? (this is probably the ideal situation
for us)
>From BOL I understand it may have a shared lock (ie. readonly) at some
times.
But I'm also concerned about writes to the table.
We would prefer that the access is slow at that point rather than
returning an error message.
Can anyone clear this up for me?
Cheers!!
PS:
We can't use REBUILD ONLINE as
1) We use large object data types and
2) The client does not have Enterprise versionA REBUILD by default is a totally off-line process in that the table has an
Exclusive lock for the duration. So if the user tries to access the table it
will be blocked. How long is determined by the amount of time it takes to
rebuild the index or the timeout setting which ever comes first. If you need
it to be available all the time then you should look into the REORGANIZE
option instead. And in either case I would not recommend specifying the fill
factor in that script. By doing so you loose the ability to tune the fill
factors on individual indexes. If you do not specify it then it will use the
last setting. So if you initially want 95 then you should have built them
with 95 or manually issue a REBUILD with 95 as the setting. From then on
don't specify it again in the general rebuild script.
--
Andrew J. Kelly SQL MVP
<davconts@.gmail.com> wrote in message
news:1152064393.005694.10670@.j8g2000cwa.googlegroups.com...
> Hey all,
> One of the guys here (Tibor Karaszi) kindly pointed me to a script in
> BOL that checks the fragmentation of indexes in a db, then depending on
> the amount of fragmentation it REBUILDS or REORGANIZES the index. The
> script I want to use is as it appears in BOL, but I've tweaked it to
> specify a FILLFACTOR of 95 for the REBUILDS.
> So my question is, if I have this script say running in a maintenance
> plan at a 24/7 site, and it decides to REBUILD an index, if someone
> indirectly accesses the table via our application would the application
> receive an error as the table has been locked by the REBUILD?
> Or will the query from the user wait until the lock on the table is
> returned by the REBUILD process? (this is probably the ideal situation
> for us)
>>From BOL I understand it may have a shared lock (ie. readonly) at some
> times.
> But I'm also concerned about writes to the table.
> We would prefer that the access is slow at that point rather than
> returning an error message.
> Can anyone clear this up for me?
> Cheers!!
> PS:
> We can't use REBUILD ONLINE as
> 1) We use large object data types and
> 2) The client does not have Enterprise version
>|||In addition, if you have Enterprise Edition, you can do the rebuild with the ONLINE option.
REORGANIZE might be better for you than REBUILD with ONLINE, you have to test to see what suits you
best.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e5Uduv9nGHA.4848@.TK2MSFTNGP03.phx.gbl...
>A REBUILD by default is a totally off-line process in that the table has an Exclusive lock for the
>duration. So if the user tries to access the table it will be blocked. How long is determined by
>the amount of time it takes to rebuild the index or the timeout setting which ever comes first. If
>you need it to be available all the time then you should look into the REORGANIZE option instead.
>And in either case I would not recommend specifying the fill factor in that script. By doing so you
>loose the ability to tune the fill factors on individual indexes. If you do not specify it then it
>will use the last setting. So if you initially want 95 then you should have built them with 95 or
>manually issue a REBUILD with 95 as the setting. From then on don't specify it again in the general
>rebuild script.
> --
> Andrew J. Kelly SQL MVP
> <davconts@.gmail.com> wrote in message news:1152064393.005694.10670@.j8g2000cwa.googlegroups.com...
>> Hey all,
>> One of the guys here (Tibor Karaszi) kindly pointed me to a script in
>> BOL that checks the fragmentation of indexes in a db, then depending on
>> the amount of fragmentation it REBUILDS or REORGANIZES the index. The
>> script I want to use is as it appears in BOL, but I've tweaked it to
>> specify a FILLFACTOR of 95 for the REBUILDS.
>> So my question is, if I have this script say running in a maintenance
>> plan at a 24/7 site, and it decides to REBUILD an index, if someone
>> indirectly accesses the table via our application would the application
>> receive an error as the table has been locked by the REBUILD?
>> Or will the query from the user wait until the lock on the table is
>> returned by the REBUILD process? (this is probably the ideal situation
>> for us)
>>From BOL I understand it may have a shared lock (ie. readonly) at some
>> times.
>> But I'm also concerned about writes to the table.
>> We would prefer that the access is slow at that point rather than
>> returning an error message.
>> Can anyone clear this up for me?
>> Cheers!!
>> PS:
>> We can't use REBUILD ONLINE as
>> 1) We use large object data types and
>> 2) The client does not have Enterprise version
>