Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm running a stored procedure and it just doesn't finish. Here are a couple of things I have noticed:

  • Normally, the procedure takes 20mins but now it's running for over an hour by now.
  • Normally, the procedure uses several cores to process but now it's using just one.
  • Normally, the procedure shovels around 10+ MB/s ( sometimes 100+ ) but now it's not shoveling any data.

The stored procedure is suppose add some data to a big table ( that's done ), to calculate some mean values into a small table ( that's done ) and to calculate some standard deviations ( seems to stuck be here ).

What are the things I can do? Or what are the things I can investigate.

I'm the only user on the server ( it's a remote machine ). I'm running from inside the MS Management Studio. The version I'm running is SQL Server 2008 R2.

Thanks in advance, Christian


migration rejected from Sep 27 '13 at 17:13

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as off-topic by Max Vernon, Paul White, bluefeet, RolandoMySQLDBA, Kin Sep 27 '13 at 17:13

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Max Vernon, Paul White, bluefeet, RolandoMySQLDBA, Kin
If this question can be reworded to fit the rules in the help center, please edit the question.

Check out what the session is waiting on by querying the wait_type from sys.dm_os_waiting_tasks. – Thomas Stringer Jul 18 '12 at 16:40
"Normal" is 20 minutes? Doesn't the normal case bother you as well? I can't imagine any stored procedure should take 20 minutes unless there are intentional WAITFOR delays or it is waiting on some external process... – Aaron Bertrand Jul 18 '12 at 16:41
@AaronBertrand or your server is half way around the world. – njk Jul 18 '12 at 17:06
@njk and you are on dial-up? Otherwise network latency shouldn't be causing delays that extreme. – Aaron Bertrand Jul 18 '12 at 17:07
Check sys.dm_exec_requests. – Aaron Bertrand Jul 18 '12 at 17:16