Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a SQL server 2000 instance which runs several concurrent select statements on a group of 4 or 5 tables. Often the performance of the server during these queries becomes extremely diminished. The querys can take up to 10x as long as other runs of the same query, and it gets to the point where simple operations like getting the table list in object explorer or running sp_who can take several minutes.

I've done my best to identify the cause of these issues, and the only performance metric which I've found to be off base is Average Latch Wait time. I've read that over 1 second wait time is bad, and mine ranges anywhere from 20 to 75 seconds under heavy use.

So my question is, what could be the issue? Shouldn't SQL be able to handle multiple selects on a single table without losing so much performance? Can anyone suggest somewhere to go from here to investigate this problem?

Thanks for the help.

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

Depends on locking. What most people totally forget is caring about locks. Standard connections set full read locks (repeatable reads) which may use up ressources.

If you ONLY read, ONLY read once, use a connection marked in a way and a sql statement markeed in a way to NOT leave ANY locks.

Would be applicable for most asp.net pages - you read, present page... no need to keep locks around for this operation, only when you start writing.

share|improve this answer
    
I should clarify that these queries are not from an application. They are ad-hoc run via Management Studio. As for Locking, Perfmon is showing 0 average lock wait time, about 5k lock requests/sec, 0 lock waits/sec and 0 deadlocks/sec. Do you still think the issue could be lock related? –  Colin Feb 9 '11 at 14:48
    
Yes. It takes time to initialize 5000 locks per second, you know. Ad hoc management studio is not really... good in that. –  TomTom Feb 9 '11 at 20:14
    
Thanks. Are there better options for running ad hoc queries than management studio? I will try implementing a nolock table hint with my queries since the tables they read from can't be altered. Hopefully that will resolve some of my performance issues. –  Colin Feb 9 '11 at 20:56
    
None that I Know of. Sadly. YOu can put in hints (nolock). Mostly adhoc is not seen as something putting heavy load on production - sometimes this is a bad assumption. I would, btwl, schedule an update. Your database is ANCIENT. –  TomTom Feb 10 '11 at 8:22
add comment

If the application is truly only doing reads against that table, blocking should not be a problem. I would:

  1. Ensure that the table has appropriate indexes for the queries that are running. One of those indexes should be a clustered index.
  2. Ensure that the indexes have been rebuilt or the table statistics have been updated in the recent past.
  3. Look at disk performance, which is a common problem. If the seconds/transfer numbers are more than a few tens of milliseconds, try find a way to minimize the amount of I/O that is going on (which usually means adding indexes, changing the table schema or changing the queries), or improve the performance of the disk subsystem. That might mean moving files onto different disks, changing RAID levels, fiddling with the RAID controller policy, etc.
  4. For SQL2000, I would ensure that the processors have Hyper Threading disabled.
share|improve this answer
    
How does Hyper Threading affect SQL 2000? I'm pretty sure it's on, I see 8 cores on what Intel says is a 4 core CPU (though they also say it does not support Hyper Threading). –  Colin Feb 9 '11 at 15:35
add comment

SQL Server 2000 uses a lock mechanism that upgrades locks to encompass more and more of the schema - ie you may start locking a page of a table, but as you lock more rows, that lock will quickly get promoted to a full table lock. For certain types of query, this can absolutely kill performance.

So much so that MS changed the locking mechanism in later versions to be more like Oracles row level locking.

share|improve this answer
    
See my comment on the other response about locking. I don't believe this to be the issue due to the Perfmon counters I describe. –  Colin Feb 9 '11 at 17:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.