SLOW ETL only on weekends

Question

SLOW ETL only on weekends

sqllearner44

SSC Veteran

Points: 276
More actions
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

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

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

Jeff Moden SSC Guru Points: 1003895 More actions · Answer 1

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)

sqllearner44 SSC Veteran Points: 276 More actions · Answer 2

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

sqllearner44 SSC Veteran Points: 276 More actions · Answer 3

Hi Jeff,

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

Regards,

SQLLearner

Jeff Moden SSC Guru Points: 1003895 More actions · Answer 4

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)

Jeff Moden SSC Guru Points: 1003895 More actions · Answer 5

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)