I've got users using an application called contract works that is written on Seibel. I have had reports of it timing out and slowing down.
The application is on the same server as the databases (which is SQL 2000).
There is 4 gb of RAM of which 2.5 GB is assigned to the Datababse however SQL is only ever using around 1.8GB.
The application is written using dynamic cursors, there are next to no Stored procedures called. Beyond that i've seen as many as 20 databases called in queries with left outer joins and the execution plans are showing 100% utilisation at page scans, however no recomendations were made to add any indexes. It looks like the required indexes already exist.
I've checked to see if there are any locks, which there were none. Checked IO/memory/CPU and could see spikes and the heavy usage queries being returned. I guess returning data going as far back as 2003 using cursors is extremely inefficient and I just cant see how I could improve performance beyond recommending better hardware and more memory. I believe the drives are also old and probably around 5400 rpm adding to the problem.
I was hoping for further recommendations or advise.
please see the presentation:
beyond that current things i've tried.
EXEC sp_configure 'max server memory', 2048
maintenence plan to rebuild stats daily, and reorganise indexes weekly(using sql 2000 maintenence plans). Although I'm not sure if I should rebuild the indexes once a week.
Beyond that I was running checkpoint and DBCC DropCleanBuffers daily too but noticed at the start of each day performance is hit as plans are reritten.
Wait times are high often when theres a large query running.
any recommendations are much appreciated.