Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Chinwa
Starting Member
20 Posts |
 Posted - 2007-11-01 : 10:14:53
|
| Hello,I am hoping some of you can direct me to areas I should look into.First off let me say that everything was fine and worked great up until 3 weeks ago. Then all of a sudden we began to experience timeouts on a particular query.The vb.net 2002 code has not changed and is pretty basic stuff to connect to the server and execute the query. The only change I have made to it since then is to add a commandtimeout parameter so it would actually run.There does not seem to be a specific pattern either to when it runs slow and when it does not.I can be the only one in the server in the morning (the only one in the office for that matter) and one day the application will execute the query and return data in under 9 seconds and the next day it will be a minute and a half.I can test it during peak network times and the same thing...one day fine the next day performance is slow.Also, if I run the query straight from the management studio it will run in 2-9 seconds and from .net it takes 20-90 seconds.Are there server admin jobs I could run to see if tables are deadlocked, if the network is to busy..anything? |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-11-01 : 12:47:19
|
| Is your server being rebooted each day/mid-day?Could you be suffering from "paramenter sniffing"?...search here for symptoms/solutions.You can run Profiler to log the SQL activity....but it has to be running before the problem occurs. |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-01 : 22:56:17
|
| You can check blocking with 'sp_who2 active'. |
|
|
|
Chinwa
Starting Member
20 Posts |
Posted - 2007-11-02 : 08:26:04
|
| Thanks all, I will try the sp_who2 but I did run an update statistics on the tables involved and I put the query through the tuning advisor.It recommended some statistics to be build on a couple of columns.After following it's advice the query seems to be back to normal.I added a job to our nightly maintenance that will update statistics on the database tables nightly. After doing some reading on UPDATE STATISTICS I had no idea how important that process is to keeping your database performance high.Thanks again! |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-02 : 22:58:22
|
| Sql server optimizer generates query execution plan based on table statistics. |
|
|
|
|
|
|
2 sec in management studios and 90 sec from vb.net