| Author |
Topic |
|
blackjackIT
Starting Member
25 Posts |
 Posted - 2007-10-12 : 15:28:20
|
Not sure if this should be in here or the dev area. I'm both and often can't tell the difference  I have a monthly build that runs a couple hundred stored procedures in sequence. This process takes approximately 15 hours to run. I have almost everything humming along in this big POS (can you tell I inherited it?) except for one very odd hangup...There is a single proc that runs within the last 30 minutes of the build that seems to hang up for absolutley no reason. Here is what I've found so far.1. The build hangs up on this proc every time it's ran.2. There are no blocks and no wait anywhere3. If I run the proc outside of the build it finishes within 3 minutes4. If I kill the build and start it again from that step, the proc runs fine with 10 minutes or so.5. A trace shows that it's hanging on an insert from a subquery into a table variable. This insert runs in less than a minute if ran manually.6. I thought the tempdb might be full but spaceused during hang only shows about 85% free.7. Same behavior in development and production8. Both servers are SQL2000 SP4 with 2 DualCore 3.0 Xeons and 2GB Ram9. IO seems to minimize and CPU load jumps to an oddly consistent state at around 25% during hangupI've been digging for answers but with the SPID running with no locks and no wait, it doesn't make any sense to me what is actually going on. |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
blackjackIT
Starting Member
25 Posts |
Posted - 2007-10-12 : 16:11:07
|
| Simple recovery mode with no deletes being performed in the proc thus nothing being logged. It's been hung for about 8 1/2 hours now with still no sign of locks or waits. It's pretty much busy doing nothing. |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-13 : 00:48:38
|
| You may like to trace it in profiler to see what's going on. |
|
|
|
blackjackIT
Starting Member
25 Posts |
Posted - 2007-10-13 : 10:31:04
|
quote:
5. A trace shows that it's hanging on an insert from a subquery into a table variable. This insert runs in less than a minute if ran manually.
|
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-13 : 21:53:18
|
| Then you need to find out why hanging on that. |
|
|
|
blackjackIT
Starting Member
25 Posts |
Posted - 2007-10-16 : 08:12:04
|
| no kidding...I'm at a loss. It took a little over 11 hours but the proc completed. As I said before, if restarted or ran manually, this proc completes in under 10 minutes. |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-10-16 : 09:20:13
|
| Just raw list of ideas, where to dig:Are there any parameters to that proc? Are they the same or different when you run it manually? Are you using linked servers? Are there exec(strings) or WITH RECOMPILE inside? May be connection settings (ANSI NULLS, etc) are different in the connection from an application and Query Analyzer? Or may be SET MAXDOP is different? Is it possible taht query returns to much data and application can not consume it and slows down? |
|
|
|
blackjackIT
Starting Member
25 Posts |
Posted - 2007-10-16 : 09:37:33
|
| Thanks for the feedback, here's what I've gotNo ParamsNo linked serversNo internal execs or recompilesDon't see any differences in connectionsI thought paralellism might have had something to do with it but further testing showed otherwiseHang occurs within proc before results are returned to app |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-10-16 : 09:46:42
|
| Can you split the "monthly build" into 2 pieces...."all but the problem" and the "problem code". and use some outside agency, SQL Agent or whatever, to schedule them to run consecutively?Maybe the problem is just being all-in-one. |
|
|
|
blackjackIT
Starting Member
25 Posts |
Posted - 2007-10-16 : 09:57:58
|
| Unfortunately, no. I have no control over the application development. I can put in a request for such a change but it will most definitely be deemed low priority. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-10-16 : 10:10:42
|
| Maybe a higher priority will be placed if/when the job never finishes....or when the MD/FD wants the output faster! Amazing what can happen when impatient powerful people get involved! |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
blackjackIT
Starting Member
25 Posts |
Posted - 2007-10-16 : 10:38:37
|
I wish I could but alas.... it is against my contract to share code with anyone outside the company. Damn trade secrets!  |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
blackjackIT
Starting Member
25 Posts |
Posted - 2007-10-16 : 10:48:41
|
| LMAO! I completely and utterly agree. This is just the tip of the iceburg on this one. The system needs a complete overhaul but I guess people like the look of duct tape better. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2007-10-16 : 11:31:42
|
| corporate espionageJust think, post all of the sprocs and you could grind your competitors to a haltIn any caseBefore you start the processs, start profile on a client and have it write the output to a data table, preferably on a different serverthen you can run queries against the output and can see where the bottleneck isBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
blackjackIT
Starting Member
25 Posts |
Posted - 2007-10-16 : 11:36:25
|
| Already did... see previous posts |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-10-16 : 13:11:11
|
| Ok, lets continueI understand you can't share the code.But how many statements do you have inside?Trace it with events SP:Stmt:Started/Completed.What statement took 14 hours to execute? Dont share the code, but what is it? INSERT/UPDATE/DELETE???Find the connection, related to this process and track it usingselect * from master.dbo.sysprocesses where spid=<spid>Is it always the same or CPU or IO counters increase?Is blocked equals to 0? |
|
|
|
blackjackIT
Starting Member
25 Posts |
Posted - 2007-10-16 : 13:31:48
|
| The proc runs about 50 statements, mostly manipulations to table variables prior to final output. The statement that gets hung up is an insert into a table variable from a select with a few joins including other table variables. When I run either the proc itself or up to the problem code, it executes no problem within a few minutes.I monitored the hang when I ran the build the other day and both CPU and IO were not increasing and blocked was at 0. |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-10-16 : 13:43:10
|
| Wow. Table variables and procedure running normally for 3 minutes... Sounds like possible million of rows in table variables... Table variables do not like >10000rows (it is just my impression based on experience)You know what? Could you replace all table variables @t with temp tables #t?Yes, table variables are more efficient (no log, statically known exec plan) but it is important for stored procs with Duration<100ms called may times. In your case (3min normal time) plan recompilation is almost irrelevant. |
|
|
|
Next Page
|
Strange Proc Execution Hang