September 2, 2003 at 5:09 pm
Hello,
I just finished writing a SQL Server 2000 application using VB.NET for a client of mine. The app went into production back in May 2003. For a while the perfomance was great all queries ran under 3 seconds but before I left the queries were taking longer than 30 seconds to run. I had the servers guy look at the server and they could not see anything wrong with it as the server utilization was at like 7 percent most of the time, there were no locks or any blocking as far I could see. The slowdowns were occurring when the application was inserting or updating records. I had all the insert and updates in stored procedures using transactions as sometimes it was inserting records into more than one table. There are a number of other databases on the system. I need a method of finding out what is going on so that I can get the server people to fix it. If anyone can help please let me know. Cheers
September 5, 2003 at 8:00 am
This was removed by the editor as SPAM
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply