July 7, 2006 at 3:10 pm
First, I am not a DBA but have 5 years of developing experience in sql server 2000 and have taken on the task of figuring out an issue that has come up at work. I noticed that when one of our clients upgraded their service pack from 3a to 4 on Sql Server 2000, they saw a huge increase in the time it took to run a dts package that includes several stored procedures, etc. Originally, under service pack 3a - it took about 18 minutes to run and with service pack 4 installed the same job took an 1:45 minutes to run.
The client has new dual core processor/server but they are not running it with the intel 64bit technology enabled. Their raid controller has the write to cache turned off as well.
My desktop at work also has a dual core processor with 2 gigs of memory and a 160 gig hard drive, and the desktop does not have the intel 64bit technology enabled either.
I devised an experiment to run on my own computer. I had copies of the databases from the client and I ran the same job on my computer which had the developer edition of SQL Server 2000 and service pack 3a. It took about 14 minutes to run. Next, I installed service pack 4 and re-ran the same job. It took an hour and 42 minutes to run.
I have since tried updating statistics, etc. Nothing seems to improve the performance. I can't see that the cpu is running at capacity either when I run the job.
Can someone tell me what is going on? I've posted elsewhere but no one has apparently had the issue with service pack 4 as I have had.
July 7, 2006 at 5:15 pm
Could you get the execution plans for the two queries and see what is different. Sometimes a service pack will include tweaks to the optimizer that might (gasp) de-optimize some edge-case queries.
Posting the query, the table and index ddl and the execution plans will help in the diagnosis.
July 9, 2006 at 6:54 am
For slow performance, first thing I would like is at execution plan.
Do we have some datatype conversion which is going on? I remember I posted this KB earlier.
Lets see if that helps...
http://support.microsoft.com/kb/899976/en-us
FIX: A query that involves data that is the numeric data type may return incorrect results in SQL Server 2000 SP3 and in earlier SQL Server 2000 service packs
As per KB:
<script type=text/javascript>loadTOCNode(3, 'moreinformation');</script>In SQL Server Enterprise Manager, add the -T9059 startup parameter to SQL Server. To do this, follow these steps:
| 1. | In SQL Server Enterprise Manager, expand Microsoft SQL Servers, expand SQL Server Group, right-click the computer that is running SQL Server, and then click Properties. |
| 2. | On the General tab, click Startup Parameters. |
| 3. | In the Parameter box, type -T9059, and then click Add. |
| 4. | Click OK two times, and then close SQL Server Enterprise Manager. |
| 5. | Restart the computer that is running SQL Server. |
May 30, 2007 at 2:41 am
Does this flag -T9059 exist under MSSQL 2005 as well? Because I guess it will give us the same performance penalty when doing
select * from <table> where <primary key> = <number>
where <primary key> is of type numeric(9, 0) since it interprets <number> as integer and has to downcast it.
So my question is if its possible to run with the -T9059 flag on MSSQL 2005 as well? Anyone knows?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply