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 |
|
SQLCode
Posting Yak Master
143 Posts |
 Posted - 2004-10-21 : 14:51:42
|
| Hi,I have a DTS package in which there is a stored procedure which processes millions of records.When I execute it on the server itself, it takes lesser time than when executed on my machine. My understanding is that it runs on the server no matter where it is kicked off from.My confusion is, all of a sudden, the procedure is taking awfully more than the time from its normal.Latest was, server crash due to this. The reason was Temp db which was residing on a different drive, was left with very little space.So we moved it arount where there was enough space on disk.Still ran for ever > 30 hrs (normal 2-5 hrs)We had to kill it.We did dbcc shrinkdb and few things before we had started again. didn't seems to have helped it.I also checked index scan density and was not very bad.So What could be possible reasons for this?We are also considering disk defragmentation, but any more ideas are greatly appreciated. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-21 : 16:32:17
|
| Yes it runs from the server, but you might be sending data over to the client which would impact the performance. So does the stored procedure display any data when run?Tara |
 |
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2004-10-25 : 10:22:06
|
| Yes the produre returns data and is doing what it is supposed to do.Now, even when from the server it is taking 10 times more than usual.What could be causing this ?Thanks for your help. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-25 : 12:52:44
|
| Hardware bottlecnecks, bad database design, fragmented indexes, poor query design, old statistics, lack of statistics, etc...Tara |
|
|
|
|
|
|
|
|
SP slow all of a sudden