Post reply

Performance issue

  • October 14, 2012 at 3:31 pm

    #283026

    Hi, I am moving our database platform from SQL2005 to SQL2012. Some tasks on the new server are way slower then on the old server.

    I am seeing lot of CXpacket. Do you have any recommendation to try to avoid this wait process?

    Thanks

  • October 15, 2012 at 1:54 am

    #1549011

    http://blog.sqlauthority.com/2010/11/13/sql-server-reducing-cxpacket-wait-stats-for-high-transactional-database/

    http://www.mssqltips.com/sqlservertip/2027/a-closer-look-at-cxpacket-wait-type-in-sql-server/

    Like they say, you should set ‘maximum degree of parallelism’ to lower number, or check how it was configured on SQL 2005 and set the same on SQL 2012

  • October 15, 2012 at 2:37 am

    #1549019

    The CX packet wait means queries are running in parallel, nothing more. It does not automatically mean you need to reduce maxdop (and certainly not to 1)

    What you need to do is investigate the other waits that the parallel queries are getting and work on eliminating or reducing those. Also general tuning of the queries will usually help.

    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

  • October 15, 2012 at 9:22 am

    #1549173

    Thanks all.

    The main slowness is in our ETL process. This process

    -Truncate a reporting table

    -Populate the reporting table

    -Create indexes on the table

    Even the create index is slower. I will work on optimizing the population of the table. But for the creation of the indexes, is there a way to optimize it?

    Thanks

  • October 15, 2012 at 10:49 am

    #1549217

    Memory, CPU and IO subsystem throughput. That's pretty much that.

    Take a look a the IO subsystem, I won't be particularly surprised to find that's the bottleneck.

    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

  • October 15, 2012 at 11:08 am

    #1549223

    This is what I am thinking also. But our sys admin doesn't want to believe me.

    What monitor should I can use to prove this?

    Thanks,

  • October 15, 2012 at 11:25 am

    #1549226

    http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

    Start with chapter 1

    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

  • October 15, 2012 at 11:30 am

    #1549231

    Got the message.

    Thanks for your help guys. 😉

Viewing 8 posts - 1 through 7 (of 7 total)

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