I have a SQL db (2000 R4) that periodically under peak usage locks the tables so they can't be written to. It lasts for about 10 to 150 seconds. During this time, the cpu is at low utilization, although it looks like sometimes the problem is preceeded by a cpu burst of upwards of 80% for under a second. Can you please also address my 'ideas' below and tell me how I can investigate them (i.e. with certain tools or tests.)
Background: I have an internal application using ODBC that accesses the DB with many concurrent threads (upwards of 50, but the problem can happen with much fewer). Concurrently I have a web site that is accessed by users (upwards of 200+) that accesses the same DB (via ODBC). The DB runs on its own server with dual ZEON processors under Windows Server 2003 R2.
IDEAS::
1) Could this be a 'concurrent access/users' limit issue? (During heavy web usage times, the number of concurrent users, those that have web pages with the DB open, increases. Unfortunately I don't have good stats on those numbers. Also I noticed that the web asp code does NOT do cconn.close commands, although the internal application does. The thinking is that as the number of users rises and declines, it crosses some threashold which causes the problem and recovers as the number of users goes back down.)
2) Could a particular operation or combination of operations, all during peak periods, be locking out the db temporarliy? (I have not been able to recreate the problem myself as a web user having tried most
every operation on the site.)
3) Are there any related SQL parameters/settings that could be addressed? (Maybe something like concurrent access things, pending queue lengths, timeouts, etc?)
4) Are there related limitations/parameters for ODBC or IIS that I should be concerned with?
5) How would I find a SQL EXPERT (... a TRUE expert) in the Atlanta area? I need help with uptime and backup/recovery strategies, problem trapping/analysis/resoluti
on, and performance tuning.
Thank you for your help. - DC
Start Free Trial
