I was appointed in charge of a 10-year transactional system where most of the business logic is implemented at the database level (triggers, stored procedures, etc.). Win2000 Server, MSSQL 2000 Enterprise. No immediate plans to replace or upgrade the system are considered.
The main process is a program that performs transactions - in particular, it executes a stored procedure with various parameters; call him sp_ProcessTrans. The program executes the stored procedure at asynchronous intervals.
By itself, everything works fine, but on remotely located workstations there are 30 instances of this program, they all execute asynchronously sp_ProcessTrans, and then retrieve data from the SQL server. The execution is performed quite regularly - from 0 to 60 times per minute, depending on what the program instance responds to.
System performance decreased significantly with a 10-year increase in data: the cause is deadlocks, in particular the timeout for blocking, in the table Employee.
I discovered:
- In execution,
sp_ProcessTranshe selects from the table Employee7 times - The selection is made in a field that is NOT the primary key
- There is no index in this field. Thus, the table is scanned 7 times per transaction.
, . ( , NUM(7), ). .
, . 30 , "" , .
, .
MSSQL, - ( , ..) , ? , 30 .
, ? ( , - . 10- .)