Post reply

Many individual inserts hitting one CPU core

  • December 15, 2015 at 4:52 am

    #303293

    Hi all,

    I have an issue that is confusing me. I have a .NET application that generates an insert per row to build a data access table, we'll call it DataAccess (I know it's horrid). The batch that's sent over from the application contains a delete from DataAccess for one group of rules, then a series of insert statements, one per row to insert the newly generated data access rules for that group.

    DataAccess has two columns, GroupID(int) and CaseID(char 8). These columns make up the clustered index of the table.

    The server is quite meaty and has 128GB RAM which is enough to hold the databases in question in their entirety and 16 cores to use. The disk is poor but I am pretty confident we can rule this out in this instance.

    So to recap, the .NET application selects the most recent access rules for a group, generates a delete for that group from DataAccess and an insert per CaseID for that group as one SQL Batch and sends it over to process.

    My issue is that this operation used to take around 30 minutes but is now taking 15+ hours. There have been no significant changes in the amount of cases, rules or groups. What I have noticed is that I have a lot of SOS_SCHEDULER_YIELD waits in a 30 second sample (over 4k) but no other waits that seem significant. When checking the resource monitor, I see, as I expected, one core at 100% utilisation and the other 15 cores putting their feet up.

    Over the course of a couple of a few days, I have tried differing server settings to try and get these operations running parallel but with no success. I have rebuilt statistics and rebuilt indexes in an attempt to get a different plan generated but I'm running out of ideas and wondered if anyone could provide me a new avenue to explore. The issue seems obvious to me but perhaps I'm mis-diagnosing.

    I do know how this is being achieved is terribly inefficient but at this point, modifying the method isn't an option and despite this inefficiency it did, until recently, complete in an acceptable time frame for the business.

    Any suggestions would be greatly appreciated.

    Thank you,

    Gary.

  • December 22, 2015 at 12:54 pm

    #1847565

    What does the execution plan show?

  • December 22, 2015 at 3:49 pm

    #1847608

    I know it's going to sound crazy but it sounds like a network cable, NIC, or Switch/Hub port may have gone bad. It could also be that some genius changed the settings to "auto-negotiate" instead of leaving the setting hardcoded.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

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