Post reply

CXPACKET and ASYNC_NETWORK_IO waits

  • July 25, 2010 at 1:54 am

    #224618

    Hi,

    We have Share Point 2007 databases in SQL Server 2005 EE x64 with SP3. We have two quad core processors (8 processors) and MaxDop is set to default.

    CXPACKET and ASYNC_NETWORK_IO waits are happening continuously for the Content database.

    ASYNC_NETWORK_IO waits is happening but it's wait time is <200 ms

    So is this <200 ms wait time for ASYNC_NETWORK_IO wait could be considered as an issue?

    We are experiencing performance degradation for Share point application while retrieving the documents

    Thanks

  • July 26, 2010 at 12:24 am

    #1198018

    could you please advice me..

    thanks

  • July 26, 2010 at 12:30 am

    #1198019

    First u can set the MDOP to the no. of processors ie 8.

    check IO on the drive where database files exists....using perfmon for disk counters like avg disk q length, disk sec transfer..etc..

  • July 26, 2010 at 5:14 am

    #1198114

    CXPACKET: You have either too little ram, too poor an IO configuration or both. Drop MAXDOP to half the cores (non-hyperthreaded here) and try that.

    For network packets, quite a few potential causes here, but most usual is simply that the client's aren't consuming data fast enough. Usually not a problem to worry with, or at least one you can do anything about.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • July 26, 2010 at 11:07 am

    #1198314

    Thanks SQLGuru,

    CXPACKET: You have either too little ram, too poor an IO configuration or both. Drop MAXDOP to half the cores (non-hyperthreaded here) and try that.

    For network packets, quite a few potential causes here, but most usual is simply that the client's aren't consuming data fast enough. Usually not a problem to worry with, or at least one you can do anything about

    question:

    When setting up MaxDop value, what should be considered from the below:

    MaxDop value = half the value of no.of Physical CPU's (1/2 * 2 (two quad core CPU's))?

    MaxDop value = half the value of no.of total core processors (1/2* 8 (no.of total core processors))

    thanks

  • July 28, 2010 at 3:24 pm

    #1199515

    Could you please advice me

    Thank you

  • July 28, 2010 at 6:22 pm

    #1199558

    If you have 2xQuad core processors, then you have 8 cores, and should see 8 "CPUs" in Task Manager. (If you are hyper-threading, you'll see more.) Kevin's suggestion would therefore be to drop MAXDOP down to 4. You can either set this at a server-level, or specifically on the query that you're seeing the problem on.

    An alternative, more aggressive test would be to drop MAXDOP down to 1 for this individual query. This may, or may not, perform better than a MAXDOP of 4. You'll have to experiment a little to find the best option for this one query, but a server-wide value of 1 may not be appropriate.

  • July 29, 2010 at 12:35 am

    #1199628

    Thank you,

    As it's an instance hosting Share point database, I think it's NOT possible to mention MaxDop value in individual queries. So I have only one option of setting server wide MaxDop value.

    I will try with setting MaxDop value to 4 and then see CXPACKET wait goes away. Thanks for your advice.

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

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