Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Upgrade from 2008 to 2008 R2, one of scripts leads to CPU 100%, why???

Answered Upgrade from 2008 to 2008 R2, one of scripts leads to CPU 100%, why???

  • Monday, February 18, 2013 12:48 PM
     
     
    Sign In to Vote
    0

    hi gurus, 

    I just upgraded one of production servers from SQL SERVER 2008 to 2008 R2. The server exhibited increasingly high CPU culminating in all 8 CPUs being 100% utilized when SQL SERVER executed one of scripts. These scripts ran everyday successfully before upgrade, and I could not figure out why it leads to CPU 100%, anyone can help me for this? 

    The server information is :

    OS: Win Server 2003 R2  x64, CPU:8, Memory: 64GB.

    --

    Thanks, 

    Justin

     

All Replies

  • Monday, February 18, 2013 12:57 PM
     
     
    Sign In to Vote
    0
    Can you post here the execution plan of the query?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

     
  • Monday, February 18, 2013 1:00 PM
    Moderator
     
     Answered
    Sign In to Vote
    1

    Hello,

    It is recommended to defrag indexes and update statistics when you upgrade to a new version of SQL Server.

    Hope this helps

    Regards,
    Alberto Morillo
    SQLCoffee.com

     
  • Monday, February 18, 2013 6:21 PM
     
     
    Sign In to Vote
    0

    Hello,

    <> Make sure the database involved are having updated statistics with full scan:

    Exec sp_MSForEachtable 'UPDATE STATISTICS ? WITH FULLSCAN'

    GO

    <> Also if you know the queeries which are causing high CPU, try using MAXDOP query hint, see if it helps.

    <> Also confirm in performance monitor/task manager, sqlservr.exe is consuming high CPU.