Re: Very slow after applying sp3!

From: Albert Hew (voonkong.hew_at_fedex.com)
Date: 08/05/03 

Date: Tue, 5 Aug 2003 15:21:00 +0800

Dear Andrew

Thanks for the response.
I tried generating the execution plan but failed to do so because the
SQL2000 optimizer refuses to generate execution plan when it encounters a
temp table. I may be wrong, anyone could offer some suggestion to generate
the execution plan for code using temp table ??

Anyway, I went a step further by diagnosting the store procedure.
Interestingly this is what I found out.

For the same machine running SQL 2000 with sp2, execution of the following
code was excellent, very fast... took me about 15 minute to generate a
monthly report. However, after applying sp3, the CPU usage spike irregulary
and it took longer than 5 hours to complete the same job.

SELECT a.shipment_profile_id,
                               a.scan_type_c,

                               a.scan_exception_c,

                               a.scan_dt,

                               a.track_loc_c

INTO #asia_new_tb FROM scan a

WHERE EXIST ..condition1

and condition2

and condition3

..

.

..

DECLARE asia_new CURSOR for

                        SELECT shipment_profile_id,

                               scan_type_c,

                               scan_exception_c,

                               scan_dt,

                               track_loc_c

                               FROM #asia_new_tb

OPEN asia_new

FETCH asia_new into @shipment_id,

                    @scan_type,

                    @scan_exce,

                    @scan_dt,

                    @track_loc

CLOSE asia_new

DEALLOCATE asia_new

DELETE #asia_new_tb

All I did was ...made minor modification to the above code by declaring the
cursor to a user table instead of the temp table. So the code looks like the
following (running in a machine with SQL 2000 and sp3).

The execution of this code by SQL2000 with sp 2 was extreme slow. However,
it works extremely well in SQL2000 with sp3.
Appreciate you or other can help to verify this and even perhaps find an
explanation for this.

DECLARE asia_new CURSOR for

                                       select a.shipment_profile_id,

                          a.scan_type_c,

                                     a.scan_exception_c,

                                    a.scan_dt,

                                    a.track_loc_c

FROM scan a

WHERE EXIST..condition1

AND condition2

AND condition3

OPEN asia_new

FETCH asia_new INTO @shipment_id,

                    @scan_type,

                    @scan_exce,

                    @scan_dt,

                    @track_loc

CLOSE asia_new

DEALLOCATE asia_new

DELETE #asia_new_tb

"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:eDRGOecWDHA.2276@TK2MSFTNGP10.phx.gbl...
> What is the difference in the execution of the 2 plans? If you can narrow
> down what part is effected and post it we might be able to suggest an
> alternative method. If you can get a repro script you may want to give ms
> pss a call.
>
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;sql SQL Support
> http://www.mssqlserver.com/faq/general-pss.asp MS PSS
>
> --
>
> Andrew J. Kelly
> SQL Server MVP
>
>
> "Albert Hew" <voonkong.hew@fedex.com> wrote in message
> news:uBQvL%23WWDHA.2352@TK2MSFTNGP12.phx.gbl...
> >
> >
> > I encountered a weird problem.
> >
> > I coded a stored procedure which is used to generate the monthly report,
> > usually takes about 15 minutes to complete in the SQL2000 server with
sp2.
> > However, after applying the SQL 2000 sp3a, it takes longer than 5 hours
to
> > complete! The CPU usage has been approaching 100% when the store
> procedure
> > is being executed.
> >
> > To confirm this is resulted from the sp3a installation. I actually
> > re-install the Windows 2000 server (including sp4) and SQL 2000 server
> from
> > scratch. I also applied the SQL 2000 service pack 2. When I re-run the
> > store procedure, the execution time needed by the same store procedure
is
> > only about 15 minutes and CPU usage is back to normal, about 40% during
> the
> > execution time.
> >
> >
> >
> > The SQL 2000 server is running on HP TC4100 machine with 2G RAM, 2CPUs
and
> 7
> > SCSI hard disk.
> >
> > Greatly appreciate if anyone could offer some advice on how to approach
> > this.
> >
> >
> >
> > Albert
> >
> >
>
>

Relevant Pages

  • Re: index bloat?
    ... execution plan is, how can I get the query optimizer to generate it - *without the hints*? ... Because some users of the DB are using report designing software that will just generate the join query with no hints, and I don't want them sitting there for hours when they don't have to. ... Does the optimizer not choose the merge join because it requires a bookmark lookup? ...
    (comp.databases.ms-sqlserver)
  • Re: Frustrating Execution Plan Analysis
    ... >I've always been under the impression that Query cost is directly related to ... But if the execution plan isn't making sense, ... SQL Server will make a plan based on assumptions about what will ...
    (microsoft.public.sqlserver.programming)
  • Re: Execution plan reuse
    ... Is there way to execute SPs without actually sustaining the affects of it? ... response time of my SPs by keeping the execution plans in cache before the ... > An execution plan for a stored procedure can be reused by any connection ... >>I want to confirm my understanding of execution plan reusage. ...
    (microsoft.public.sqlserver.programming)
  • Re: execution plan for a SP with transaction differs from one without transaction
    ... temp table and that write into another table from the temp one.. ... the execution plan is the same.. ... it is creating a temporary table, doing a bulk ...
    (microsoft.public.sqlserver)
  • Re: Very slow after applying sp3!
    ... you would be best served to get rid of the cursor altogether and use set ... > I tried generating the execution plan but failed to do so because the ... I went a step further by diagnosting the store procedure. ... > and condition2 ...
    (microsoft.public.sqlserver.security)