process status - runnable

[vlitim]

I have a stored procedure that has been working ok and now every now and again it seems to hang taking up all of the server CPU. It seems to be random when it happens and for no reason that I can see.

When I look into the process it says that its is "runnable" and it stays like that until I kill it where the CPU returns to normal!

Has anyone any ideas on this. I can't understand why I can run the same procedure for a week without any problems and then suddenly it goes mental and then it will be ok again!!??

Please help this is driving me mad!

Cheers

Tim

 

[SQLDenis]

blocking perhaps?......

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vlitim]

possibly! How do I find out and how do I prevent it if this is the case?

 

[druer]

1. You can find out what processes are being blocked, and who is blocking them by running the following command: select * from sysprocesses
where blocked > 0
order by blocked

2. Is it possible that your stored procedure executes cleanly shortly after reindexing has occured and you are having the problems after the indexes are fragmented before being reindexed?

3. Have you tried running the stored procedure in the query analyzer with a show plan on to see what the trouble spots in it might be?

Just some thoughts,