SPID blocking frequently

Question

SPID blocking frequently

SQL Galaxy

One Orange Chip

Points: 29472
More actions
September 23, 2016 at 11:34 am

#317731

SPID blocking randomly & very frequently in OLTP database.
This database is AOAG (Alwayson availability groups), since last two days transaction get blocking frequently and cleared automatically after that again some SPID gets blocked..
Can you suggestion me, what are the root cause to be checking and troubleshooting?
I have create trace file in SQL Profiler for capturing SQL statement competed time which are the query took long time , but in this trace all statement completed with less than 5 seconds for all transaction.
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

I would suggest setting up an Extended Events blocked process report. Here's a blog post[/url] on how to do it. You can also query sys.dm_exec_requests when the blocking is occurring in order to see which process is causing the blocking. You can combine that DMV with others to get the query text and execution plan as well as the locks being held open.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Since it only recently started, my question would be... how often do you update 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)

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 3

Update statistics configured thru Maintenance plan with FULL SCAN on weekly basis, so it would completed around 57 min.

As suggested Mr. Grant, I have configured Extent Event with help of that URL you mentioned. Let see what process gets blocked over period of time. Thanks

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

In Extent event started session then stopped session over period of time (1 Hours), but does not captured and stored blocked process data in xel file.

I might be wrong selection in the "Capture Global Fields", here I didn't select any check options like call stack, client_name, client address etc., Does it require to select those option in Capture Global Fields? pls. confirm.

Thanks

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Since it is "very frequent" blocking, I would just use sp_whoisactive. Given AlwaysOn is in play if it is synchronous that could have an effect. No matter the cause sp_whoisactive will inform you of where the pain is.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 6

I have used WhoIs-Active version 11..

I found as below script frequently access in database from application, Pl. suggest me, How can escalate to application developer?

Wait Info: (1ms)LATCH_EX [ACCESS_METHODS_DATASET_PARENT]

CPU: 4,220

Memory: 11.841

status: suspended

<?query --

Select J.Doc_Type, J.Doc_No, J.RegnNo, J.Doc_Date, J.Doc_Dept,

PM.First_Name, PM.Last_Name, PM.Dt_Birth, PM.Sex,

Replace(IsNull(EM.MobileNo,''),'+91','') as MobileNo,

PM.CaseNo, PM.Emp_No, EM.Email_ID, RM.Description As Relation

From Patient_Master PM

Join DailyCase_Det DD ON PM.CaseNo = DD.CaseNO

Join Employee_Master EM ON EM.Emp_No = PM.Emp_No

Join Code_Master RM ON RM.Hosp_Code = 0 And RM.Type_Code = 7 And RM.Code = PM.Relation

Join Jio_Interface J ON J.Regnno = DD.RegnNo

Where J.Doc_Status <> 'Success' And J.Doc_No > 0 And IsNull(J.Doc_Tried,0) <= 5 And J.Doc_Date >= '12 Apr 2016'

--?>

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

SQL Galaxy (9/25/2016)
Pl. suggest me, How can escalate to application developer?

Ummm... ostensibly, you're the DBA on this system. You need to figure it out without having to rely on the kindness of strangers. 😉

The code appears to contain all of the necessary parameters. Turn on the actual execution plan and give it a whirl before you approach any Developer. Pay close attention to the non-SARGability of the components in the WHERE clause.

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

Looks like medical records data.

I love the smell of healthcare data in the morning. Smells like ... KAAAACCCHHHIIIIINNNNNGGGGG!!!! 😀

Seriously though, you should be able to find out who is running it, or at least the app name, with some easy extra columns on the sp_whoisactive or similar DMV queries.

Capture the query plan. I would be willing to bet there is at least one mismatched data type in there and a big scan or few. I am severely whipsawed on timezones right now, but IIRC that wait type is related to chewing on lots of data?

Bet you have the default settings still for Cost Threshold for Parallelism and MAXDOP too ...

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 9

Hi SQL GURU and Jeff,

Issues resolved by developer, he has changed and modified the existing whole query at application side. After that there is no glitch and blocking at instance and database side.

Thanks

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

SQL Galaxy (9/27/2016)
Hi SQL GURU and Jeff,
Issues resolved by developer, he has changed and modified the existing whole query at application side. After that there is no glitch and blocking at instance and database side.
Thanks

It good to see a developer that actually took things to heart. Can you post the new code that's coming across now?

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