Tell me more ×
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. It's 100% free, no registration required.
up vote 1 down vote favorite

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

share|improve this question
1  
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
2  
Check sys.dm_exec_requests. – Aaron Bertrand Jul 18 '12 at 17:16
show 11 more comments

migrated from stackoverflow.com Jul 18 '12 at 17:54

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.