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: 246977 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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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