August 4, 2020 at 8:14 am
Hi Experts,
we have observed that the SELECT statements are causing from application which is using LINQ from different SPID. When traced the transactions we observed that many SELECT statements are created with same SPID.
The developer modified the query to use the READONLY in .net code in QA environment as fix , now when we did a load test with 5 users, we have observed multiple select statements inside same SPID but not causing any blocking.
What might be the cause or I am looking at the wrong side?
<blocked-process-report monitorLoop="1395896">
<blocked-process>
<process id="process3f22908c8" taskpriority="0" logused="0" waitresource="KEY: 22:72057594062372864 (2540814a0d00)" waittime="6545" ownerId="3442125886" transactionname="user_transaction" lasttranstarted="2020-07-27T17:51:38.077" XDES="0x849d06860" lockMode="S" schedulerid="9" kpid="22340" status="suspended" spid="184" sbid="2" ecid="0" priority="0" trancount="1" lastbatchstarted="2020-07-27T17:51:38.150" lastbatchcompleted="2020-07-27T17:51:37.967" lastattention="1900-01-01T00:00:00.967" clientapp="Core .Net SqlClient Data Provider" hostname="TEXASDC01-XRX" hostpid="1477404160" loginname="XRM200" isolationlevel="read committed (2)" xactid="3442125886" currentdb="22" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame line="1" stmtstart="72" stmtend="858" sqlhandle="0x02000000bd314d340780710d0cfc583f5ae4036c247f166a0000000000000000000000000000000000000000" />
<frame line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000" />
</executionStack>
<inputbuf>
(@__rotationNumber_0 nvarchar(4000))SELECT [x].[ACTIVITY], [x].[PORT_CODE] AS [Port], [x.Template].[NAME] AS [ServiceName], [x].[ID] AS [ServiceRequestID], [x].[CURRENT_STATUS] AS [Status], [x.Template].[RESOURCE_TYPES] AS [ResourceType]
FROM [TAB1] AS [x]
LEFT JOIN [TAB2] AS [x.Template] ON [x].[TEMPLATE_ID] = [x.Template].[ID]
WHERE [x].[ROTATION_NUMBER] = @__rotationNumber_0
ORDER BY [x].[CREATED_DATE] DESC </inputbuf>
</process>
</blocked-process>
<blocking-process>
<process status="sleeping" spid="135" sbid="2" ecid="0" priority="0" trancount="1" lastbatchstarted="2020-07-27T17:51:44.453" lastbatchcompleted="2020-07-27T17:51:44.453" lastattention="1900-01-01T00:00:00.453" clientapp="Core .Net SqlClient Data Provider" hostname="TEXASDC01-XRX" hostpid="1477404160" isolationlevel="read committed (2)" xactid="3442125731" currentdb="22" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack />
<inputbuf>
(@__service_ID_0 nvarchar(450))SELECT [x].[ID], [x].[STATUS], [x].[RESOURCE_ID]
FROM [TAB3] AS [x]
WHERE ([x].[SERVICE_REQUEST_ID] = @__service_ID_0) AND ([x].[ACTIVE] = 1) </inputbuf>
</process>
</blocking-process>
</blocked-process-report>
August 4, 2020 at 11:35 am
You've identified some queries, but now you need to investigate their behavior within the system. The way to do this is to look at the execution plan in order to understand how those queries are being resolved based on your structures.
The blocking query in the report above doesn't have any obvious problems. It's a query against a single table. However, what kind of indexes are on the table? Are they ones that a WHERE clause against SERVICE_REQUEST_ID and ACTIVE will provide meaningful filtering? There's no way to know based on what you have here. Get the execution plans and you'll know more.
Blocking occurs when a query runs long. So, figure out how to make the query run faster. Usually, it's changes to code. Sometimes, it's changes to data structures. Rarely, it might be a system configuration issue. However, usually, 99%, its' the code or the data structure.
"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
August 4, 2020 at 12:50 pm
You've identified some queries, but now you need to investigate their behavior within the system. The way to do this is to look at the execution plan in order to understand how those queries are being resolved based on your structures.
The blocking query in the report above doesn't have any obvious problems. It's a query against a single table. However, what kind of indexes are on the table? Are they ones that a WHERE clause against SERVICE_REQUEST_ID and ACTIVE will provide meaningful filtering? There's no way to know based on what you have here. Get the execution plans and you'll know more.
Blocking occurs when a query runs long. So, figure out how to make the query run faster. Usually, it's changes to code. Sometimes, it's changes to data structures. Rarely, it might be a system configuration issue. However, usually, 99%, its' the code or the data structure.
Thanks Grant. The indexes were created on the columns in WHERE clause. The strange part is Blocked and Blocker queries have different tables.
August 4, 2020 at 1:56 pm
Are they part of larger batches? There could be multiple statements involved.
"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
August 5, 2020 at 7:59 am
Are they part of larger batches? There could be multiple statements involved.
Yes Grant that what we found when ran the profiler trace many transaction with same spid but after developers using READONLY in linq queries and doing a load test we didnt get a blocking. The transactions are still in same SPID which is expected right? Will the transactionID in profiler will help differentiate the transactions are all isolated?
August 5, 2020 at 6:12 pm
I'm not sure I understand the question. Yes, the SPID above should match any SPID you capture using Profiler or Extended Events.
"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
August 5, 2020 at 10:32 pm
I cannot see how <blocking-process> stands on the way of <blocked-process>.
Both queries apply shared locks, so, they should not block each other.
Can you find what is waitresource in the 1st query?
Here's how:
https://littlekendra.com/2016/10/17/decoding-key-and-page-waitresource-for-deadlocks-and-blocking/
_____________
Code for TallyGenerator
August 10, 2020 at 4:51 am
I cannot see how <blocking-process> stands on the way of <blocked-process>.
Both queries apply shared locks, so, they should not block each other.
Can you find what is waitresource in the 1st query?
Here's how:
https://littlekendra.com/2016/10/17/decoding-key-and-page-waitresource-for-deadlocks-and-blocking/
Thanks Sergiy. Will check and update you.
August 10, 2020 at 5:23 am
I cannot see how <blocking-process> stands on the way of <blocked-process>.
Both queries apply shared locks, so, they should not block each other.
Can you find what is waitresource in the 1st query?
Here's how:
https://littlekendra.com/2016/10/17/decoding-key-and-page-waitresource-for-deadlocks-and-blocking/
This is another blocking observed
--Head Blocker--
(@__rotationNumber_0 nvarchar(4000))SELECT TOP(1) [x].[ID], [x].[ATA], [x].[ATD], [x].[BERTH_NUMBER],
[x].[BILLING_AGENT_CODE], [x].[BILLING_AGENT_NAME], [x].[CLOSURE_BY_ATD], [x].[CREATED_BY], [x].[CREATED_DATE],
[x].[DEFAULT_SERVICES_CREATED], [x].[ETA], [x].[ETD], [x].[INBOUND_VOYAGE_NUMBER], [x].[OFFICIAL_NUMBER],
[x].[PORT_CODE], [x].[PORT_NAME], [x].[ROTATION_NUMBER], [x].[SHIFTING_DEFAULT_SERVICES_CREATED]
, [x].[SHIPPING_AGENT_CODE], [x].[SHIPPING_AGENT_NAME], [x].[SHIPPING_LINE_NAME], [x].[TERMINAL_CODE],
[x].[TERMINAL_NAME], [x].[UPDATED_BY], [x].[UPDATED_DATE], [x].[VESSEL_CALL_APPROVED],
[x].[VESSEL_CALL_SIGN], [x].[VESSEL_FLAG], [x].[VESSEL_FLAG_NAME], [x].[VESSEL_IMO], [x].[VESSEL_NAME]
FROM Table_1 AS [x]
WHERE [x].[ROTATION_NUMBER] = @__rotationNumber_0
--Blocked Process
WaitType WaitDescription LastWaitType
LCK_M_S keylock hobtid=72057594062176256 dbid=22 id=lock743043d80 mode=X associatedObjectId=72057594062176256 LCK_M_S
(@__templateType_0 nvarchar(4000),@__rotationNumber_1 nvarchar(4000))SELECT [e].[CURRENT_STATUS] AS [StatusID],
[e].[ACTIVITY], COUNT(*) AS [Count]
FROM Table_2 AS [e]
WHERE ([e].[TEMPLATE_TYPE] = @__templateType_0) AND ([e].[ROTATION_NUMBER] = @__rotationNumber_1)
GROUP BY [e].[CURRENT_STATUS], [e].[ACTIVITY]
hobtid is pointing to below non clustered index.
hobtid=72057594062176256 =RK_NCIX_REQUEST_templatetypeRotationumberCurrentstatus_Activity
August 10, 2020 at 8:05 am
Is it a part of a transaction opened somewhere in the application code?
a transaction which includes UPDATE and/or DELETE statements in line with that SELECT.
Because you have an "X" lock, and a SELECT statement would not invoke this kind of locks.
_____________
Code for TallyGenerator
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply