SP RUNS ONCE THEN GETS STUCK IN A LOOP

Question

SP RUNS ONCE THEN GETS STUCK IN A LOOP

Viewing 6 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

did you update statistics?(see BOL "sp_autostats")

As I stated before, long running queries are often a result of bad statistics.

two servers, even with the same data, can have different statistics (and query plans) due to inserts/updates/queries against the data. since this query would update the entire table the first time it is run, the statistics could be out of whack because of this single update statement. and thus doink any further updates.

is the database option "Auto Create Statistics" turned off? if it is, unless you have a scheduled job to create statistics manually, you will encounter problems like this.

SEE BOL "sp_dboption"

exec sp_dboption 'YOURDBNAME','auto create statistics'

exec sp_dboption 'YOURDBNAME','auto update statistics'

will return your current settings

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

wjunk SSC Veteran Points: 214 More actions · Answer 2

Yeah Auto Create Statistics is turned on. Cheers. Would the statistics explain why it works the first time and not the second time?

wjunk SSC Veteran Points: 214 More actions · Answer 3

I've also tried adding

option (maxdop 1)

to the stored procedure which is ran to avoid the parallelism but still the second time it runs it stalls in Exchange and Sleeping.

Lowell SSC Guru Points: 323546 More actions · Answer 4

can you change your procedure to have the WITH RECOMPILE option in it? that would force it to build a new execution plan, and might fix the issue.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 5

No explanation except for maybe parallelism, different stats, different number of processors, different section of harddisk, different memory, etc, etc.

It's just not a good idea to update a view of a view (heck, didn't even know that it would work except in very rare cases). It's like doing joined updates without have the target table in the FROM clause... will usually work, but when it doesn't, it'll take hours to resolve.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

wjunk SSC Veteran Points: 214 More actions · Answer 6

Yeah that did it. Excellent! Thanks for your help.

Regards