Slow Server performance
-
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
-
You could start by setting up a few traces. Take a look at "Monitoring with SQL Profiler" in Books Online.
Also, if you setup the the trace to run on the server without using SQL Profiler you'll reduce some of the overhead created by running the trace. Have a look at sp_trace_create in Books Online
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
I had the server guys run a trace for me but they don't which counters they should be monitoring ? Which counters should I monitor ?
-
Use one of the built-in templates traces with Profiler. If your application is built using mostly stored procedures, use the "SQLProfilerTSQL_SPs" template. Click on the Data Columns and add "Duration" as an output. Click on Filters, drill to "Duration" --> "Greater than or equal to" and then type 3000 (for 3 seconds.)
Name your trace, pick a table to log the trace to, and click "Run"...
--
When you've collected some data, let us know, and we can supply a couple scripts that will enable you to do some analysis...
--
HTH,
Jay
Edited by - jpipes on 09/03/2003 12:27:55 PM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply