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)
>
> 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 tes
t 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 whi
ch ever comes first. If
>you need it to be available all the time then you should look into the REOR
GANIZE 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@.j8g2000
cwa.googlegroups.com...
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment