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


MS Sql Server LazyDBA home page