SQL Server Performance Forum – Threads Archive

SQL2000 Performance degradation after SP4

Hi there I need help please. We have a serious performance issue after updating the SQL2000 Server to SP4. A stored procedure that use to run in only 5 minutes now take 1 hour 40 minutes to complete. We have never had any problems with any of the previous Service Packs. Does anyone know of a specific problem with SP4?

http://www.aspfaq.com/show.asp?id=2543 you may experience performance degradation, or output changes, in queries that involve decimal or numeric columns with different precision/scale —————————————-
http://spaces.msn.com/members/dineshasanka

Hi dineshasanka I’ve tried what you suggested, but it did not resolve the problem. Thanks
Ernie_A

Can you tell me what did you do exactly. Does your SP uses decimal or numeric columns —————————————-
http://spaces.msn.com/members/dineshasanka

The SP is part of a payrun calculation and uses decimal columns. All calculations are handled in a number of SP’s. The biggest concern is that everything is working 100% with SP3. Nothing has changed in the DB since updating to SP4. I’ve run a trace using Profiler and found that the SP is taking longer on updating one of the tables. This increases exponentially as more records are being processed. This is not the case when we run the same SP on a Server that is up to SP3. Hope this will shed more light on the problem. Thanks
Ernie_A

This discussion might be relatedhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58294&whichpage=1

does your system use PAE/AWE, if so, did you apply the post sp4 hotfix for AWE also, were you on SP3 build 760, or the MS-031 hotfix, build 818,
the cost based optimizer formulas changed at build 765, hence some execution plans might be different. also, does this sp generate a parallel execution plan. other than that, i would need to see the execution plans on SP3 and SP4, are there differences

Thanks for the info mmarovic I’ve read what the guys battled with here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58294&whichpage=1 I’ve come to the conclusion that if a big company like that, battled to get things sorted with Microsoft, what chance do I have. We’ve logged a support call with MS and I’ve sent them a number of trace files from both SP3 and SP4. I will send them a copy of the DB next. I’ll wait to see if anything happens and will keep you informed. I am going to recommend to our client to roll back to SP3. I can not wait for weeks on end to see if the problem will be fixed. The client has a much larger DB and where the SP use to take only 15 minutes on SP3 it is now taking almost 8 hours on SP4. Thanks for all your help. It’s been the first time I’ve used a forum and will definately keep a close eye on it from now on. It’s great.

well since there is no resolution from this forum,
and you have posed this problem to MS, including sending them the DB i will now mention my service, which is the link at the bottom left of the sql-server-performance home page
it includes getting the same info MS PSS requests, ie, the perfmon logs & profiler traces,
but instead of the full database, i work with the cloned database, which has the statistics but not the data.
it is easier to ship the cloned db, but requires more expertise to work with, so i do not think other people can do this yet

just my humble 0,2ct but after you applied the sp, did you rebuild indexes, update usage and update statistics ? What I found with sp4 is the engine being far more sensitive (in the negative way) with implicit conversions.
e.g. your column is datatype char and the var in your where-clause is integer. The implicit conversion eats it up !
Even with small tablesets the effect of altering to the correct datatype (i.e. the one of the table-column) has a huge impact !

Hi alzdba All indexes were rebuilt and this made no change in the processing time Thanks
E

I’m not 100% sure, but I believe you have to clear the proccache after you’ve updated the statistics, so you procedures will be compiled with the statistics. dbcc freeproccache

I’ve run DBCC FREEPROCCACHE This made no difference. Thanks anyway.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by alzdba just my humble 0,2ct What I found with sp4 is the engine being far more sensitive (in the negative way) with implicit conversions. e.g. your column is datatype char and the var in your where-clause is integer. The implicit conversion eats it up ! Even with small tablesets the effect of altering to the correct datatype (i.e. the one of the table-column) has a huge impact ! <hr height="1" noshade id="quote"></blockquote id="quote"> THANK YOU alzdba This got me thinking that there might be something wrong with the DB. And there it was. A variable was declared as decimal(6) in the SP, but the table itself was decimal(3). This did not cause any problems with SP3(Service Pack3), but it could not be said for SP4. INSERTS into the DB did not slow the Stored Procedure (SP) down by much, but the SP slowed drastically when the SP included UPDATES. Processing time increased exponentially. With a 500,000 records processing took 4.67 times longer on the mismatched DB compared to 12.23 times longer for 2,000,000 records. I ran the script below on both SP3 and SP4. On SP3 I achieved similar times regardless of the difference between the table and the SP. If anybody#%92s interested I#%92ve included the test that I ran in Querry Analyzer. This might give you some indication of the impact that SP4 may have on your system, that is if you are experiencing a similar problem. I’ve also sent my findings to the Microsoft Proffesional Support Engineer and will document any recomendations OR Hotfixes that we might receive from Microsoft. Thanks for everyone’s input.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] ——————————————————————————————————— SCRIPT TO RUN IN QUERRY ANALYZER TO TEST ON SP4 ———————————————– –DROP TEST TABLE if exists (select * from dbo.sysobjects where id = object_id(N'[dba].[pr_runtest]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1) drop table [dba].[pr_runtest] GO –CREATE TEST TABLE CREATE TABLE [dba].[pr_runtest] ( [runno] [decimal](6,0) NOT NULL , –FOR SECOND TEST CHANGE TO DECIMAL 6,0 [empno] [decimal](10,0) NOT NULL , [calcno] [decimal](6,0) NOT NULL , [calctime] [decimal](6,0) NULL , [indsub] [decimal](2,0) NULL , [indval] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [opcode] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [op1a] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [op1n] [decimal](6,0) NULL , [op1value] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [op2a] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [op2n] [decimal](6,0) NULL , [op2value] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [op3a] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [op3n] [decimal](6,0) NULL , [op3value] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [goto_label] [decimal](6,0) NULL , [remarks] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT [PK__pr_runtest__08012052] PRIMARY KEY CLUSTERED ( [runno], [empno], [calcno] ) WITH FILLFACTOR = 90 ON [PRIMARY] ) ON [PRIMARY] GO ——————————————————————————————————— ——————————————————————————————————— –RUN STORED PROCEDURE IN QUERY ANALYZER DECLARE @ld_emp_LIMIT decimal(9) DECLARE @ld_code_LIMIT decimal(6) DECLARE @ld_emp decimal(10) DECLARE @ld_lineno decimal(6) DECLARE @ld_calcno decimal(3) DECLARE @ld_code decimal(6) SELECT @ld_emp_LIMIT = 2000 SELECT @ld_code_LIMIT = 100 SELECT @ld_emp = 1 SELECT @ld_code = 1 SELECT @ld_lineno = -1 SELECT @ld_calcno = 10 DELETE FROM dba.pr_runtest SELECT @ld_emp = 1 WHILE @ld_emp <= @ld_emp_LIMIT BEGIN SELECT @ld_calcno = 1 WHILE @ld_calcno <= @ld_code_LIMIT BEGIN insert into dba.pr_runtest(runno,empno,calcno,calctime,indsub,indval,opcode,op1a,op1n,op2a,op2n,op3a,op3n,goto_label,remarks) values(@ld_lineno,@ld_emp,@ld_calcno,10,0,”,’AAA’,’Q’,374,”,0,’Q’,375,0,”) SELECT @ld_calcno = @ld_calcno + 1 END SELECT @ld_emp = @ld_emp + 1 END SELECT @ld_emp = 1 WHILE @ld_emp <= @ld_emp_LIMIT BEGIN UPDATE dba.pr_runtest SET indsub = 40, indval = ‘A’, op1value = ‘UPDATE – ‘ + convert(varchar,getdate()) WHERE empno = @ld_emp AND runno = @ld_lineno AND calcno = 1 SELECT @ld_emp = @ld_emp + 1 END GO – drop table [dba].[pr_runtest]; ——————————————————————————————————— ——————————————————————————————————— –1ST TEST CARRIED OUT WITH THE FOLLOWING DETAILS –SELECT @ld_emp_LIMIT = 2000 –SELECT @ld_code_LIMIT = 100 –THIS INSERTED 200,000 RECORDS IN THE PR_RUNTEST TABLE AND 2000 UPDATES — RESULTS — RESULTS — RESULTS — RESULTS — RESULTS — RESULTS — RESULTS — RESULTS – –Decimal 3 – 8:10 –Decimal 6 – 1:45 –MISMATCH RAN 4.67 TIMES LONGER THAN THE SCRIPT THAT MATCHES THE TABLE. –THESE RESULT SETS ARE ONLY FOR SP4. –TIMES OF 1 MINUTE 30 SECONDS WERE REACHED FOR BOTH DEC 3 AND DEC 6 ON SP3. ——————————————————————————————————— –2ND TEST CARRIED OUT WITH THE FOLLOWING DETAILS –SELECT @ld_emp_LIMIT = 5000 –SELECT @ld_code_LIMIT = 200 –THIS INSERTED 1,000,000 RECORDS IN THE PR_RUNTEST TABLE AND 5000 UPDATES — RESULTS — RESULTS — RESULTS — RESULTS — RESULTS — RESULTS — RESULTS — RESULTS – –Decimal 3 – 94:10 –Decimal 6 – 7:42 –MISMATCH RAN 12.23 TIMES LONGER THAN THE SCRIPT THAT MATCHES THE TABLE. –THESE RESULT SETS ARE ONLY FOR SP4. –TIMES OF 8 MINUTES WERE REACHED FOR BOTH DEC 3 AND DEC 6 ON SP3. ——————————————————————————————————— –3RD TEST CARRIED OUT WITH THE FOLLOWING DETAILS –SELECT @ld_emp_LIMIT = 2000 –SELECT @ld_code_LIMIT = 100 –NO UPDATES WHERE MADE – INSERTS ONLY –THIS INSERTED 100,000 RECORDS IN THE PR_RUNTEST TABLE — RESULTS — RESULTS — RESULTS — RESULTS — RESULTS — RESULTS — RESULTS — RESULTS – –Decimal 3 – 1:58 –Decimal 6 – 1:31 –MISMATCH RAN 5.44 TIMES LONGER THAN THE SCRIPT THAT MATCHES THE TABLE. –THESE RESULT SETS ARE ONLY FOR SP4.

Thank you for posting the problem resolution. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

We’ve also experienced performance issues with SP4 on our test server. We decided to remain on SP3. We’ve tested your SP and found similar results. We will have to look at our DB for similar discrepancies.

]]>