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
identical...

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

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
A...

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,

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
http://support.microsoft.com/?id=297864
Inside SQL Server 2000/7.0 by Kalen Delaney, http://www.insidesqlserver.com
Understanding Performance Testing,
http://msdn.microsoft.com/library/techart/d5dplyover.htm
224587 INF: Troubleshooting Application Performance with SQL Server
http://support.microsoft.com/?id=224587
243586 INF: Troubleshooting Stored Procedure Recompilation
http://support.microsoft.com/?id=243586
298475 INF: Information Required to Successfully Troubleshoot Application
http://support.microsoft.com/?id=298475
175658 How To Gather Information for Effective Troubleshooting of
Performance http://support.microsoft.com/?id=175658
319942 HOW TO: Determine Proper SQL Server Configuration Settings
http://support.microsoft.com/?id=319942
311826 INF: Index Tuning Wizard Best Practices
http://support.microsoft.com/?id=311826

Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.