Stored Procedure - Unable to replicate poor performance

Question

Stored Procedure - Unable to replicate poor performance

marshall.jones

SSC Enthusiast

Points: 177
More actions
April 26, 2009 at 9:16 pm

#384447

I have a stored procedure that when captured in a trace is running very poorly. When I run in query analyser however it runs fine.
When the trace is running it will 95,000 reads and have a duration take about 24 seconds to complete. When I run manually (copy and pasting the command text to ensure I'm running the same thing) it runs under 5,000 reads and takes less than half a second to complete.
I've tried to ensure it's not caching a bad query plan by adding "with recompile" at the top of the proc but this doesn't seem to make any difference.
The proc in question runs with dynamic sql via sp_executesql.
Can anyone suggest anything?

Viewing 7 posts - 1 through 6 (of 6 total)

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

David O Hall of Fame Points: 3228 More actions · Answer 1

Are you running the two test back to back? The second run sounds like it is benefiting from all the data being in the buffer pool (aka a warm cache.)

David

marshall.jones SSC Enthusiast Points: 177 More actions · Answer 2

It doesn't appear to be that. I can rerun the query many times in either QA or profiler and still get the same results (consistantly fast or slow)

For now I've hacked in a "option(compile)" into the end of the proc. This has reduced the slowdown and will fix the problem for now.

From my googling I found that sp_executesql has a bad habit of caching query plans that don't match. Recompiling can help with this or using a query plan which is something I haven't tried yet.

Paul White SSC Guru Points: 150467 More actions · Answer 3

Hey,

It would have helped a great deal had you posted example code.

WITH RECOMPILE at the top of a procedure does not extend to any dynamic SQL run by that procedure.

Adding WITH RECOMPILE to the called procedure would force the whole called procedure to recompile.

Adding OPTION (RECOMPILE) or OPTION(OPTIMIZE FOR(@var=value)) to the problem statement is a good solution if it is suffering due to parameter sniffing, or if your database is running with forced parameterization. You also have the option of a USE PLAN hint, or a plan guide (template). There really are many possible causes, and therefore many possible solutions.

Without more details, it is difficult to say which would be right for you.

Cheers,

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

marshall.jones SSC Enthusiast Points: 177 More actions · Answer 4

Let me scare you a little Paul and tell you that Vanders helped me out already. Good to see a TM guy dishing out advice tho 😛

Paul White SSC Guru Points: 150467 More actions · Answer 5

marshall.jones (4/27/2009)
Let me scare you a little Paul and tell you that Vanders helped me out already. Good to see a TM guy dishing out advice tho 😛

Matt helping someone? You sure it was the same guy :laugh:

Didn't know he was on here - or did you mean he helped you out at work?

Thanks for the scare! 😎

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

marshall.jones SSC Enthusiast Points: 177 More actions · Answer 6

I keep all my ex workermates on close call on MSN for situations just like this (tho you are on that list for some reason too tho I don't think we've ever met).

Thanks for the answer tho.