I need specialist in SQL server, who will find the exact reason of server stacking and will get tips of possible way of problem resolution.
We have an application in MS Access and a number of small VB6 applications. All of them connected to MS SQL Server. An Ms Access program uses DAO 3.5 library. A VB6 application uses ADO 2.7.
Until December of 2011 that was SQL server 2000. After December 2011 we migrate to SQL Server 2008.
From the 2005 I started work with this organization the server begin stack once or more every day. The reason was locking of some table (t_orders) that reaches about 300000 records (dead locks or something like this). I tried find the exact operation that cause the problem but didn’t succeed. The solution was delete each year the records from this table. So the table will have no more than 200000 – 250000 records.
The addition of VB6 modules increased the problem. So the number of records that we could hold in the table becomes less than 200000. And, finally, after migration to SQL 2008 the situation become even worst.
To treat the problem I need to know – what is the exact reason of stacking. Possible the problem can be treated by appropriate configuration of the server. If no, I need know the expressions, that cause the problem to be possible identify the sql expressions in the sources of application and change this expressions appropriately.
For now the SQL server locks once-twice a day.
I collected the logs of sql server monitoring and results of sp_lock, sp_who2, sp_who3, cspWhoCPU_expensive procedures.