tags:

views:

47

answers:

2
+2  Q: 

Stored procedure hangs seemingly without explanation

Hi, we have a stored procedure that ran fine until 10 minutes ago and then it just hangs after you call it.

Observations:

  • Copying the code into a query window yields the query result in 1 second
  • SP takes > 2.5 minutes until I cancel it
  • Activity Monitor shows it's not being blocked by anything, it's just doing a SELECT.
  • Running sp_recompile on the SP doesn't help
  • Dropping and recreating the SP doesn't help
  • Setting LOCK_TIMEOUT to 1 second does not help

What else can be going on?

+1  A: 

Run Adam Machanic's excellent sp_WhoIsActive stored proc while your query is running. It'll give you the wait information - meaning, what the stored proc is waiting on - plus things like the execution plan:

http://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/

Brent Ozar  2010-10-18 01:38:35

related questions

*= in Sybase SQL
sql missing rows when grouped by DAY, MONTH, YEAR
Adapt Replace all strings in all tables to work with text
Can you call a webservice from TSQL code?
How to tell the data types after executing a stored procedure?
C# - SQLClient - Simplest INSERT
SQL Server: Get data for only the past year
Get last item in a table - SQL
Is there a way to make a TSQL variable constant?
Create a database from another database?
SQL Server PIVOT examples?
What are some references, lessons and or best practices for SQL optimization training.
What's a good way to check if two datetimes are on the same calendar day in TSQL?
Trigger without a transaction?
How do I do an Upsert Into Table?
What is the easiest way using T-SQL / MS-SQL to append a string to existing table cells?
How do I avoid using cursors in Sybase (T-SQL)?
Best .NET Solution for Frequently Changed Database
T-Sql date format for seconds since last epoch / formatting for sqlite input
HOWTO - Compare a date string to datetime in SQL Server?
Differences Between MySql and MS SQL
Best method for varchar date validation in Sybase (T-SQL)?
interrogating table lock schemes in T-SQL
Testing for inequality in T-SQL
T-Sql Remove Decimal Point From Money Data Type