June 20, 2006 at 9:26 am
Hi,
Has anyone come into performance issues after applying SQL 2000 SP4? Like different execution plans, change in optimizer, and what kinds of queries/joins become faster/slower?
Thanks,
Del piero
June 21, 2006 at 10:43 am
I ran into one massive performance slowdown that was actually sloppy work at a previous employer.
There was a table that used a DECIMAL(12,0) for a primary key (related to a COBOL issue that is too long to explain here). A specific stored procedure was attempting to find a specific record in the table with one parameter IE: @key DECIMAL so the stored procedure only had one line.
CREATE PROCEDURED storedProcedure(@key DECIMAL)
BEGIN
SELECT * FROM table WHERE primary_key = @key
END
When we applied SP4 an application that used to run in less than 10 minutes took over 24 hours to run! I noticed in the profiler that each call to the stored procedure was taking 500 milliseconds and it was being called thousands of times.
First I tried the following:
CREATE PROCEDURED dbo.storedProcedure(@key DECIMAL)
BEGIN
SET NOCOUNT ON
SELECT * FROM dbo.table WHERE primary_key = @key
END
That shortened the execution time to just over 400 milliseconds. Then I did the following:
CREATE PROCEDURED dbo.storedProcedure(@key DECIMAL(12,0))
BEGIN
SET NOCOUNT ON
SELECT * FROM dbo.table WHERE primary_key = @key
END
By adding the precision and scale to the search key the execution time was 0 milliseconds. That is correct, it was so short it couldn't be measured.
Cheers
June 22, 2006 at 2:47 am
Here you have a topic on locking issues after SP4 installation: http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic1883.aspx
<hr>
June 23, 2006 at 10:53 am
There is a laundry list of post-SP4 hotfixes available. Read through them to see what the identified and corrected issues are: http://support.microsoft.com/kb/894905/
June 23, 2006 at 10:59 am
There are some changes to optimization plans, yes, but they are pretty specific. The list of changes:
SQL Server 2000 Service Pack 4 Readme
K. Brian Kelley
@kbriankelley
June 25, 2006 at 7:46 am
Thanks for all the replies. It looks like we need to thoroughly test our applications before applying SP4 on a production database ... which sounds like a risky task! I also found something related from the following discussion topic:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=48&messageid=288736
Thanks,
Del Piero
June 25, 2006 at 9:01 pm
You should do that with every SP even if there have been no reported problems.
June 27, 2006 at 2:20 am
For Ted,
http://support.microsoft.com/kb/899976/en-us
<script type=text/javascript>loadTOCNode(2, 'moreinformation');</script>After you install SQL Server 2000 Service Pack 4 (SP4), SQL Server may not choose an index seek if you compare numeric columns or numeric constants that are of different precision or scale. Queries of this kind may run significantly slower in SQL Server 2000 SP4 than in SQL Server 2000 SP3. We recommend that you modify queries or schema so that the data type, the precision, and the scale are the same when comparisons are performed.
Microsoft already posted a workaround for this i.e. Trace Flag 9059
June 27, 2006 at 9:34 am
To Balmukund
Thanks that was an interesting read. In the case I mentioned we were getting the correct results which had been verified. It was simply too slow and based on the article you mentioned indexing was likely not working correctly.
Sadly the design was to use a DECIMAL(12,0) for a key instead of an INT so we didn't have roundoff errors it was simply too slow.
However, there may be other spots where they were having that problem so I am forwarding the link.
Cheers
Ted
June 28, 2006 at 10:39 am
BTW, is there any restriction that SP4 must be applied on top of SP3a? Or it is OK to apply SP4 on top of SP3 as well? I guess both should be OK but I just need to confirm ...
Thanks,
Del Piero
June 28, 2006 at 11:02 am
SP4 can be applied to any earlier version.
June 29, 2006 at 1:42 am
The SP's are cumulative. Everything in SP3 is in SP4 as well. You can install it over SP1 if you want. Just be aware that there are some post-SP3 (SP3a maybe??) hotfixes that were not included in SP4, and those hotfixes are overwitten as well.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply