Post reply

affinity mask value for 8 cpu heavily loaded sqlserver 2005 Ent

  • March 30, 2011 at 1:40 am

    #389172

    Hi guys

    I have migrated my SQLServer2005 Ent from Physical to VM and upgraded Virtual CPU from 4 to 8. Now the Cpu utilization of the SQLServer 2005 seems to be ok from yesterdays performance which was pathetically slow. I have done clone migration from physical to VM, we'll also need to do the fresh installation as Vmware team recommended to do Fresh installation.

    To analyze performance i have done some analysis pre & post VM migration based on the observed values I have found there is some cpu related issue after VM migration.

    Memory bottle neck which was present on the physical server has been resolved since we upgraded memory from 8 to 16 GB.

    Can any help me out what is best affinity mask value for 8 cpu heavily loaded sqlserver 2005 Ent, Cpu utilization showing almost 100 %.

    Please find enclosed Performance counter report pre & post migration.

    Regards

    Vinod Kochu

  • March 30, 2011 at 3:09 am

    #1305306

    Leave the affinity mask at default unless you really know what you are doing or are instructed so by Customer Support.

    High CPU often means you need to optimise your queries.

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 30, 2011 at 7:42 am

    #1305438

    Thanks for your reply ...

    But my quers were running fine on physical server it was not beyond 25 % on pick time. now it's showing 100 % on VM. so how can i reduce the cpu utilization. it is not possible to optimize all quers because my dev-team wouldn't allow me to do this.

  • March 30, 2011 at 7:46 am

    #1305442

    Then tell the dev team to optimise their queries

    I've seen scenarios where performance is worse on servers with more CPUs. Was because the queries were badly written and could parallel further, causing worse blocking than they could before, hence worse overall performance.

    If it's parallelism, you could try increasing the cost-threshold for parallelism, or set maxdop to say half the number of CPUs, but that's not a solution, that's hiding the symptoms. The solution is most likely to find the badly performing queries and fix them.

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 31, 2011 at 6:17 am

    #1305917

    Hi Gila,

    The same queries are running fine on Physical Server. But it is not running on VMware. Hope you have checked performance counter output too.

    The SQLServer Performance issue encounter after VM migration only.

    Thanks & Regards,

    Vinod Kochu

  • March 31, 2011 at 6:38 am

    #1305925

    I think that you would be better off comparing the execution plans for the worst performing queries between the two servers and start from there.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply