MS SQL Server 7

[danedwards11]

I have a customer using MS SQL 7, running on a Windows NT cluster. My application is an IVR (automated telephone system) which processes calls for the customer. The scripts for processing phone calls resides on the SQL Server. Without the scripts, I cannot process calls.

On 3 occasions over the last 6 months, my application has timed out talking with the server. It starts with a single line, but eventually it will lock every line in the system (72 lines * 2 machines). It also starts on both machines at almost exactly the same time. Some lines wait as long as 5 minutes. Eventually, all the lines are released at almost exactly the same time.

I do not get the "Your process was chosen as the deadlock victim" message. I have seen this in other instances, but not this one.

Two tables are used to process the calls: Scripts & Nodes. Scripts represent an overall process, while Nodes represent individual actions within the script.

I get my scripts with "SELECT <field list> FROM Scripts WHERE ScriptID = 9999". ScriptID is an indexed field. I get my nodes with "SELECT <field list> FROM Nodes WHERE NodeID = 999". NodeID is an indexed field.

I use ODBC to access the server.

I am trying to find any information or tool that might help me resolve this problem. As you can imagine, when 144 phone lines stop talking and answering, the customer tends to get a bit testy.

Thank you for any assistance you might provide.

Dan

[MAK]

1. How often you reindex? Reindex every night.
DBCC SHOWCONTIG will show the fragmentation.

2. See these statements under execution plan. If it is not choosing the right indexes then you have to force it.

SELECT <field list> FROM Scripts (Index=Scripts_ScriptId_Idx) WHERE ScriptID = 9999
SELECT <field list> FROM Nodes (Index=Nodes_Nodeid_Idx) WHERE NodeID = 999

3. See whether you are running any batch job during that time (heavy updates)

[danedwards11]

Thanks for your quick reply. When I first read it, I thought "Surely SQL Server would use the NodeID index when all I'm using is the NodeID in the WHERE clause?!?!". Then I checked. I didn't have the Nodes table indexed. Doohh! There are about 175,000 rows in that table and NodeID is an IDENTITY, but somehow when I transferred the database over, I didn't get NodeID setup as an index.

Anyway, I'll add the index Monday. It still seems strange that I would have 300+ seconds of delay, though. The normal response time is sub-second, but maybe the table scans are forcing something else in the server which eventually causes the extreme delays.

Thanks for your help.