SQL 6.5 Response

[Guest_imported]

Hi,

I'm novice to SQL and do hope any helpful tips from anyone of you.

We've the application running on the server with SQL 6.5. The problem is whenever the records in INTable > 100k, the processing is getting slower. The normal processing should take only 2-3 secs and with records >100k, it goes beyond 20 secs. The way the application was designed is we have 8 processors(pgms to inquiry) accessing to the database at the same time or probably 1-2 secs different. We look into sp_who2 and realized there were so many deadlocks for each processors. Our current solution is to delete as many records as we can. I donot know whether this is caused by the way the application was designed or we can tuned it on SQL to make it perform better.

Any valuable tips is highly appreciated.

TIA

JohariK

 

[MikeTom]

Tips. You need to find where the slowness is coming from. possibly configuration of server, defragged tables or you need indexes on yor tables fot the quey.

To start of if you sre deleting and inserting alot to your tables they are going to become fragmented so you need to defrag. the way to do this is re-index your clustered index for the table with a fill factor ( i would suggetst about 80%) if you do not have clusteered indexes you want to create one prfebally on a unique primary key.

Secondly you need to run Performance monitor and monitor the Procedure cache (should be above 90%) run dbcc memusage wich will tell you how much memory is allocated to sql server and how it is divided up. you may need to read on up on mem configuration and sql performance tuning i would suggest start with BOL.

After doing all of the above if you are satisfied tou server is tuned properley then i would look at you specific query to see why it is taking so long and maybe trying to rewrite it or create further indexecs for it.