Mombu the Microsoft Forum sponsored links

Go Back   Mombu the Microsoft Forum > Microsoft > Very slow after applying sp3!
User Name
Password
REGISTER NOW! Mark Forums Read

sponsored links


Reply
 
1 1st April 21:52
uri dimant
External User
 
Posts: 1
Default Very slow after applying sp3!


Albert
Have you tried update statistics?
  Reply With Quote


  sponsored links


2 1st April 21:52
albert hew
External User
 
Posts: 1
Default Very slow after applying sp3!


Uri
I did. It is not showing any difference !

In fact, when I re-installed the SQL2000 server, I 'bcp'ed in the data
and re-built the indexes.

Albert
  Reply With Quote
3 1st April 21:52
andrew j. kelly
External User
 
Posts: 1
Default Very slow after applying sp3!


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
  Reply With Quote
4 1st April 21:53
albert hew
External User
 
Posts: 1
Default Very slow after applying sp3!


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
  Reply With Quote
Reply


Thread Tools
Display Modes




Copyright 2006 SmartyDevil.com - Dies Mies Jeschet Boenedoesef Douvema Enitemaus -
666