SELECT blocking DML and DDL
-
Hi All.
I am seeing a scenario where SELECT is blocking a TRUNCATE statement in one of our prod environments. The SELECT statement runs fast in SSMS like 5-10 mins. However, from the application when it is called, it runs forever and I see ASYNC_NETWORK_IO wait. Want to know , ways to fix this blocking.
Below is the select statement.
SELECT "C_B_REF_ORG"."ROWID_OBJECT",
"C_B_REF_ORG"."DUNS_NMBR",
"C_B_REF_ORG"."CONTROL_OWN_TXT",
"C_B_REF_ORG"."GLBL_DUNS_NMBR",
"C_B_REF_ORG"."PARENT_DUNS",
"C_B_REF_ORG"."ORG_START_YEAR",
"C_B_REF_ORG"."PRMRY_US_SIC_CODE",
"C_B_REF_ORG"."IMPORT_IND",
"C_B_REF_ORG"."EXPORT_IND",
"C_B_REF_ORG"."MINORITY_IND",
"C_B_REF_ORG"."FEMALE_IND",
"C_B_REF_ORG"."INCORP_YEAR",
"C_B_REF_ORG"."SMALL_BUSINESS_IND",
"C_B_REF_ORG"."HQ_DUNS",
"C_B_REF_ORG"."DOMESTIC_DUNS",
"C_B_REF_ORG"."FAM_HIERARCHY_LVL",
"C_B_REF_ORG"."OUT_OF_BUS_IND",
"C_B_REF_ORG"."FAM_TREE_COUNT",
"C_B_REF_ORG"."OPS_STATUS_TXT",
"C_B_REF_ORG"."STANDALONE_IND",
CASE
WHEN "C_B_REF_ORG".STANDALONE_IND = 'true' THEN
'SINGLE LOCATION'
WHEN 0 <
(
SELECT Count(*)
FROM C_B_REF_ORG_FAM_ROLE
WHERE REF_ORG_ID = "C_B_REF_ORG".ROWID_OBJECT
AND Upper(FAM_ROLE_MEMBER_TXT) IN ( 'HEADQUARTERS', 'PARENT' )
) THEN
'HEADQUARTERS'
WHEN 0 <
(
SELECT Count(*)
FROM C_B_REF_ORG_FAM_ROLE
WHERE REF_ORG_ID = "C_B_REF_ORG".ROWID_OBJECT
AND Upper(FAM_ROLE_MEMBER_TXT) IN ( 'BRANCH', 'DIVISION' )
) THEN
'BRANCH'
WHEN 0 <
(
SELECT Count(*)
FROM C_B_REF_ORG_FAM_ROLE
WHERE REF_ORG_ID = "C_B_REF_ORG".ROWID_OBJECT
AND FAM_ROLE_MEMBER_TXT = 'Subsidiary'
AND Upper(FAM_ROLE_MEMBER_TXT) NOT IN ( 'HEADQUARTERS', 'PARENT', 'BRANCH', 'DIVISION' )
) THEN
'SINGLE LOCATION'
END AS LOCATION_TYP,
CASE
WHEN 0 <
(
SELECT Count(*)
FROM C_B_REF_ORG_FAM_ROLE
WHERE REF_ORG_ID = C_B_REF_ORG.ROWID_OBJECT
AND FAM_ROLE_MEMBER_TXT = 'Subsidiary'
and HUB_STATE_IND = 1
) THEN
'Subsidiary'
END AS FAM_ROLE_MEMBER_TXT,
CASE
WHEN 0 <
(
SELECT Count(*)
FROM C_B_REF_ORG_FAM_ROLE
WHERE REF_ORG_ID = C_B_REF_ORG.ROWID_OBJECT
AND FAM_ROLE_MEMBER_TXT IN ( 'Headquarters', 'Parent' )
and HUB_STATE_IND = 1
) THEN
1
ELSE
0
END AS U_IS_HQ,
"C_B_REF_ORG"."NON_MARTKETABLE_REASON_TXT"
FROM "dbo"."C_B_REF_ORG" "C_B_REF_ORG"
INNER JOIN "dbo"."DNB_INCR_REF_ORGS" "DNB_INCR_REF_ORGS"
ON "C_B_REF_ORG"."ROWID_OBJECT" = "DNB_INCR_REF_ORGS"."REF_ORG_ID"Estimated execution plan
https://www.brentozar.com/pastetheplan/?id=SJqKUhIx6
Actual execution plan
https://www.brentozar.com/pastetheplan/?id=S1hP328g6
Rowcounts
Sample output.
(59869 rows affected) and it takes 4 seconds if I execute the SELECT in SSMS.
(1 row affected)
Table structures and index info attached in below excel.
Regards,
Sam
-
--LOCKS on SELECT
<Database name="CMX_ORS">
<Locks>
<Lock request_mode="S" request_status="GRANT" request_count="1" />
</Locks>
<Objects>
<Object name="(null)">
<Locks>
<Lock resource_type="METADATA.DATABASE_PRINCIPAL" principal_name="dbo" request_mode="Sch-S" request_status="GRANT" request_count="1" />
</Locks>
</Object>
<Object name="C_B_REF_ORG" schema_name="dbo">
<Locks>
<Lock resource_type="OBJECT" request_mode="Sch-S" request_status="GRANT" request_count="1" />
</Locks>
</Object>
<Object name="C_B_REF_ORG_FAM_ROLE" schema_name="dbo">
<Locks>
<Lock resource_type="OBJECT" request_mode="Sch-S" request_status="GRANT" request_count="1" />
</Locks>
</Object>
<Object name="DNB_INCR_REF_ORGS" schema_name="dbo">
<Locks>
<Lock resource_type="OBJECT" request_mode="Sch-S" request_status="GRANT" request_count="1" />
</Locks>
</Object>
</Objects>
</Database>
---LOCKS on TRUNCATE
<Database name="CMX_ORS">
<Locks>
<Lock request_mode="S" request_status="GRANT" request_count="1" />
</Locks>
<Objects>
<Object name="DNB_INCR_REF_ORGS" schema_name="dbo">
<Locks>
<Lock resource_type="OBJECT" request_mode="Sch-M" request_status="GRANT" request_count="8" />
<Lock resource_type="OBJECT" request_mode="Sch-M" request_status="WAIT" request_count="1" />
</Locks>
</Object>
</Objects>
</Database>
--ADDITIONAL INFO OF SELECT
<additional_info>
<text_size>-1</text_size>
<language>us_english</language>
<date_format>mdy</date_format>
<date_first>7</date_first>
<quoted_identifier>ON</quoted_identifier>
<arithabort>OFF</arithabort>
<ansi_null_dflt_on>ON</ansi_null_dflt_on>
<ansi_defaults>OFF</ansi_defaults>
<ansi_warnings>ON</ansi_warnings>
<ansi_padding>ON</ansi_padding>
<ansi_nulls>ON</ansi_nulls>
<concat_null_yields_null>ON</concat_null_yields_null>
<transaction_isolation_level>ReadCommitted</transaction_isolation_level>
<lock_timeout>-1</lock_timeout>
<deadlock_priority>0</deadlock_priority>
<row_count>0</row_count>
<command_type>SELECT</command_type>
<sql_handle>0x02000000ee861c3a1e788b6ac89ad246cb23c505da28904b0000000000000000000000000000000000000000</sql_handle>
<plan_handle>0x06000b00ee861c3a60d34b62da02000001000000000000000000000000000000000000000000000000000000</plan_handle>
<statement_start_offset>0</statement_start_offset>
<statement_end_offset>4862</statement_end_offset>
<host_process_id>24025</host_process_id>
<group_id>2</group_id>
<original_login_name>CMX_ORS</original_login_name>
</additional_info>
--ADDITIONAL INFO OF TRUNCATE
<additional_info>
<text_size>-1</text_size>
<language>us_english</language>
<date_format>mdy</date_format>
<date_first>7</date_first>
<quoted_identifier>ON</quoted_identifier>
<arithabort>OFF</arithabort>
<ansi_null_dflt_on>ON</ansi_null_dflt_on>
<ansi_defaults>OFF</ansi_defaults>
<ansi_warnings>ON</ansi_warnings>
<ansi_padding>ON</ansi_padding>
<ansi_nulls>ON</ansi_nulls>
<concat_null_yields_null>ON</concat_null_yields_null>
<transaction_isolation_level>ReadCommitted</transaction_isolation_level>
<lock_timeout>-1</lock_timeout>
<deadlock_priority>0</deadlock_priority>
<row_count>0</row_count>
<command_type>TRUNCATE TABLE</command_type>
<sql_handle>0x01000b0050c57b1330d8ef821203000000000000</sql_handle>
<statement_start_offset>0</statement_start_offset>
<statement_end_offset>-1</statement_end_offset>
<host_process_id>15236</host_process_id>
<group_id>2</group_id>
<original_login_name>CMX_ORS</original_login_name>
</additional_info> -
RCSI is turned ON , on the database.
Regards,
Sam
-
A bit confusing - you state it runs quickly in 5-10 minutes (not quick in my opinion) - then later on say it takes 4 seconds. Either way, the first step is to optimize your query. You cannot truncate a table that is being used - and RCSI has no bearing on that activity. If the table(s) are being accessed - the truncate needs to wait - so the only recourse is to optimize the select(s) so they don't last as long.
Since all sub-queries are using the same base query, I would convert that to a CROSS APPLY with a single query that returns the counts.
CROSS APPLY (
SELECT HEADQUARTERS = SUM(CASE WHEN UPPER(fr.FAM_ROLE_MEMBER_TXT) IN ('HEADQUARTERS', 'PARENT') THEN 1 ELSE 0 END)
, BRANCH = SUM(CASE WHEN UPPER(fr.FAM_ROLE_MEMBER_TXT) IN ('BRANCH', 'DIVISION') THEN 1 ELSE 0 END)
, SINGLE_LOCATION = SUM(CASE WHEN fr.FAM_ROLE_MEMBER_TXT = 'Subsidiary'
AND fr.HUB_STATE_IND = 1
THEN 1
ELSE 0
END )
FROM C_B_REF_ORG_FAM_ROLE fr
WHERE fr.REF_ORG_ID = "C_B_REF_ORG".ROWID_OBJECT
) sqThis would get all counts in a single pass instead of having to query it multiple times.
I also noticed a problem with your Subsidiary counts - the first one (for location type) has a where statement that doesn't make sense. A row cannot be both 'Subsidiary' and some other value. In other words - there is no reason to check if FAM_ROLE_MEMBER_TXT = 'Subsidiary' and NOT IN some other values. In the second sub-query for 'Subsidiary' there is a check for HUB_STATE_IND - assuming that is the correct check.
With that said - it looks like you don't care about getting an actual count, just an existence check. Something like this may work better:
CROSS APPLY (
SELECT LOCATION_TYP = MIN(CASE WHEN UPPER(fr.FAM_ROLE_MEMBER_TXT) IN ('HEADQUARTERS', 'PARENT') THEN 1
CASE WHEN UPPER(fr.FAM_ROLE_MEMBER_TXT) IN ('BRANCH', 'DIVISION') THEN 2
CASE WHEN fr.FAM_ROLE_MEMBER_TXT = 'Subsidiary' AND fr.HUB_STATE_IND = 1 THEN 3
CASE WHEN "C_B_REF_ORG".STANDALONE_IND = 'true' THEN 3
ELSE 4
END )
FROM C_B_REF_ORG_FAM_ROLE fr
WHERE fr.REF_ORG_ID = "C_B_REF_ORG".ROWID_OBJECT
) sqAnd your outer query would then be:
CASE sq.LOCATION_TYP
WHEN 1 THEN 'HEADQUARTERS'
WHEN 2 THEN 'BRANCH'
WHEN 3 THEN 'SINGLE LOCATION'
END AS LOCATION_TYPAnd these:
CASE WHEN sq.LOCATION_TYP = 3 THEN 'Subsidiary' ELSE '' END AS FAM_ROLE_MEMBER_TXT
CASE WHEN sq.LOCATION_TYP = 1 THEN 1 ELSE 0 END AS U_IS_HQ
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Thanks Jeffrey. Other thing I was looking for indexing and also to remove the UPPER() function. This database is not case-sentive database. I am not sure, why they are using it.
-
most of the code looks like someone used to Oracle that was moved to work with SQL Server - in Oracle world majority of things are case sensitive - and using count(*) on these type of constructs can be nasty fast in Oracle (not soo much in older versions of SQL Server)
Even using double quotes around object names hints at people used to other DBMS
- frederico_fonseca wrote:
most of the code looks like someone used to Oracle that was moved to work with SQL Server - in Oracle world majority of things are case sensitive - and using count(*) on these type of constructs can be nasty fast in Oracle (not soo much in older versions of SQL Server)
Even using double quotes around object names hints at people used to other DBMS
Yes. its a informatica vendor specific application called MDM. Looks like the same code is being used for Oracle and SQL Implementation. I see a lot HEAP Table being used in this application like T$MT_7143178982627110744289472 , T$MT_8834444883271470391867515 etc. which then I realized, was it code first written putting Oracle as back end database.
Also, told the dev team use a specific unique column like PK column or identity non-nullable column instead of using Count(*) which will reduce the I/O or the logical reads.
I also felt Jeffrey cross apply logic very neat.
-
What are the possible solutions for "ASYNC_NETWORK_IO" wait. When I tried to execute the query its returning results in 4-5 secs. But from application it taking ages.. eventually we are killing this spid.
Looks like from SQL Server side there is no problem, it has processed the request and now waiting for the application to consume the data.
Is it a good advice to dev team to cache all the data returned by SQL and then do what ever they want in the application layer or if there are any possible solutions for this scenario ? please suggest ?
- vsamantha35 wrote:
What are the possible solutions for "ASYNC_NETWORK_IO" wait. When I tried to execute the query its returning results in 4-5 secs. But from application it taking ages.. eventually we are killing this spid.
Looks like from SQL Server side there is no problem, it has processed the request and now waiting for the application to consume the data.
Is it a good advice to dev team to cache all the data returned by SQL and then do what ever they want in the application layer or if there are any possible solutions for this scenario ? please suggest ?
What you are saying is that a query executes quickly in SSMS, but when executed through the application, it is slow? What makes you think that the wait ASYNC_NETWORK_IO is an issue?
You need to focus on ONE THING first. If you don't, you will never get to the root cause of the issue.
Start with tuning that procedure. Follow Jeffery's recommendations. See what happens. Then post the actual execution plan, and work on the indexes. If there are still issues when this is executed through the application, then you can focus on that.
How much data does this query return to the client? How is it being rendered on the screen?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ - vsamantha35 wrote:
Thanks Jeffrey. Other thing I was looking for indexing and also to remove the UPPER() function. This database is not case-sentive database. I am not sure, why they are using it.
If the database collation and column collation are not case-sensitive, then remove the UPPER since it isn't needed. As far as indexing - that should already exist since you would already have indexes on PK and FK columns.
If you don't have an index on REF_ORG_ID in the table C_B_REF_ORG_FAM_ROLE - that would definitely be one I would add.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I don't have much time this evening but I do have a question...
WHY are people trying to TRUNCATE a table that the app or other people are trying to SELECT from? That's a bit like try to tear down a wall while someone is leaning against it.
--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) -
Thank you All.
- vsamantha35 wrote:
Thank you All.
Two way street here, Sam. You've not answered my question above. Why is someone trying to Truncate a table while others are trying to SELECT from it? It makes no sense and I'd really like to know the reason.
--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) - Jeff Moden wrote:vsamantha35 wrote:
Thank you All.
Two way street here, Sam. You've not answered my question above. Why is someone trying to Truncate a table while others are trying to SELECT from it? It makes no sense and I'd really like to know the reason.
Jeff, I have asked the same to our dev team. I didn't get any response yet.. Awaiting command. They have processes developed by different dev teams and once they integrated all pieces of code, running into all kinds of blocking n high cpu issues.
Regards,
Sam
- vsamantha35 wrote:Jeff Moden wrote:vsamantha35 wrote:
Thank you All.
Two way street here, Sam. You've not answered my question above. Why is someone trying to Truncate a table while others are trying to SELECT from it? It makes no sense and I'd really like to know the reason.
Jeff, I have asked the same to our dev team. I didn't get any response yet.. Awaiting command. They have processes developed by different dev teams and once they integrated all pieces of code, running into all kinds of blocking n high cpu issues.
Regards,
Sam
Ok... thanks for trying, Sam. And totally understood on the problems you're facing right 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)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply