Another one about blocking

This is weird, but I hope someone can help me out:
MSSQLServer 2000, SP 2, W2K Server
Dedicated server with one DB, 60 concurrent users
Locktimeout is set
The DB is accesses via my app only.
About once a week the DB runs into a blocking situation, wich locks up all
other users. Although the lock timeout is set, no timeout occurs. I
monitored the DB for the blockees and got grip of the blocking statement:
select count(*) as c FROM TBLMWTRACE M WITH (NOLOCK) WHERE m.datum = Convert(datetime, '20031120', 112) and ((GELESEN IS NULL) or (GELESEN = 'N')) AND (M.HAUSCD = 'BRI')
I researched my app and I am quite sure, that there are no transactions left
open. If lengthy select statements were the cause, they would have been
captured by the monitor.
My assumption is, that if SQLServer gets under load, he starts issuing
x-locks without releasing them. But this can´t be true, of course.
What can I do to track down what the causes the blocks?
Thanks BerndHi Bernd !
Seems, that the option "with (NOLOCK)" does not work as expected. This
option is identical to transaction mode set to uncommmitted, that means,
that no locks are set, incoinsitencies may occurr. Have you considered
the "lock escalation" ? SQL Server 2000 automatically switches to table
lock, whenever too many users try to access a database.
My advice. Use databases with MVTO, MVRO, your problems will disappear.
look for it in google groups.
regards, Guido Stepken
Bernd Maierhofer (dato) wrote:
> This is weird, but I hope someone can help me out:
> MSSQLServer 2000, SP 2, W2K Server
> Dedicated server with one DB, 60 concurrent users
> Locktimeout is set
> The DB is accesses via my app only.
> About once a week the DB runs into a blocking situation, wich locks up all
> other users. Although the lock timeout is set, no timeout occurs. I
> monitored the DB for the blockees and got grip of the blocking statement:
> select count(*) as c FROM TBLMWTRACE M WITH (NOLOCK) WHERE m.datum => Convert(datetime, '20031120', 112) and ((GELESEN IS NULL) or (GELESEN => 'N')) AND (M.HAUSCD = 'BRI')
> I researched my app and I am quite sure, that there are no transactions left
> open. If lengthy select statements were the cause, they would have been
> captured by the monitor.
> My assumption is, that if SQLServer gets under load, he starts issuing
> x-locks without releasing them. But this can´t be true, of course.
> What can I do to track down what the causes the blocks?
> Thanks Bernd
>

Another locking question

From this article by
Kalen...http://www.sqlmag.com/Articles/Index.cfm?ArticleID=26922...
she mentioned
"For INSERTS which are blocking, you might want to make sure that your
clustered index is NOT on a column that determines the order that the new
rows are arriving. For example, don't put your clustered index on OrderDate
or OrderNumber, which will always be increasing"
My quesion is why not ?I believe Kalen is just saying that if your inserts are causing blocking you
would not be helping the situation by having an index that uses these same
inserts as the logic to always through the data to the bottom of the last
leaf level of the table (clustered). I think that for inserts that are not
causing blocking it is quite acceptable to use a clustered index on a
sequential key (like an identity column).
Later in that same article Kalen talks about key locks, I've fixed quite a
few blocking issues by adding a clustered index to a table which then would
acquire these key locks. So you would have to test to ensure your inserts
are causing blocking.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OivRkZZhDHA.484@.TK2MSFTNGP12.phx.gbl...
> From this article by
> Kalen...http://www.sqlmag.com/Articles/Index.cfm?ArticleID=26922...
> she mentioned
> "For INSERTS which are blocking, you might want to make sure that your
> clustered index is NOT on a column that determines the order that the new
> rows are arriving. For example, don't put your clustered index on
OrderDate
> or OrderNumber, which will always be increasing"
> My quesion is why not ?
>