SQL Server 2016 Job running long

Question

SQL Server 2016 Job running long

c.bhuvaneswari

SSC Veteran

Points: 241
More actions
April 3, 2018 at 2:27 pm

#410382

Hi,
We have a job(SSIS package) running in our datawarehouse environment. It usually runs in 40 mins - 1 hour. Today it took more than 3 hours and got hung at a stored proc updating a table. The stored proc looks up for records in landing tables and updates existing records and inserts new records into staging tables.We have logging enabled in the stored proc and looking at the staging tables, I see that the update has completed but it does go past that (updating 47 records). I stopped and re- ran the job several times and it always gets hung there. The wait type for that SPID shows cxpacket and sos_scheduler_yield. Max degree of parallelism is set to 4. We have 2 processors ( 2 cores,2 logical processors). I ran a trace to find out what this spid is doing and all I could find was a session with that spid that's it. My trace couldn't capture anything and may be that is because that session is not doing anything and sitting there. We run update statistics everyday. When this job first got hung, we stopped the job ran update stats and started again. Still no luck.
I checked with Infrastructure team on how the disk is performing and they report that all is good.
I do not know what else to do. Any help is highly appreciated!
Thanks,
Bhuvi

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

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

sgmunson SSC Guru Points: 110640 More actions · Answer 1

We'd need a LOT more detail. One question I'll ask because you mentioned cxpacket is whether you have any linked servers involved in your queries? That can be problematic if a JOIN is done to a linked server table, and the volume of data in that table has experienced sudden growth, or is rather larger to begin with. However, without your queries, table details, index details, and perhaps even your execution plan, we have no idea what you are up against.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Michael Sweetman SSC Enthusiast Points: 112 More actions · Answer 2

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Evgeny Garaev SSCertifiable Points: 7428 More actions · Answer 3

Hi,

1. You should monitor operating system counters: CPU usage, memory, etc. This way you can tell if it was more resource pressure on that server or not.
2. If it wasn't resource pressure when it was locking in the database.
3. If it was resource pressure when that means something has changed: you had more data to process that day, queries have changed, statistic distribution has changed, etc.
4. If nothing has changed it might be a concurrent resource intensive process running at the exactly same time.

You can use following tools:
1. Windows performance monitor
2. Extended events
3. SQL Server profiler
4. SSMS Activity monitor

Emperor100 SSCrazy Points: 2173 More actions · Answer 4

Hi,
Reduce the Degree of parallelism setting to 2 and give it a try.

=======================================================================

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

c.bhuvaneswari - Tuesday, April 3, 2018 2:27 PM
Hi,
We have a job(SSIS package) running in our datawarehouse environment. It usually runs in 40 mins - 1 hour. Today it took more than 3 hours and got hung at a stored proc updating a table. The stored proc looks up for records in landing tables and updates existing records and inserts new records into staging tables.We have logging enabled in the stored proc and looking at the staging tables, I see that the update has completed but it does go past that (updating 47 records). I stopped and re- ran the job several times and it always gets hung there. The wait type for that SPID shows cxpacket and sos_scheduler_yield. Max degree of parallelism is set to 4. We have 2 processors ( 2 cores,2 logical processors). I ran a trace to find out what this spid is doing and all I could find was a session with that spid that's it. My trace couldn't capture anything and may be that is because that session is not doing anything and sitting there. We run update statistics everyday. When this job first got hung, we stopped the job ran update stats and started again. Still no luck.
I checked with Infrastructure team on how the disk is performing and they report that all is good.
I do not know what else to do. Any help is highly appreciated!
Thanks,
Bhuvi

When was the last time you rebuilt statistics?

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