Proc does not execute but query does

Question

Proc does not execute but query does

Viewing 11 posts - 16 through 25 (of 25 total)

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

Jack Corbett SSC Guru Points: 184388 More actions · Answer 1

Barry,

Of course it wouldn't be reliable, I mentioned it.:D Actually that is interesting, I may have to find a way to test that out. I know I had read somewhere that WITH RECOMPILE was an option.

Grant,

Yeah, I could not remember the OPTIMIZE FOR hint. Of course if it's optimized for the wrong plan then you can get the same issue.

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

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

Hey Jack,

Yeah, OPTIMIZE FOR is actually more than a little big dangerous. What happens when the underlying statistics change? You have to constantly review the work you did with it.

I wasn't aware that WITH RECOMPILE wouldn't force a recompile in 2005. That's a bit of a shock. Or do they mean that recompiling is unlikely to generate better plans? That's possible.

Another mechanism, that's similar to OPTIMIZE FOR was this thing I saw from Ken Henderson. He created two parameters for each parameter, one with a default value and one that you were passing parameters into the proc.

He wrote the query using the parameters that had the default value, but first substituted the value before executing the query. It fools the optimizer into thinking it knows the values being supplied.

CREATE Proc dbo.A

MyParam nvarchar(50)

MyParamInternal nvarchar(50) = 'Dubuque'

AS

SET @MyParamInternal = @MyParam

SELECT *

FROM TableA

WHERE ColumnA = @MyParamInternal

It's a bit wordy, and I would not use it in all instances. But if you're experiencing parameter sniffing in a particular procedure, it's another option. It suffers the same drawbacks as OPTIMIZE FOR 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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

I was under the impression that WITH RECOMPILE didn't work really well at the PROC level in 2005. Once they switched to statement level recompiles, the proc-level one didn't seem to do much for me, but the statement level did seem to help (in the few cases I used it).

Barry - did you get any info on why they didn't think it was a good move?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

RBarryYoung (1/5/2009)
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...? 🙂

Well, they're not here but...

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

http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing-pt-2/

http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing-pt-3/

I could flesh those out into an article if you think it would be useful. Drop me a mail/pm with anything you think should be added, explained, corrected, etc.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

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.

Wasn't that for the 'catch-all' type queries? ((Col1 = @var1 or @var1 IS NULL) AND (Col2 = @var2 OR @Var2 IS NULL)...)

I know Itzik mentioned that OPTION RECOMPILE 'fixes' those in SQL 2008 but didn't in 2005.

Or are you thinking of something else?

Grant Fritchey (1/5/2009)I wasn't aware that WITH RECOMPILE wouldn't force a recompile in 2005.

As far as I'm aware (and I will check), WITH RECOMPILE on a proc means that the plan will never be cached and hence has to be compiled fresh on each execution.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jack Corbett SSC Guru Points: 184388 More actions · Answer 6

GilaMonster (1/5/2009)
RBarryYoung (1/5/2009)
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...? 🙂
Well, they're not here but...
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing-pt-2/
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing-pt-3/
I could flesh those out into an article if you think it would be useful. Drop me a mail/pm with anything you think should be added, explained, corrected, etc.

This must be where I learned the little I know about it.;)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

GilaMonster (1/5/2009)
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.
Wasn't that for the 'catch-all' type queries? ((Col1 = @var1 or @var1 IS NULL) AND (Col2 = @var2 OR @Var2 IS NULL)...)
I know Itzik mentioned that OPTION RECOMPILE 'fixes' those in SQL 2008 but didn't in 2005.
Or are you thinking of something else?

That's probably it.

[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]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Grant Fritchey (1/5/2009)
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.

Ummm.... what's unnecessary about coming up with a good execution plan for a large batch? I can see it maybe being a problem for a heavy hitting bit of GUI code, but for a batch?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 9

Jeff Moden (1/6/2009)
Grant Fritchey (1/5/2009)
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.
Ummm.... what's unnecessary about coming up with a good execution plan for a large batch? I can see it maybe being a problem for a heavy hitting bit of GUI code, but for a batch?

True. But those of whose who primarily live in OLTP land, recompiles are usually a problem, not usually a solution.

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

Grant Fritchey (1/6/2009)
True. But those of whose who primarily live in OLTP land, recompiles are usually a problem, not usually a solution.

Dang it... I always forget about the other side of the pasture... thanks, Grant.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

David Levy SSCommitted Points: 1622 More actions · Answer 11

I have always seen the recompile hint as a great way to get out of the business day especially on an OLTP system but I have never used it at the stored procedure level, just at the individual statement level.