Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2003
    Location
    Sydney
    Posts
    6

    Unanswered: 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,

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    while mentioning the fact that computer A was upgraded, you didn't say anything about the compatibility level of the database on that computer.

  3. #3
    Join Date
    Feb 2003
    Location
    Sydney
    Posts
    6
    Hi,

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

    Kim,

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    you're VERY welcome.

    have you checked the execution plan for both databases?

  5. #5
    Join Date
    Feb 2003
    Location
    Sydney
    Posts
    6
    i'm still baby in SQL 2000, would you please tell me where can i checked the execution plan ?

    Thanks,
    KIM,

  6. #6
    Join Date
    Sep 2003
    Posts
    522
    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.

  7. #7
    Join Date
    Feb 2003
    Location
    Sydney
    Posts
    6
    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...

  8. #8
    Join Date
    Sep 2003
    Posts
    522
    updating statistics is part of what dbreindex does, so no need to do it again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •