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

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,
Kim,while mentioning the fact that computer A was upgraded, you didn't say anything about the compatibility level of the database on that computer.|||Hi,

Thanks alot for your reply.. the compatibility level for SQL 2000 is 80

Kim,|||you're VERY welcome.

have you checked the execution plan for both databases?|||i'm still baby in SQL 2000, would you please tell me where can i checked the execution plan ?

Thanks,
KIM,|||open query analyzer (2 sessions) and press ctrl-k in both prior to executing the procedure. one of the panes will be called "Execution Plan." there you can see the difference, if any. also right-mouse click anywhere on the command pane of the query window, go to connection options, and set io and cpu statistics on.|||I did not have a chance to look at Execute plan and CPU stats because my boss give me a new task, he want to know whether necessary to run "Update Statistics" after run "DBCC DBreindex". Does "DBCC DBReindex" automaticlly update stats? You mind to answer this question for me... thanks alot...|||updating statistics is part of what dbreindex does, so no need to do it again.