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 |
|
memarcie
Yak Posting Veteran
91 Posts |
 Posted - 2007-03-06 : 14:19:19
|
| I have a job that runs billing monthly which accesses databases on SQL Server and MySQL. It was running approx 1.5 hours the past several months. Suddenly, this month, something is causing it to run extremely slow. It has been running for 24 hours and only about half done. There have been no application changes to cause slow downs. If I look at performance monitor the average disk que is around 56. Could something be wrong with the hardware and what would I look at to pinpoint that?Thanks,Marcie |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-03-06 : 14:51:21
|
| If this is the only process that has slowed down, it seems unlikely that hardware is the problem.Since your application is using multiple servers, you should start by trying to identify which part of the process and which server is slow.CODO ERGO SUM |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-06 : 15:02:23
|
| Indexes defragged/reindexed and statistics updated regularly??Kristen |
|
|
|
memarcie
Yak Posting Veteran
91 Posts |
Posted - 2007-03-06 : 15:10:04
|
| It makes sense that it most likely is not sql server but something else causing the slow down. Kristen - I haven't upated statistics lately, but if that were the problem I would expect it to get progressively slower versus this drastic change. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-03-06 : 15:21:02
|
quote:
Originally posted by memarcie...I would expect it to get progressively slower versus this drastic change...
Don't count on that. A typical result is that it choses a bad query plan that takes much longer to run. For example, a switch from a nested loop join to a merge join.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-06 : 17:20:32
|
| "I would expect it to get progressively slower versus this drastic change"I expect there are a number of reason why there might be a sudden change, one that occurs to me is that the original query plan which, lets assume, is a good-un!! is cached in RAM, the stats get worse and worse, then you Reboot and the cached query plan is lost, and the new plan is awful because the Stats are out of date.Anyways, easy to test, try updating the stats, clear the cache, and see if its any better!Kristen |
|
|
|
memarcie
Yak Posting Veteran
91 Posts |
Posted - 2007-03-07 : 14:58:57
|
| Well, we have rebuilt the views over the tables involved, defragged the data drive, and eliminated some old files that were using up a lot of space without any improvement. It seems that running queries with sql server alone works ok, and we may be having some type of network issue which is causing the big rise in disk que length. |
|
|
|
|
|
|
|
|
SQL Server has slowed down extremely