Stored procedure hangs seemingly without explanation

by

I think that this is related to parameter sniffing and the need to parameterize your input params to local params within the SP. Adding with recompile causes the execution plan to be recreated and eliminates much of the benefits of having a SP. We were using With Recompile on many reports in an attempt to eliminate this hanging issue and it occassionally resulted in hanging SP’s that may have been related to other locks and/or transactions accessing the same tables simultaneously. See this link for more details
Parameter Sniffing (or Spoofing) in SQL Server and change your SP’s to the following to fix this:

CREATE PROCEDURE [dbo].[SPNAME] @p1 int, @p2 int
AS

DECLARE @localp1 int, @localp2 int

SET @localp1=@p1
SET @localp2=@p2

More Related Contents:

  1. How do I find a stored procedure containing ?
  2. Using Aliases in Where Clause or an Alternative Option?
  3. Procedure or function !!! has too many arguments specified
  4. Stored procedure that exports data into csv files only exports to one file
  5. Using a cursor with dynamic SQL in a stored procedure
  6. Execute a SQL Stored Procedure and process the results [closed]
  7. Is it possible to write single query on two table which are not connected to each other?
  8. Comma separated results in SQL
  9. T-SQL stored procedure that accepts multiple Id values
  10. Understanding PIVOT function in T-SQL
  11. Using RegEx in SQL Server
  12. Is the NOLOCK (Sql Server hint) bad practice?
  13. Parse comma-separated string to make IN List of strings in the Where clause
  14. What is a “batch”, and why is GO used?
  15. SQL Server 2008 Empty String vs. Space
  16. SQL MERGE statement to update data
  17. T-SQL loop over query results
  18. Compare DATETIME and DATE ignoring time portion
  19. SQL Server: combining multiple rows into one row
  20. How to Round a Time in T-SQL
  21. Split function in SQL Server 2008
  22. Return a value if no rows are found in Microsoft tSQL
  23. Optional parameters in SQL Server stored procedure
  24. Check if a varchar is a number (T-SQL)
  25. How can I group by date time column without taking time into consideration
  26. Instead of trigger in SQL Server loses SCOPE_IDENTITY?
  27. How to wait for 2 seconds?
  28. Check if a parameter is null or empty in a stored procedure
  29. Execute sp_executeSql for select…into #table but Can’t Select out Temp Table Data
  30. Recursive Child/Parent queries in T/SQL

Leave a Comment