Sp inside Sp gets stale and very slow. Unless I recreate it, when its fast

Question

Post reply

Sp inside Sp gets stale and very slow. Unless I recreate it, when its fast

shmuel

SSC Veteran

Points: 286
More actions
September 10, 2008 at 7:44 pm

#126314

I have a sp, (sp_outer) that INSERT INTO #TempTable/EXEC's an inner sp (sp_inner) to fill a table that sp_outer uses. If I drop and recreate sp_inner, and execute it by itself, it runs in about 7 seconds. sp_outer then takes about 10 seconds altogether.
If, however, I let things sit for a while (from minutes to several hours), sp_inner becomes sick, and sp_outer ends up taking about 2 minutes to run.
sp_inner gets a bunch of data out of two large (800k rows) tables. The data is gotten by inner joining with another table, appropriate sums are performed with 1 grouped-by column. All 3 tables have primary keys clustered on the same corresponding int columns.
The "filtering" table is about 26000 rows. The result of sp_inner is about 450 rows.
I don't know how to use profiler to determine the cause of this slowdown.
Any advice would be greatly appreciated.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

If you use parameters on either proc, you may be fighting "parameter sniffing". Google it for a wealth of information.

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Can you post the procedures?

For a brief overview of parameter sniffing -

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

http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/

http://sqlinthewild.co.za/index.php/2008/05/22/parameter-sniffing-pt-3/

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

shmuel SSC Veteran Points: 286 More actions · Answer 3

Well, I can't post the procx, cuz my boss would have a fit. I did fiddle around with them as follows:

1. Within sp_inner, I made all the parameters that appear in the ginormous query be local variables that are copied from the proc's arguments.

2. If I drop and recreate sp_inner, and then run sp_outer, it takes forever (90 seconds for ginormous query).

3. After waiting forever in item 2, above, I now execute sp_inner, by itself, using THE EXACT SAME parameters as sp_outer just called it, sp_inner finishes in 5 seconds.

4. Now, if I run sp_outer, it comes back in 7 to 10 seconds, until I do step 2, again, or something else happens, like it gets stale, or a backup is performed.

BTW, if I put "WITH RECOMPILE" on sp_outer's call to sp_inner, it takes forever always.

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

Run the sp both ways with the execution plan on. See if there's a big difference in the inner proc's exec plan. You could also use Statistics IO and Statistics time to see where the most time is.

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

shmuel SSC Veteran Points: 286 More actions · Answer 5

I've captured the plans for the good, fast, execution, and the bad, slow one. I can't figure out why it makes different plans, since I'm callling the sp_inner the same way in both cases.

The file is quite wide. I viewed it in visual studio.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

I always love to see that from a boss, don't post the sp code, don't post the DDL, but ya sure post the actual full executions plans... they won't be able to figure anything out from that :D.

shmuel SSC Veteran Points: 286 More actions · Answer 7

sorry, she gets paid to make the rules. I get paid to be her loyal servant.

Still, why should an sp_inner call from within sp_outer run slower and have a junky plan, when the calling parameters are identical?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

How are you calling the sps?

Are the settings the same on all connections (if they are different)?

Are you qualifying the names?

Are you using the same case for all characters?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

shmuel (9/12/2008)
sorry, she gets paid to make the rules. I get paid to be her loyal servant.
Still, why should an sp_inner call from within sp_outer run slower and have a junky plan, when the calling parameters are identical?

I know... but that's just too funny when you're no caught in the situation :w00t:.

shmuel SSC Veteran Points: 286 More actions · Answer 10

The plot thickens...

I removed the "create table #hold_sp_inner_results" statement, and placed it by itself, and then performed the INSERT INTO/EXEC of sp_inner. Same slowness, unless sp_inner is run by itself first.

I then put an "INTO dbo.goofy" clause into the main, wicked, SELECT in sp_inner. It zips thru real FAST.

I removed the "INTO..." from sp_inner, and recreated the sp_inner. I truncated dbo.goofy, and INSERT/EXEC into ol' goofy. It was SLOW.

I even scripted dbo.goofy and made a create statement for it. I dropped dbo.goofy, used my create statement, and did an INSERT INTO/EXEC of sp_inner. It was SLOW.

So, it appears to be that, when using INSERT/EXEC, sp_inner "feels" something about the existence of the insert-target table and bollixes up the x-plan for the stored proc.

If this is the case, how would one get around it? The timing difference here is so profound (18 to one, or so) that it can't be ignored.