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 |
|
gongxia649
So Suave
344 Posts |
 Posted - 2008-01-02 : 11:55:46
|
| It appears I have an intermitent issue on the sql server. I have a query that insert data from AS400 to SS.Joining 5 tables, these are processing tables and they are open for every customer rep. Today a dev tried running this query but it never finished then I ran it on my side and it took 1 min, told him it's Ok and he could run it now. He ran it and it worked. Then later he runs it again and it was taking 8 min and he cancelled it. Anyone know what is going on and how to troubleshoot it? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-01-02 : 12:14:05
|
| It can be due to various factors like some other processes blocking this by using same resources,the amount of data thats involved from 5 tables,.... I think you can run query with show execution plan option enabled and see what step in it is costly and why. |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2008-01-02 : 12:19:22
|
| You need to use SQL Profiler and see if there are any locks being escalated/in place? This however would need to be in place before/during the problem execution....which as you have seen is intermittent. (it does have an operational/performance cost to have in place continuously). "Dev" could be getting a bad execution plan. Also..."you" may have benefited from data in CACHE, and maybe your results are unrepresentative of normal "live" operations.Maybe investigate "parameter sniffing" which causes unusual execution plans....mentioned here before. |
|
|
|
|
|
|
Intermitent issue.