SQL Deadlock Issues w/US 3.10.A

We have a configuration under development that utilizes SQL 2000, with approximately 200 concurrent users. When testing under reasonably low user counts (10-30 users), ODBC error messages referencing deadlocks occur rather frequently (every 10-15 minutes). These errors appear dispersed throughout various program areas; we have seen errors in the Sales Order posting, Req. Worksheet function Calculate Plan, etc. The configuration includes: Quad Xeon 700mhz Processors - 2MB L2 Cache (Same stepping) 300GB (RAID 1+0) Transaction Log Drive 700GB (RAID 1+0) Data Drive (sep. controller from log drive) 8GB PC100 RAM (Compaq Certified) Windows 2000 Advanced Server (w/ /3GB and /PAE enabled) SQL Server Enterprise Edition (4 processor edition) 1 Gbit optical link to 100 Mbit switches (all workstations are on 100 Mbit switch ports) Our concerns are that either the 3.10.A client executable and/or database objects have not been significantly tested under SQL and heavy concurrent user loads. One example of the problem can be seen when 2 users attempt to use the P&P/Requisition Worksheet function “Calculate Plan.” Approximately every 5th to 10th attempt results in a deadlock condition. Looking forward to any replies regarding this, SQL locking, configuration “tricks” learned by earlier adopters of 3.10, etc. David Hutchinson dhutch@symbiant-tech.com

Hi, Sometimes it’s better to turn of the SIFT-tables in your key to avoid the deadlocks. Since 3.10 you have the possibility to indicate which SIFT level SQL has to maintain. OF course, if you disable one or more SIFT levels than you avoid to have deadlock but your performance is going down. It’s always a choise between performance and having deadlocks/space in your database. Greeting, (I have started a thread "disable sift levels : Yes/No’