SQL Server Performance

SQL2000 Performance degradation after SP4

Discussion in 'T-SQL Performance Tuning for Developers' started by Ernie_A, Feb 21, 2006.

  1. Ernie_A New Member

    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?
  2. dineshasanka Moderator

    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
  3. Ernie_A New Member

    Hi dineshasanka

    I've tried what you suggested, but it did not resolve the problem.

    Thanks
    Ernie_A
  4. dineshasanka Moderator

  5. Ernie_A New Member

    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

  6. mmarovic Active Member

  7. joechang New Member

    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
  8. Ernie_A New Member

    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.
  9. joechang New Member

    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
  10. alzdba Member

    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 !
  11. Ernie_A New Member

    Hi alzdba

    All indexes were rebuilt and this made no change in the processing time

    Thanks
    E
  12. alzdba Member

    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
  13. Ernie_A New Member

    I've run DBCC FREEPROCCACHE

    This made no difference. Thanks anyway.
  14. Ernie_A New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by alzdba</i><br /><br />just my humble 0,2ct<br /><br />What I found with sp4 is the engine being far more sensitive (in the negative way) with implicit conversions.<br />e.g. your column is datatype char and the var in your where-clause is integer. The implicit conversion eats it up !<br />Even with small tablesets the effect of altering to the correct datatype (i.e. the one of the table-column) has a huge impact !<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />THANK YOU alzdba<br /><br />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. <br /><br />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.<br /><br />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. <br /><br />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.<br /><br />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.<br /><br />Thanks for everyone's input.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br /><br /><br /><br />---------------------------------------------------------------------------------------------------------<br />SCRIPT TO RUN IN QUERRY ANALYZER TO TEST ON SP4<br />-----------------------------------------------<br /><br />--DROP TEST TABLE<br />if exists (select * from dbo.sysobjects where id = object_id(N'[dba].[pr_runtest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)<br />drop table [dba].[pr_runtest]<br />GO<br /><br />--CREATE TEST TABLE<br />CREATE TABLE [dba].[pr_runtest] (<br />[runno] [decimal](6,0) NOT NULL , --FOR SECOND TEST CHANGE TO DECIMAL 6,0<br />[empno] [decimal](10,0) NOT NULL ,<br />[calcno] [decimal](6,0) NOT NULL ,<br />[calctime] [decimal](6,0) NULL ,<br />[indsub] [decimal](2,0) NULL ,<br />[indval] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[opcode] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[op1a] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[op1n] [decimal](6,0) NULL ,<br />[op1value] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[op2a] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[op2n] [decimal](6,0) NULL ,<br />[op2value] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[op3a] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[op3n] [decimal](6,0) NULL ,<br />[op3value] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[goto_label] [decimal](6,0) NULL ,<br />[remarks] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />CONSTRAINT [PK__pr_runtest__08012052] PRIMARY KEY CLUSTERED <br />(<br />[runno],<br />[empno],<br />[calcno]<br />) WITH FILLFACTOR = 90 ON [PRIMARY] <br />) ON [PRIMARY]<br />GO<br /><br />---------------------------------------------------------------------------------------------------------<br />---------------------------------------------------------------------------------------------------------<br />--RUN STORED PROCEDURE IN QUERY ANALYZER<br /><br /><br />DECLARE @ld_emp_LIMIT decimal(9)<br />DECLARE @ld_code_LIMIT decimal(6)<br />DECLARE @ld_emp decimal(10)<br />DECLARE @ld_lineno decimal(6)<br />DECLARE @ld_calcno decimal(3)<br />DECLARE @ld_code decimal(6)<br /><br />SELECT @ld_emp_LIMIT = 2000<br />SELECT @ld_code_LIMIT = 100<br />SELECT @ld_emp = 1<br />SELECT @ld_code = 1<br />SELECT @ld_lineno = -1<br />SELECT @ld_calcno = 10<br /><br />DELETE FROM dba.pr_runtest<br /><br />SELECT @ld_emp = 1<br />WHILE @ld_emp &lt;= @ld_emp_LIMIT<br />BEGIN<br /> SELECT @ld_calcno = 1<br /> WHILE @ld_calcno &lt;= @ld_code_LIMIT<br /> BEGIN<br /> insert into dba.pr_runtest(runno,empno,calcno,calctime,indsub,indval,opcode,op1a,op1n,op2a,op2n,op3a,op3n,goto_label,remarks) <br /> values(@ld_lineno,@ld_emp,@ld_calcno,10,0,'','AAA','Q',374,'',0,'Q',375,0,'')<br /> SELECT @ld_calcno = @ld_calcno + 1<br /> END<br /> SELECT @ld_emp = @ld_emp + 1<br />END<br /><br />SELECT @ld_emp = 1<br />WHILE @ld_emp &lt;= @ld_emp_LIMIT<br />BEGIN<br />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<br /> SELECT @ld_emp = @ld_emp + 1<br />END<br />GO<br /><br />-- drop table [dba].[pr_runtest];<br /><br />---------------------------------------------------------------------------------------------------------<br />---------------------------------------------------------------------------------------------------------<br />--1ST TEST CARRIED OUT WITH THE FOLLOWING DETAILS<br />--SELECT @ld_emp_LIMIT = 2000<br />--SELECT @ld_code_LIMIT = 100<br />--THIS INSERTED 200,000 RECORDS IN THE PR_RUNTEST TABLE AND 2000 UPDATES<br /><br /> -- RESULTS -- RESULTS -- RESULTS -- RESULTS -- RESULTS -- RESULTS -- RESULTS -- RESULTS --<br />--Decimal 3 - 8:10<br />--Decimal 6 - 1:45<br />--MISMATCH RAN 4.67 TIMES LONGER THAN THE SCRIPT THAT MATCHES THE TABLE.<br />--THESE RESULT SETS ARE ONLY FOR SP4.<br />--TIMES OF 1 MINUTE 30 SECONDS WERE REACHED FOR BOTH DEC 3 AND DEC 6 ON SP3.<br /><br />---------------------------------------------------------------------------------------------------------<br />--2ND TEST CARRIED OUT WITH THE FOLLOWING DETAILS<br />--SELECT @ld_emp_LIMIT = 5000<br />--SELECT @ld_code_LIMIT = 200<br />--THIS INSERTED 1,000,000 RECORDS IN THE PR_RUNTEST TABLE AND 5000 UPDATES<br /><br /> -- RESULTS -- RESULTS -- RESULTS -- RESULTS -- RESULTS -- RESULTS -- RESULTS -- RESULTS --<br />--Decimal 3 - 94:10<br />--Decimal 6 - 7:42<br />--MISMATCH RAN 12.23 TIMES LONGER THAN THE SCRIPT THAT MATCHES THE TABLE.<br />--THESE RESULT SETS ARE ONLY FOR SP4.<br />--TIMES OF 8 MINUTES WERE REACHED FOR BOTH DEC 3 AND DEC 6 ON SP3.<br /><br />---------------------------------------------------------------------------------------------------------<br />--3RD TEST CARRIED OUT WITH THE FOLLOWING DETAILS<br />--SELECT @ld_emp_LIMIT = 2000<br />--SELECT @ld_code_LIMIT = 100<br />--NO UPDATES WHERE MADE - INSERTS ONLY<br />--THIS INSERTED 100,000 RECORDS IN THE PR_RUNTEST TABLE<br /><br /><br /> -- RESULTS -- RESULTS -- RESULTS -- RESULTS -- RESULTS -- RESULTS -- RESULTS -- RESULTS --<br />--Decimal 3 - 1:58<br />--Decimal 6 - 1:31<br />--MISMATCH RAN 5.44 TIMES LONGER THAN THE SCRIPT THAT MATCHES THE TABLE.<br />--THESE RESULT SETS ARE ONLY FOR SP4.<br /><br />
  15. mmarovic Active Member

    Thank you for posting the problem resolution. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  16. sqldevdba New Member

    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.

Share This Page