Timeslicing job causes the DB Lock

[Shalinee Chauhan]

Hi,

We are experiencing issue in Out of the box Time Slicing job. In our setup this jobs runs on every minute. The job is working fine on most of the time however we have noticed mostly on month end or sometimes on timesheet weekending(which Thursday in our setup) day at 12 AM it shows time slicing job instance is processing and never finished after 12 AM until I cancel the timeslicing jobs manually.

 

This also  causes ks on INV_INVESTMENTS table and multiple other related tables. Later it impacts to all end users and they can not perform any UML operation on INV_Investment tables in Clarity Application .

Example:Create, Save and Submit will not work on any of the investment however resource update, timesheets and other features are working.

 

The is happening since many months and we are in unable to re-produce the same behavior in our Dev, QA instance. Our production is currently on old version 12.1.3 with MS SQL Server 2008. WE have cluster environment and using multi-casting and F5 Hardware load balancing and SSO setup.

 

Our Dev and QA instances are on CA PPM 14.2 SP 11 and MS SQL 2008. Our production go live is soon in coming weeks. We did not see any error in logs at that time.

Please see the definition of timeslice setup in our instance...

 

 

Any suggestion is much appreciated!

 

Thanks,

Shal.

[Suman Pramanik]

Hi Shal,

 

Can you share the Job incompatibilities of timeslice ? Also can the DBA share what query is causing the dead lock

 

Regards

Suman Pramanik 

[Shalinee Chauhan]

Thanks Suman,

 

Here is the details of incompatible jobs.

 

(Embedded image moved to file: pic15249.gif)

[Suman Pramanik]

The incompatible section looks good, if there are any update happening at the investment table you probably may need to check the process and if you have some direct database updates via process.

 

Also see if this document helps you Configuring and Scheduling some of the main jobs 

 

Regards

Suman Pramanik 

[Shalinee Chauhan]

Jernin/Suman,

 

As I mentioned earlier too in our environment time slicing job is creating

the DB locks only on 12 AM of 1st of each month. That is the time when

month roll over happened. Occasionally it does the same behavior on 1st day

of the week.

 

Today Again, we notice the time slice job is trying to complete the

instance started at 11:59 PM on 4/30/207 and it was not completed until at

5/1/2017 6:40 and later I cancel the process instance. Then I checked the

status of tim,e slice in studio side and it looks like this. Also I ran the

time-slice status query and it returned below result.

 

(Embedded image moved to file: pic19331.gif)

(Embedded image moved to file: pic38122.gif)

(Embedded image moved to file: pic31913.gif)

 

 

Result of time-slice query status:

(Embedded image moved to file: pic25797.gif)

 

 

 

I also ran the below command  to find the status of Block ID.

 

 

 

(Embedded image moved to file: pic20124.gif)

 

 

 

 

DBCC INPUTBUFFER(processid)

 

SPID 54 is the root blocker. Below are the details. I have killed it.

 

(Embedded image moved to file: pic22519.jpg)

 

(@P1 bigint)update inv_investments set odf_ss_labor_variance = @P1  where

odf_ss_labor_variance is null

 

 

 

Please let me know if you see any issue.

 

Thanks

Sal.

[Jerin Sebastian]

Hi Shalinee,

Time Slicing & Datamart Extraction getting into a deadlock is a common issue I have seen with SQL Server especially when you have customizations around slice tables. 

In all these occurrences of the issue, were you able to figure out the query behind the deadlock with Time Slicing? I assume you have checked the logs and found blobcrack deadlock errors. 

The next time when you hit this issue, please run the below query to find the list of processes running in the DB

 

sp_who2 'active'

 

The column blockedby will give you the processes which are blocked and who is blocking it. 

 

DBCC INPUTBUFFER(processid) --This will give the query for the process id. Mention the process id which are blocked and which is blocking it. Once we get the query, we should be able to figure out which job/process is causing this the deadlock.. 

 

If you don't have customizations around slice tables, then as Suman mentioned, it could be the job incompatibilities not set correctly causing this issue....

 

Thanks,

Jerin 

[Shalinee Chauhan]

Thanks Jerin for sharing all the details.

 

We do not have any customization on time-slicing tables. Here is the result

of last time deadlock results via sp_who2

 

(Embedded image moved to file: pic21756.gif)

 

 

DBA mentioned that the locks created by an update on inv_investment table.

I do not have exact message. Next time, I will definitely will look into

it.

Also, I did not see any error in any of the logs on servers at same time

related to"blobcrack deadlock ".

[Jerin Sebastian]

H Shalinee,

A single update to investments should not end up with a deadlock so either there is some xog activity happening which is doing a bulk update of investments or a process which has an update query in it. I would give more emphasis on the latter as Time Slicing is usually capable of handling XOG requests. 

Also, if you hit this issue next time, please have DBA or use the query I gave above to find the complete query of both the blockedby session and the session which is blocked. 

 

Thanks,

Jerin

[Shalinee Chauhan]

Jernin/Suman,

 

As I mentioned earlier too in our environment time slicing job is creating

the DB locks only on 12 AM of 1st of each month. That is the time when

month roll over happened. Occasionally it does the same behavior on 1st day

of the week.

 

Today Again, we notice the time slice job is trying to complete the

instance started at 11:59 PM on 4/30/207 and it was not completed until at

5/1/2017 6:40 and later I cancel the process instance. Then I checked the

status of tim,e slice in studio side and it looks like this. Also I ran the

time-slice status query and it returned below result.

 

(Embedded image moved to file: pic19331.gif)

(Embedded image moved to file: pic38122.gif)

(Embedded image moved to file: pic31913.gif)

 

 

Result of time-slice query status:

(Embedded image moved to file: pic25797.gif)

 

 

 

I also ran the below command  to find the status of Block ID.

 

 

 

(Embedded image moved to file: pic20124.gif)

 

 

 

 

DBCC INPUTBUFFER(processid)

 

SPID 54 is the root blocker. Below are the details. I have killed it.

 

(Embedded image moved to file: pic22519.jpg)

 

(@P1 bigint)update inv_investments set odf_ss_labor_variance = @P1  where

odf_ss_labor_variance is null

 

 

 

Please let me know if you see any issue.

 

Thanks

Sal.

[Jerin Sebastian]

Hi Sal,

Thank you for the screenshots which helps in understanding the problem better. 

 We have got only one query(54) which blocked SPID100. Can you please get the query for SPID 100 - since there was a deadlock this should be recorded in the DB error logs and DBA should be able to provide you. 

Also I would request you to please check

- the fragmentation on the inv_investments table

- are you running the reorg/reindexing on the DB as per the best prac

- enough disk and IO space on DB

- any processes/jobs which were running around 12AM. Ideally once we get the query for SPID 100, we should be able to say what it was running. 

 

Thanks,

Jerin

[Shalinee Chauhan]

Hi Jerin,

 

Please see comments received from DBA..

 

 

Can you please get the query for SPID 100 - since there was a deadlock this should be recorded in the DB error logs and DBA should be able to provide you. (It was not a deadlock. It was only regular blocking)
Also I would request you to please check
- the fragmentation on the inv_investments table Please see the attached excel sheet for the fragmentation details
- are you running the reorg/reindexing on the DB as per the best prac We have a weekly job that runs once every Sunday.
- enough disk and IO space on DB. We have good amount space available for Data files(335 GB free) and Log files(481 GB Free)
- any processes/jobs which were running around 12AM. Ideally once we get the query for SPID 100, we should be able to say what it was running.

 

Thanks 

Shal

Attachment(s)

HNA_Prod_Fragmentation_Details.xlsx   22 KB 1 version

[Jerin Sebastian]

Hi Shal,

Can you please check with DBA if there is a way to get the query running behind SPID100? 

I think that's the way to go forward - to identify both the queries - blockedby and blocking. 

 

Thanks,

Jerin

[Shalinee Chauhan]

Hi Jerin,

 

Thanks for your help. DBA is not able to see the query for SPID 100 and other. I have to wait until next DB Deadlock occur and will try to get most of the facts.

 

Thanks,

Shal.

[Jerin Sebastian]

Hi Shal,

Yes, lets get all the details possible and also check with DBA if they can run a SQL profiler as well. 

Get the queries of all the blocked sessions as well. 

 

Thanks,

Jerin

[Shalinee Chauhan]

Thanks Jerin for sharing all the detailed information.

I will keep posted whenever the next DB lock will occur.

 

Thanks,

Shal.