0

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.

3 Answers 3

1

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.

4
  • 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, 2011 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, 2011 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, 2011 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, 2011 at 8:22
0

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.
1
  • 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, 2011 at 15:35
0

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.

1
  • 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, 2011 at 17:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .