January 2, 2009 at 3:26 pm
Hi all
i am facing a strange problem,
I have a stored proc that takes forever to run but when i run the same proc as the query then it runs easily
Confused???
:w00t:
January 2, 2009 at 4:01 pm
search for "Parameter Sniffing"
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 2, 2009 at 4:35 pm
It sounds like you might have a cardinality error. If you are using an @temp table try changing it to a #temp table. If that does not help try adding the option recompile hint to each of your statements. If that works you can start peeling them off until you find the query you need to tune.
If it is not a heavily used server you could also try grabbing the plan from the cache, pasting it into notepad and saving it with a .sqlplan extension so you can open it in mgmt studio by double clicking on it. Once the plan is open in mgmt studio just put the mouse pointer over each operator until you see the actual rows and estimated rows are way off or just compare to the ad-hoc plan for differences.
Here is a query to get the plan:
SELECT q.text,
p.query_plan
FROMsys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) q
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) p
WHEREq.dbid = DB_ID(' ')
AND q.objectid = OBJECT_ID(' ')
January 5, 2009 at 6:48 am
I think Barry's right. It's probably parameter sniffing.
Compare both execution plans. See what's different between the two.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 5, 2009 at 8:02 am
Just to pile on, normally in these cases it is Parameter Sniffing.
David's suggestions are valid as well, particularly about comparing execution plans.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 5, 2009 at 8:04 am
Yeah Grant, it's easily one of the Top 5 questions on the boards here.
If I could find a good article on it here at SqlServerCentral.com then I could put it in my briefcase and point posters to it. Maybe you or Gail could do that...? 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 5, 2009 at 8:08 am
C'mon Barry, sounds like a topic you could handle as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 5, 2009 at 8:17 am
Actually, no. I really do not know much about it, particularly how to fix it. I just have seen this one posted so many times here that it's pretty easy to recognize by now.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 5, 2009 at 8:27 am
RBarryYoung (1/5/2009)
Actually, no. I really do not know much about it, particularly how to fix it. I just have seen this one posted so many times here that it's pretty easy to recognize by now.
The only fix I've ever seen posted on this forum is to copy the parameters into variables early on in the sproc...
CREATE PROCEDURE dbo.MyOdourlessProc
@pCountry VARCHAR(2),
@pCompany INT
AS
SET NOCOUNT ON
DECLARE @Country VARCHAR(2), @Company INT
SELECT @Country = @pCountry, @Company = @pCompany
.
.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 5, 2009 at 8:32 am
Chris Morris (1/5/2009)
RBarryYoung (1/5/2009)
Actually, no. I really do not know much about it, particularly how to fix it. I just have seen this one posted so many times here that it's pretty easy to recognize by now.The only fix I've ever seen posted on this forum is to copy the parameters into variables early on in the sproc...
CREATE PROCEDURE dbo.MyOdourlessProc
@pCountry VARCHAR(2),
@pCompany INT
AS
SET NOCOUNT ON
DECLARE @Country VARCHAR(2), @Company INT
SELECT @Country = @pCountry, @Company = @pCompany
.
.
I believe WITH RECOMPILE also works.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 5, 2009 at 8:37 am
Jack Corbett (1/5/2009)
I believe WITH RECOMPILE also works.
That makes sense, Jack...https://qa.sqlservercentral.com/blogs/ken_kaufman/archive/2007/11.aspx
Also here[/url], in more detail.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 5, 2009 at 8:41 am
Or Jack.
Or you for that matter.
I might though.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 5, 2009 at 8:43 am
Actually, I heard from someone at PASS that WITH RECOMPILE does not work reliably for this problem in SQL2005, but "is fixed" in SQL 2008.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 5, 2009 at 8:44 am
You can also use the OPTIMIZE FOR query hint as a way to force the plan down an optimal path.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 5, 2009 at 8:46 am
RBarryYoung (1/5/2009)
Actually, I heard from someone at PASS that WITH RECOMPILE does not work reliably for this problem in SQL2005, but "is fixed" in SQL 2008.
WITH RECOMPILE was never a great option since it adds unnecessary overhead.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply