"It only seems to issue Schema Stability locks when running (because it
is built on a stack of views)."
Did you profile the views that the query is based on?
-Chris
-----Original Message-----
From: Johnson Mark E
[mailto:mssqldba-ezmlmshield-x52567857.[Email address protected]
Sent: Tuesday, August 24, 2004 6:34 PM
To: LazyDBA Discussion
Subject: RE: Help with locks, pauses
If your not seeing any blocking, why do you think locking is the
problem?
Mark
________________________________
From: Gwr2435 [mailto:mssqldba-ezmlmshield-x82727449.[Email address
protected]
Sent: Tue 8/24/2004 5:03 PM
To: LazyDBA Discussion
Subject: Help with locks, pauses
We have a fairly large SqlServer2000 DB (~30GB), some tables have
10million to 100million rows. Raid5, 4way, 4GB RAM, tempdb and
transaction logs on separate disks. This is an extremely active db --
mostly batch processing.
We have some queries that run several times per day. The same query,
which usually returns 10-20 rows, can take anywhere from 1 minute to 4+
hours. In Enterprise Manager, it doesn't say anything is blocking it.
It only seems to issue Schema Stability locks when running (because it
is built on a stack of views).
We've tried with(readpast), with(NOLOCK), SET TRANSACTION ISOLATION
LEVEL READ UNCOMMITED. It just seems that *something* else is happening
that is prevents the query from finishing sometimes. Could it be
something like MSSQL Server is updating it's own statistics or something
and that is interfering with the SELECT statment?
I'm from an Oracle background and I've never seen other activity block a
SELECT statement like this. I'm not trashing SQLServer, I just want to
know what the key is to preventing this behavior. I must be missing
something...
Thanks,
Glen Roberts
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
MS Sql Server LazyDBA home page