Any inventive ways to DBREINDEX and minimize blocking

I would like to come up with a routine in which I could execute a DBREINDEX
without taking downtime in my application. The tables that I want to
REINDEX are pretty small (the reindexing should take no more than 10-15
seconds).
The problem is that if the table i want to REINDEX has an IX lock on the
table, then the SPID trying to runs the REINDEX waits with a Sch-M lock on
that table. The Sch-M lock in turn then blocks everyone else from obtaining
an IX lock. So if I had some busy process that was running for a long time
holding a IX lock (let's say 30 seconds), the reindex would be blocked for
up to 30 seconds. No big deal to me if the reindex spid gets blocked but
what I am concerned about it the blocking that it will in turn do to other
spids waiting for an IX lock.
I was hoping that I could define some process like:
WHILE 1=1
BEGIN
(If an Sch-M lock is available without actually taking it)
BEGIN
DBCC DBREINDEX table
RETURN
END
END
Any comments would be greatly appreciated.Why are you DBREINDEXing instead of INDEXDEFRAGing?
The latter is an online operation.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> I would like to come up with a routine in which I could execute a
DBREINDEX
> without taking downtime in my application. The tables that I want to
> REINDEX are pretty small (the reindexing should take no more than 10-15
> seconds).
> The problem is that if the table i want to REINDEX has an IX lock on the
> table, then the SPID trying to runs the REINDEX waits with a Sch-M lock on
> that table. The Sch-M lock in turn then blocks everyone else from
obtaining
> an IX lock. So if I had some busy process that was running for a long
time
> holding a IX lock (let's say 30 seconds), the reindex would be blocked for
> up to 30 seconds. No big deal to me if the reindex spid gets blocked but
> what I am concerned about it the blocking that it will in turn do to other
> spids waiting for an IX lock.
> I was hoping that I could define some process like:
> WHILE 1=1
> BEGIN
> (If an Sch-M lock is available without actually taking it)
> BEGIN
> DBCC DBREINDEX table
> RETURN
> END
> END
> Any comments would be greatly appreciated.
>|||I've seen a few instances where INDEXDEFRAG does not bring the Scan Density
to 100% like the DBREINDEX does.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
> Why are you DBREINDEXing instead of INDEXDEFRAGing?
> The latter is an online operation.
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> DBREINDEX
on[vbcol=seagreen]
> obtaining
> time
for[vbcol=seagreen]
but[vbcol=seagreen]
other[vbcol=seagreen]
>|||That's because it's not as thorough as the DBREINDEX. It's used in
situations where db's cannot be brought down (ie 24x7 shops). Among
the things it doesn't defrag are extent hops.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
10-15[vbcol=seagreen]
the[vbcol=seagreen]
lock[vbcol=seagreen]
> on
> for
> but
> other
>|||"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
But does that really matter? You have a requirement of staying online,
obviously, so you kind of have to take whatever you can get... How much of a
performance impact, especially given your small tables, do you expect from
90% or 95% density vs. 100%?|||Scan Density is mostly irrelevant, as explained in Books OnLine. Please read
the whitepaper below which gives all the details.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
10-15[vbcol=seagreen]
the[vbcol=seagreen]
lock[vbcol=seagreen]
> on
> for
> but
> other
>|||Scan density should not be the factory you are looking at in most cases
anyway. Logical frag is the first thing you should seek. If you have more
than 1 file in your file group extent switching and all that goes with it is
not an accurate indicator in showcontig and should not be used for
determining fragmentation status in general. Bottom line is DBREINDEX is an
entirely off line operational and if you can't afford that you have to use
INDEXDEFRAG until Yukon comes out. This is a must read:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Andrew J. Kelly SQL MVP
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
10-15[vbcol=seagreen]
the[vbcol=seagreen]
lock[vbcol=seagreen]
> on
> for
> but
> other
>