Re: Very slow after applying sp3!

From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 08/03/03

  • Next message: svetlyo: "Cumulative Patch for Microsoft SQL Server (815495)" 
    Date: Sun, 3 Aug 2003 10:00:09 -0400

    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
>
>
  • Next message: svetlyo: "Cumulative Patch for Microsoft SQL Server (815495)"

    Relevant Pages

    • Re: Bind Values Trouble
      ... This is because placeholders cannot be used for column names, ... validating the structure of the SQL, ... execute the SQL can be postponed until execution time. ...
      (perl.dbi.users)
    • 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)
    • Re: Very slow after applying sp3!
      ... Dear Andrew ... I tried generating the execution plan but failed to do so because the ... I went a step further by diagnosting the store procedure. ...
      (microsoft.public.sqlserver.security)
    • Re: SET NOCOUNT ON OPTION
      ... after an insert or update operation. ... If your store procedure just SELECT things, do not hesitate to put SET ... NOCOUNT ON at the begining of the store procedure. ... the SET NOCOUNT is specific to the procedure and is set at execution ...
      (microsoft.public.sqlserver.programming)
    • Re: Performance problem on initial call of stored procedure
      ... It's the behaviour like SQL works. ... creates or validates access plans. ... For dynamic embedded SQL no access plan is stored in the program object ... After the first execution the data path will be closed again. ...
      (comp.sys.ibm.as400.misc)