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...
> > 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.
> >
> >
>|||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...
> > 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.
> > >
> > >
> >
> >
>|||"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/prodtechnol/sql/2000/maintain/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...
> > 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.
> > >
> > >
> >
> >
>|||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/prodtechnol/sql/2000/maintain/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...
> > 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.
> > >
> > >
> >
> >
>

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...[vbcol=seagreen]
> 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
>
|||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[vbcol=seagreen]
> 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
> 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/pro.../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[vbcol=seagreen]
> 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
> 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/pro.../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[vbcol=seagreen]
> 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
> on
> for
> but
> other
>

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
>

Any blocking when we extend the data files ?

Will there be any blocking ifi grow the data files and Log files from say
5GB to 25GB on a highly transactional system ? Let me know your thoughts.Hi
No locks are taken at data level, but the high I/O does affect performance
on queries.
Do it when usage is lowest and like normal, test it on a non-production
system before you do it.
Regards
Mike
"Hassan" wrote:
> Will there be any blocking ifi grow the data files and Log files from say
> 5GB to 25GB on a highly transactional system ? Let me know your thoughts.
>
>

Any blocking when we extend the data files ?

Will there be any blocking ifi grow the data files and Log files from say
5GB to 25GB on a highly transactional system ? Let me know your thoughts.
Hi
No locks are taken at data level, but the high I/O does affect performance
on queries.
Do it when usage is lowest and like normal, test it on a non-production
system before you do it.
Regards
Mike
"Hassan" wrote:

> Will there be any blocking ifi grow the data files and Log files from say
> 5GB to 25GB on a highly transactional system ? Let me know your thoughts.
>
>

Any better monitoring for locks and blocking in SQL2005?

Are the facilities for monitoring locks and blocking in SQL2005 better than
2000?
I found that in SQL2000 Enterprise Manager by the time you waited for
"current activity" to refresh, the information could have been and gone.
I'd be interested to hear if you've been using it sucessfully to do this
sort of thing.
If you want something similar to Current Activity, Activity
Monitor in Management Studio is an improvement over the
Current Activity node in Enterprise Manager. Opens in a
separate window, you have a few different view to chose
from, you can set a refresh interval.
2005 also has server level reports such as Blocking
Transactions and Top Transactions by Locks.
But just like Current Activity, you are seeing a snapshot
and you need to refresh. You have more options in 2000 to
understand the blocking, locking if you use queries to
monitor activity and it's somewhat the same in 2005.
However, there are improvements to the monitoring and
details you can get related to locking, blocking. In 2005,
you have a lot more details exposed on the waits. You can
also set up a blocked process threshold and use this with
the Blocked Process Report event class in Profiler, SQL
trace. You can also get detailed historical information
related to locking and blocking from the DMV
Sys.dm_db_index_operational_stats.
The following article has a lot of information on you can
use to monitor blocking, locking issues in 2005:
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/pro.../tsprfprb.mspx
-Sue
On Thu, 19 Jan 2006 04:50:02 -0800, "James"
<James@.discussions.microsoft.com> wrote:

>Are the facilities for monitoring locks and blocking in SQL2005 better than
>2000?
>I found that in SQL2000 Enterprise Manager by the time you waited for
>"current activity" to refresh, the information could have been and gone.
>I'd be interested to hear if you've been using it sucessfully to do this
>sort of thing.
|||Night and day, across the board. SQL Server 2000 exposed very limited
information to be able to monitor. SQL Server 2005 has layer after layer
after layer of diagnostics. From DBCC commans through DMVs/DMFs, there
aren't many areas of the engine you can't get at.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"James" <James@.discussions.microsoft.com> wrote in message
news:98B66E56-8895-4040-BA63-056144C882C6@.microsoft.com...
> Are the facilities for monitoring locks and blocking in SQL2005 better
> than
> 2000?
> I found that in SQL2000 Enterprise Manager by the time you waited for
> "current activity" to refresh, the information could have been and gone.
> I'd be interested to hear if you've been using it sucessfully to do this
> sort of thing.