September 5, 2003 at 8:46 am
There are so many things that affect performance...here are just a few suggestions.
-Check indexes. Since the routines were quick at the beginning but slow now - that hints at a data volume related problem. Somewhere a table scan may be the culprit and a good index could return the original speed.
-Check Indexes - Excessive in size or number will always incur extra time - increasingly expensive as the mass of data increases.
-Check for foreign key relationships. Some are unnecessary and create overhead.
-Check for triggers. These may be more expensive than anticipated
(Actually - if you have the luxury of submitting via stored procedure, performance is often better by executing the code as part of the procedure rather than as database rules)
-Run a performance monitor to look for memory paging. Sometimes there are just too many activities on the server (web and sql on the same machine, etc.)
Just a beginning...
Guarddata-
September 5, 2003 at 9:02 am
To add to guarddata's post...
Slower now when doing inserts/deletes than before? Have you added an exorbitant amount of indexes?
Do you update your statistics?
Do you use clustered indexes? This is a big one as intelligent heaps reclaim empty space and will contribute exactly to your problem.
Do you use cursors? Hopefully not where you can help it?
Has your server been running without downtime for a long time? Maybe your stored procs have poor execution plans.
HTH
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
Viewing 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply