Post reply

SLOW ETL only on weekends

  • February 22, 2016 at 4:01 am

    #324440

    Hi,

    I am having slow runs of ETLs that otherwise run for 15 minutes, taking close to two hours on weekends.

    I have ensured nothing else such as db maintenance is running during the times of delayed runs. The job spids are of the type CXpacket, Latch_EX. I did look at the options for changing the MAXDOP or keeping my indices least fragmented. However, that doesn't give me an answer as to why just the weekends are troublesome.

    I am suspecting I/O issues on the server. Some windows tasks using the disks may be?

    I also see frequent messages as below in the error logs.

    "last target outstanding: 5152, avgWriteLatency 13

    average writes per second: 37.49 writes/sec

    average throughput: 0.41 MB/sec, I/O saturation: 5368, context switches 9026

    FlushCache: cleaned up 5542 bufs with 3967 writes in 105820 ms (avoided 737 new dirty bufs) for db 9:0."

    Any inputs on this can serve helpful.

    Regards,

    SQL Learner

  • February 22, 2016 at 5:37 pm

    #1860241

    The weekends is typically when someone does the really heavy lifting such as making full backups at the machine level, running BeRemote to back up disk directories to tape, running defrags and other maintenance, doing FULL backups (a lot of people only do it once a week), etc, ad infinitum. Open up PerfMon or even Resource Monitor and see what else is running. If nothing else, it might just be a full "pipe".

    Another thought is that someone may have something that "turns down" the CPU's and other devices on the weekends in an attempt to save a few pennies on power and cooling.

    --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)

  • February 23, 2016 at 4:02 am

    #1860327

    Hi Jeff,

    From the DB end, I have confirmed i stop all the maintenance jobs just for this week to see if there is a better run. As in, no full backups, no index maintenance or any other maintenance done on weekends.

    Most of the waits though are CXpacket waits which dont appear other times for the same query. CXpacket is usually associated with MAXDOP. Our processor details: 2 Numa nodes, 12 processors each, so ideally a MAXDOP of 8 is preferred per M/S. I plan to change MAXDOP from the existing 0 to 8. But that still doesnt mean thats the cause for slower weekend runs .

    I will log the perfmon and verify things.

    Also, this being prod server, I doubt anyone would fiddle with the servers.

    Regards,

    SQL Learner

  • February 23, 2016 at 4:22 am

    #1860331

    Hi Jeff,

    Is there a way i can notice things if they are turned down?

    Regards,

    SQLLearner

  • February 23, 2016 at 7:59 am

    #1860412

    sqllearner44 (2/23/2016)

    Hi Jeff,

    From the DB end, I have confirmed i stop all the maintenance jobs just for this week to see if there is a better run. As in, no full backups, no index maintenance or any other maintenance done on weekends.

    Most of the waits though are CXpacket waits which dont appear other times for the same query. CXpacket is usually associated with MAXDOP. Our processor details: 2 Numa nodes, 12 processors each, so ideally a MAXDOP of 8 is preferred per M/S. I plan to change MAXDOP from the existing 0 to 8. But that still doesnt mean thats the cause for slower weekend runs .

    I will log the perfmon and verify things.

    Also, this being prod server, I doubt anyone would fiddle with the servers.

    Regards,

    SQL Learner

    My recommendation would be to NEVER turn off backups.

    --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)

  • February 23, 2016 at 8:01 am

    #1860413

    sqllearner44 (2/23/2016)

    Hi Jeff,

    Is there a way i can notice things if they are turned down?

    Regards,

    SQLLearner

    Yes... and it can be done from SQL Server if you're allowed to use xp_CmdShell. If not, you'll have to do it from a Command Prompt session. I have to find the code, though, and it's at home.

    --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 6 posts - 1 through 5 (of 5 total)

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