stored procedure not running to completion and not returning results set

Question

stored procedure not running to completion and not returning results set

aaron.reese

SSChampion

Points: 13498
More actions
May 27, 2014 at 11:27 am

#293819

Hi guys,
This one has me a bit stumped... I have NEVER seen this before...
I have stored procedure
ALTER PROCEDURE dbo.usp_Create_Fact_Job (@startDate date, @endDate date) AS
/*--Debug--*/
--DECLARE @startDate date
--DECLARE @endDate date
--SET @startDate = '01 APR 2014'
--SET @endDate = '02 APR 2014'
;
/*-- end of Debug*/
WITH CTE_one AS ( blah blah blah)
...
SELECT a whole bunch of fields from the joined tables and CTEs
When I run the code inside the stored procedure by Declaring and setting the start and enddates manually the code runs in 4 minutes (missing some indexes 😉 )
When I call the stored procedure with the ExEC
DECLARE@return_value int
EXEC@return_value = [ClaimCenter].[usp_Create_Fact_Job]
@startDate = '01 apr 2014',
@endDate = '01 apr 2014'
SELECT'Return Value' = @return_value
It never returns a results set but doesn't error out either. I have left it for 40 minutes and still no joy.
Any idea what is going on as I am stumped as to why it never returns a result
The sproc is reasonably complicated; 6 CTEs to find the most recent version of records and some
2 joins to parent tables (parent and grandparent), 3 joins to child tables (child, grandchild and great grandchild) and 3 joins to lookup views each of which self references a table to filter for last version of a record.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246880 More actions · Answer 1

You may be suffering from 'parameter sniffing' - a quick search on Google will give you details of what that is and ways to get round it...

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

I agree with Phil. Here is an excellent series of articles about the topic.

http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

aaron.reese SSChampion Points: 13498 More actions · Answer 3

@Phil, @sean

You were spot on guys. Parameter sniffing was the culprit. I have 'fixed' it by assigning the parameters to local variables. This will work for me as this is a once a day extract query and performance is not critical as it happens overnight when the server load is quite low and the extract criteria are consistent (range from 1 to 3 days) and the data distribution is pretty even.

I also found the following good posts on parameter sniffing.

http://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/[/url]

http://www.databasejournal.com/features/mssql/article.php/3841271/T-SQL-Best-Practices-150-Parameter-Sniffing.htm

http://blogs.msdn.com/b/turgays/archive/2013/09/10/parameter-sniffing-problem-and-workarounds.aspx

NOTE: This is MY solution for MY problem, Do your own research! If you have high frequency queries or wildly different data distributions you may want a different solution

Aaron