Blocking query
-
Hi All,
Seeing blocking on production server. There is one particular sql statement which is causing the blocking all the time.
Please guide and on how to resolve or minimize the blocking for this statement. I always see this as a head blocker.
These sql statement is executed quite a lot.
Why it is holding exclusive lock for long time? what could be the reasons?
I used another query to check the wait info of 1284 in sysprocesses. I see below. any thoughts?
At db level maxdop is set as 4. cost threshold for parallelism =5
Can anyone explain what this mean?
Captured locking info using sp_whoisactive for all spids involved in blocking. Attached a zip file.
1284
Inner qry
<?query --
WITH LEAD_CONTACT_EMAIL_MATCH AS (SELECT C.*, REL.CHILD_PARTY_ID, BL.IN_EMAIL, BL.REQUEST_ID
FROM C_B_PARTY C with (nolock), C_B_PARTY_REL REL with (nolock),
LEAD_MATCH_REQUEST_BATCH BL with (nolock)
WHERE C.PARTY_TYP_CD = 'Contact'
AND C.BO_CLASS_CODE = 'Individual'
AND C.HUB_STATE_IND = 1
AND C.ROWID_OBJECT = REL.PARENT_PARTY_ID
AND REL.HUB_STATE_IND = 1
AND EXISTS (SELECT 1 FROM C_B_PARTY_COMM COMM with (nolock)
WHERE COMM.PARTY_ID = C.ROWID_OBJECT
AND COMM.COMM_TYP_CD = 'EMAIL'
AND COMM.HUB_STATE_IND = 1
AND COMM.COMM_VAL = BL.IN_EMAIL
)
AND NOT EXISTS (SELECT 1 FROM #ResultSet PREV_MTCH
WHERE BL.REQUEST_ID = PREV_MTCH.REQUEST_ID
)
AND BL.BATCH_ID = @batch_id
AND BL.IS_PROCESSED = 0
)
INSERT #ResultSet
select BL.REQUEST_ID, P.ROWID_OBJECT, A.SURF_ID, A.WEBSITE, P.PARTY_NM,
P.ADDR_LN1, P.ADDR_LN2, P.CITY, P.STATE_CD, --P.STATE_NM
P.COUNTRY_CD, P.POSTAL_CD, P.POSTAL_EXT_CD, P.MDM_ID, P.CMID,
P.PARTY_TYP_CD, P.CREATE_DATE, A.SALES_REP, A.TERRITORY_ID,
'ASSOCIATED_CONTACT_ACCOUNT' OUT_MATCH_REASON_CD, BL.IN_EMAIL
from C_B_PARTY P with (nolock), LEAD_CONTACT_EMAIL_MATCH BL with (nolock), C_B_ACCOUNT A with (nolock)
where P.BO_CLASS_CODE = 'Organization'
AND P.PARTY_TYP_CD in ('Customer', 'Customer - Subsidiary', 'Suspect', 'Prospect', 'Customer via Partner', 'Inactive', 'Inactive-Former Customer')
AND P.ACTV_FLG = '1'
AND P.HUB_STATE_IND = 1
AND A.HUB_STATE_IND = 1
AND A.IS_MERGED IS NULL
AND P.ROWID_OBJECT = BL.CHILD_PARTY_ID
AND P.ROWID_OBJECT = A.PARTY_ID
AND A.SP_END_CUSTOMER IS NULL
AND NOT EXISTS(select 1 from C_B_LU_SVC_PRVDR_STS STS
WHERE STS.STATUS_CD = P.SERVICE_PROVIDER_STATUS
AND STS.STATUS_CD in ('Managed Service Provider', 'Service Provider (registered)', 'Service Provider (unconfirmed)', 'MSP End Customer', 'MSP', 'Service Provider (denied/offboarded)')
)
AND NOT EXISTS (SELECT 1 FROM C_B_TERRITORY TERR with (nolock)
WHERE TERR.ROWID_OBJECT = A.TERRITORY_ID
AND TERR.GEO_KEY LIKE '%MSP%'
)
--?>
Outer qry
<?query --
(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000))DECLARE @return_value int,
@BATCH_ROWCOUNT int,
@STATUS_CODE varchar(20),
@STATUS_DESC varchar(4000)
EXEC @return_value = [dbo].[sp_crm_lead_account_matching]
@batch_id = @P0,
@tracker_id = @P1,
@isRealTime = @P2,
@isDebug = @P3,
@BATCH_ROWCOUNT = @BATCH_ROWCOUNT OUTPUT,
@STATUS_CODE = @STATUS_CODE OUTPUT,
@STATUS_DESC = @STATUS_DESC OUTPUT
SELECT @BATCH_ROWCOUNT as N'@BATCH_ROWCOUNT',
@STATUS_CODE as N'@STATUS_CODE',
@STATUS_DESC as N'@STATUS_DESC'
SELECT 'Return Value' = @return_value
--?>Regards,
Sam
-
without even knowing which CRM vendor this is I would suggest the following
- change CTFT to 50 - default of 5 is normally way wrong.
- speak with the vendor and see for the possibility of enabling RCSI on that database - for Microsoft Dynamics CRM this is a must do for example.
but your locking is typical of readers blocking writers (which RCSI "solves") - and fact that that particular query is taking more than a few (milli)seconds isn't helping - so rewrite and/or different/new indexes may be required.
on a diff note I would also rewrite the sql to stop using that old join style. - see https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins for some comments.
and as mentioned before STOP putting nolock in all your queries/tales - if you really can accept possibility of bad data use the transaction isolation level on your connection string (or on your script) set to READ UNCOMMITTED)
-
From the nature of some of the code, it looks like all parameters are being passed as NVARCHAR(4000).
It doesn't matter how well code may be written, that generally means that index seeks will be impossible because not all columns those parameters are being compared against will have a datatype of NVARCHAR(4000). It does mean that the entire index will need to be to be fully scanned because all the columns involved in the criteria will need to be scanned and converted to NVARCHAR(4000) before it can be compared and it will hold lot'so locks while doing so.
If this is 3rd party code, they need to fix it.
If it's code written by the people at your company, they really need a proverbial "come to Jesus" meeting and fix it or leave.
Even using RCSI on this isn't going to be a good fix.
--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) - frederico_fonseca wrote:
without even knowing which CRM vendor this is I would suggest the following
but your locking is typical of readers blocking writers (which RCSI "solves") - and fact that that particular query is taking more than a few (milli)seconds isn't helping - so rewrite and/or different/new indexes may be required.
RCSI is enabled.
- vsamantha35 wrote:
Can anyone explain what this mean?
ChatGPT is really good at explaining things like that. Just paste the error message into it.
-
Any thoughts on waittypes?
- Jonathan AC Roberts wrote:vsamantha35 wrote:
Can anyone explain what this mean?
ChatGPT is really good at explaining things like that. Just paste the error message into it.
This isn't an error message , its the locks column from sp_whoisactive. why Sch-S lock and what are the request_count mean? is the number of rows?
-
I think the first thing to do is to try to optimise the query to make it run faster as frederico suggested.
- vsamantha35 wrote:Jonathan AC Roberts wrote:vsamantha35 wrote:
Can anyone explain what this mean?
ChatGPT is really good at explaining things like that. Just paste the error message into it.
This isn't an error message , its the locks column from sp_whoisactive. why Sch-S lock and what are the request_count mean? is the number of rows?
Have you tried pasting it into ChatGPT? It will explain it.
-
what else is the code doing e.g. the full code of sp_crm_lead_account_matching?
Your call has other parameters and you are using output variables that are not used on the query you think is causing the issue. - that is not necessarily the case.
- Jeff Moden wrote:
From the nature of some of the code, it looks like all parameters are being passed as NVARCHAR(4000).
It doesn't matter how well code may be written, that generally means that index seeks will be impossible because not all columns those parameters are being compared against will have a datatype of NVARCHAR(4000). It does mean that the entire index will need to be to be fully scanned because all the columns involved in the criteria will need to be scanned and converted to NVARCHAR(4000) before it can be compared and it will hold lot'so locks while doing so.
So, because of the scans and datatype conversions no index will be used and it has to read all the data and that's it has to read all the data and takes long time to finish the query and release locks. Also, this is an SELECT statement, why it is taking 'X' lock on "lead_match_request_batch" table or is it somewhere at the top of the stored proc / batch, it has already done DML operation and haven't committed yet?
Regards,
Sam
-
Have you tried pasting it into ChatGPT? It will explain it.
just created a login and tried it. It gave some good layman explaination. Once thing, I wanted to ask, why this sql needs a schema stability lock? when they are typically acquired ?
Regards,
Sam
- vsamantha35 wrote:
Have you tried pasting it into ChatGPT? It will explain it.
just created a login and tried it. It gave some good layman explaination. Once thing, I wanted to ask, why this sql needs a schema stability lock? when they are typically acquired ?
Regards,
Sam
just a random example - how would you keep driving a car if half way to your destination someone removed your engine?
Schema needs to be same through out execution - otherwise another sql could drop a column and your query would go down the drain.
- Jeff Moden wrote:
From the nature of some of the code, it looks like all parameters are being passed as NVARCHAR(4000).
It doesn't matter how well code may be written, that generally means that index seeks will be impossible because not all columns those parameters are being compared against will have a datatype of NVARCHAR(4000). It does mean that the entire index will need to be to be fully scanned because all the columns involved in the criteria will need to be scanned and converted to NVARCHAR(4000) before it can be compared and it will hold lot'so locks while doing so.
If this is 3rd party code, they need to fix it.
If it's code written by the people at your company, they really need a proverbial "come to Jesus" meeting and fix it or leave.
Even using RCSI on this isn't going to be a good fix.
Since a stored proc is being called, don't the data types of the parameters in the stored proc matter more than the data types of the source parameters? That is, say, for example, proc "dbo.sp_crm_lead_account_matching" has @batch_id defined as varchar(30), and the column in the table is varchar(30), then the search should be sargable. That should be true even though the proc call has @batch_id = @P0 and @P0 is defined as nvarchar(4000), since SQL would implicitly convert @P0 to match the param type in the proc.
Of course we don't know the proc parameter types, nor the column types, but I think those are critical to properly understanding whether or not SQL can properly use indexes for the queries in the proc.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- ScottPletcher wrote:
Of course we don't know the proc parameter types, nor the column types, but I think those are critical to properly understanding whether or not SQL can properly use indexes for the queries in the proc.
Agreed.
--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 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply