Date parsed: 16/10/2007 04:20:17
Date: Tue, 16 Oct 2007 09:20:17 -0500

1) Consider forcing an update statistics call each night during down time.

2) Schedule a job to do the recompile on a regular schedule.

3) perform statement level profile of the sproc executions and attempt to
specifically tune the bad sections.

4) Consider optimizer hints - but beware, if you don't know what you are
doing or your access patterns you could really slow things down here.
However, if you KNOW that a particular index should ALWAYS be used you can
force it to 'help' the optimizer out.

5) Turn on Show Actual Execution Plan and run the sproc in Query Analyzer or
SSMS. Also do SET STASTICS IO ON. These will allow you to see all actual
plans and the amount of reads for each part of the sproc. NOTE that if you
have cursors there could be an overwhelming amount of return.

6) Get some training and/or hire a mentor to assist you in learning and also
review your systems.

--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.


"Maury Markowitz" <MauryMarkowitz@discussions.microsoft.com> wrote in
message news:C0ACBAB8-D8C4-4BCB-AA88-1F133CE7336C@microsoft.com...
>I am still baffled by one storedproc, which requires a recompile every week
> or so even though the data hasn't really changed. More data is added to
> the
> end of the table, but running the SQL by hand always takes a fixed amount
> of
> time.
>
> I know this is because a bad plan is being stored. But why? Why does the
> optimizer always pick a good plan when I run it by hand, but a bad one
> when I
> recompile?
>
> And how do I debug this? Is there some way to examine the plan? I'm afraid
> this is beyond my normal skillset.
>
> Maury

Date parsed: 16/10/2007 15:21:33
Date: Tue, 16 Oct 2007 22:21:33 -0700

On Oct 16, 7:37 pm, Maury Markowitz
<MauryMarkow...@discussions.microsoft.com> wrote:
> I am still baffled by one storedproc, which requires a recompile every week
> or so even though the data hasn't really changed. More data is added to the
> end of the table, but running the SQL by hand always takes a fixed amount of
> time.
>
> I know this is because a bad plan is being stored. But why? Why does the
> optimizer always pick a good plan when I run it by hand, but a bad one when I
> recompile?
>
> And how do I debug this? Is there some way to examine the plan? I'm afraid
> this is beyond my normal skillset.
>
> Maury

Maybe you can do something like:

select *
into #all_rows_not_summed_yet
from large_table
where large_table.reporting_period between '20010101' and '20020301'
and large_table.id in (1,10,50)

Then you can do a sum from that temp table into your reporting table.
HTH.

Date parsed: 16/10/2007 16:19:24
Date: 16 Oct 2007 23:19:24 -0700

On Oct 16, 9:38 pm, Maury Markowitz
<MauryMarkow...@discussions.microsoft.com> wrote:
> "TheSQLGuru" wrote:
>
> Let me describe the SP first off (I should have done this earlier):
>
> We have a "reporting table" that is basically the output of a SUM query. The
> table is typically about 2500 rows long, and is only 20 columns wide, almost
> all of them int/float/money's.
>
> The input to this table is a SUM on a much larger table, about 2 million
> rows. It is heavily indexed and has excellent performance in widespread daily
> use.
>
> The SP is _extremely_ simple, it:
>
> 1) TRUNCATEs the reporting table
> 2) SELECT INTOs the SUM query
> 3) UPDATEs the reporting table three times with small bits of data collected
> from other tables (all much smaller and rarely updated)
>
> No cursors, no loops, and only one local var (the date, trimmed to the
> "start of day").
>
> > 1) Consider forcing an update statistics call each night during down time.
>
> We do, it's part of the maint plan. Or do you mean update stats on the SP
> itself?
>
> > 2) Schedule a job to do the recompile on a regular schedule.
>
> I am doing this nightly now in order to avoid the timeout.
>
> > 3) perform statement level profile of the sproc executions and attempt to
> > specifically tune the bad sections.
>
> I have done this, which is why all of this is so mystifying.
>
> I copied the SP into query analizer, removed the INTO/UPDATEs, and ran it.
> 21 seconds on a dirty cache, 15 on a primed one.
>
> A freshly compiled version of the SP takes about 12-20 seconds depending on
> the cache state.
>
> After one week, it times out after two minutes. When allowed to run without
> timeouts, it goes on for about 5 minutes.
>
> I suppose it's possible all the time is taking place in the INTO and three
> following UPDATEs. Is there a way to find out?
>
> > 5) Turn on Show Actual Execution Plan and run the sproc in Query Analyzer or
> > SSMS. Also do SET STASTICS IO ON. These will allow you to see all actual
> > plans and the amount of reads for each part of the sproc. NOTE that if you
> > have cursors there could be an overwhelming amount of return.
>
> I will do this, but unfortunately my attempts to avoid the timeout mean it's
> getting recompiled every night. Still worth looking at though.
>
> Maury

After reading your post a bit more carefully I think the select into
is taking up most time. Instead of summing from the main table maybe
first you should select the rows into a temp table, for example,
select * into #all_rows_not_summed_yet from large_table. That should
be straight forward insert. Then you should do the sum on that temp
table. HTH.

Date parsed: 17/10/2007 14:26:55
Date: Wed, 17 Oct 2007 21:26:55 -0700

On Oct 17, 7:34 pm, Maury Markowitz
<MauryMarkow...@discussions.microsoft.com> wrote:
> "SB" wrote:
> > Maybe you can do something like:
>
> > select *
> > into #all_rows_not_summed_yet
> > from large_table
> > where large_table.reporting_period between '20010101' and '20020301'
> > and large_table.id in (1,10,50)
>
> > Then you can do a sum from that temp table into your reporting table.
> > HTH.
>
> I think it's important to stress that this is _not_ a SQL performance
> problem. The SQL statement takes only a few seconds to run. It's only when
> you put it in the SP and then wait a few weeks that the plan goes bad.
>
> To put this in perspective, consider "SELECT count(*) FROM tblIventory". No
> matter how many times you run this you'd expect the time to be somewhat
> similar. If it took 10 seconds the first time, maybe it will be 5 seconds or
> 20, but not 600. What I'm seeing is that I put that inside an SP, and it
> starts off at 10 seconds, and then by the end of the week its 60, and another
> week 120.
>
> Maury

Create the procedure with recompile option.

Date parsed: 18/10/2007 14:55:12
Date: Thu, 18 Oct 2007 21:55:12 -0700

At this point you have to get into the whole mechanics of execution
plans and statistics updates and parameter sniffing.

But don't feel alone, the problem apparently has the entire Microsoft
SQL Server design staff stumped, too.

Josh


On Tue, 16 Oct 2007 06:37:02 -0700, Maury Markowitz
<MauryMarkowitz@discussions.microsoft.com> wrote:

>I am still baffled by one storedproc, which requires a recompile every week
>or so even though the data hasn't really changed. More data is added to the
>end of the table, but running the SQL by hand always takes a fixed amount of
>time.
>
>I know this is because a bad plan is being stored. But why? Why does the
>optimizer always pick a good plan when I run it by hand, but a bad one when I
>recompile?
>
>And how do I debug this? Is there some way to examine the plan? I'm afraid
>this is beyond my normal skillset.
>
>Maury