CPU usage changed after upgrading to MS SQL Server 2000 from MS SQL Server 6.5

CPU usage changed after upgrading to MS SQL Server 2000 from MS SQL Server 6.5

Post by kct » Tue, 03 Feb 2004 08:55:57

Hi All,

I had two Window NT 4.0 Server, say A and B... We recently upgraded
from MS SQL Server 6.5 to MS SQL Server 2000 on Computer A, and for
Computer B, I installed SQL Server 2000 from scratch. These two have a
same RAM(512MB) and CPU speed...so the setup for hardware is almost

For server A, there are around 10 connection to this SQL Server...
For server B, there are also around 10 connection to this SQL

The database for SQL Server B is a copy from SQL Server A.... I
restored the database on SQL Server B from the backup dump of Server

We have a store procedure called usp_GetMemo(ID Interger) ... we feed
ID number to this store procedure and return its return Description...
this table has ID column and it's Non-Cluster index, allow the
duplicate row... there are 1.7 million records for this table...

Here is problem... whenever i execute more than 100 times for this
store procedure(usp_GetMemo(ID)) continuously, the CPU usage of
Computer A is 3 times bigger than computer B....

I did run the command "DBCC DBReindex" , "Update Statistics" for this
table on computer A, but did not make any difference...

Keep in mind that Computer A has been upgraded to SQL server 2000
while Computer B installed from scratch... would that make difference
somehow?? Before the upgrading, the CPU usage of computer A is the
same as Computer B...

Any help will be really appreciated,
Thanks alot,

CPU usage changed after upgrading to MS SQL Server 2000 from MS SQL Server 6.5

Post by cgros » Thu, 05 Feb 2004 03:30:31

Double-check that the indexes are the same on both boxes. Not having a
clustered index can cause high CPU usage.

Some performance references:
297864 INF: Performance Considerations for an Upgrade from SQL Server 6.5
Inside SQL Server 2000/7.0 by Kalen Delaney, http://www.yqcomputer.com/
Understanding Performance Testing,
224587 INF: Troubleshooting Application Performance with SQL Server
243586 INF: Troubleshooting Stored Procedure Recompilation
298475 INF: Information Required to Successfully Troubleshoot Application
175658 How To Gather Information for Effective Troubleshooting of
Performance http://www.yqcomputer.com/
319942 HOW TO: Determine Proper SQL Server Configuration Settings
311826 INF: Index Tuning Wizard Best Practices

Cindy Gross, MCDBA, MCSE
This posting is provided "AS IS" with no warranties, and confers no rights.