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 |
|
whosywhat
Starting Member
2 Posts |
 Posted - 2007-06-28 : 17:26:52
|
| Not sure if this is the right Forum for this question...I have a DTS job running against SQL 2005. When this job executes one particular step (running a stored procedure), it takes a long time to run. If I try that same stored procedure in Query Analyzer, it runs really fast. How to fix?(Migrating to SSIS has been considered, but we're not going there yet.)I have tried running the DTS job with different logins, on different machines, inside DTS Designer and from a command line. Every time it is slow is DTS but fast in QA. More Details:This DTS job hasn't always been slow. Several months ago it was fast and then slowed down. For days and days it was slow. Then, someone installed SP2 for SQL. After that, the DTS job became lightning fast again. Then, today, it's back to the slowness. Putting the SP through the tuning wizard only shows that we could achieve minor improvements in performance with a bunch of new indexes. Anyone have an idea why this SP would be slow in DTS, but not in QA? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-28 : 22:40:07
|
| Try trace both dts and qa session for the sp in profiler to see if have any difference. |
 |
|
|
whosywhat
Starting Member
2 Posts |
Posted - 2007-06-29 : 11:25:39
|
| Thanks for the reply.I did look at it in profiler. The only difference that I see is that the SP duration in DTS is about 100X longer than when it runs in QA. That said, I'm not sure I'm looking at the right events. Any suggestions as to what I should look for? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-30 : 20:51:21
|
| Any difference in execution plans? |
|
|
|
|
|
|
DTS Performance in SQL 2005