SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SELECT query is blocking UPDATE query in SQL Server 2016


SELECT query is blocking UPDATE query in SQL Server 2016

Author
Message
gary1
gary1
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17652 Visits: 2354
Hi,
I'm running a data warehouse load and a select statement from table DBO"."DIM_BILL_INV_ITEM" is blocking the UPDATE query forever on the same table. Please advise how can I resolve this blocking issue?

Select Query:

(@P1 int) SELECT "TCRdr_1"."BILL_INV_ITEM_DID" , "TCRdr_1"."BILL_INV_ITEM_KEY" , "TCRdr_1"."ETL_ROW_EFF_DTS" , "TCRdr_1"."ETL_ROW_EXP_DTS" ,
"TCRdr_1"."ROW_PROC_DTS" , "TCRdr_1"."SOURCE_SYSTEM" , "TCRdr_1"."BILL_INV_KEY" , "TCRdr_1"."CHRG_KEY" , "TCRdr_1"."BILL_POL_PRD_KEY" ,
"TCRdr_1"."EVENT_DTS" , "TCRdr_1"."PMT_EXCEPT_DTS" , "TCRdr_1"."PROMISE_EXCEPT_DTS" , "TCRdr_1"."INSTALLMENT_NO" , "TCRdr_1"."LINE_ITEM_NO" ,
"TCRdr_1"."INV_ITEM_TYPE_CD" , "TCRdr_1"."INV_ITEM_TYPE_CCD" , "TCRdr_1"."INV_ITEM_TYPE_CST" , "TCRdr_1"."INV_ITEM_TYPE_CLT" , "TCRdr_1"."CURR_CD" ,
"TCRdr_1"."INV_ITEM_AMT" , "TCRdr_1"."COMMENTS" , "TCRdr_1"."INV_ITEM_TEXT" , "TCRdr_1"."CSTM_PMT_GROUP_TEXT" , "TCRdr_1"."EXCEPT_CMT" ,
"TCRdr_1"."GROSS_SETTLED_FL" , "TCRdr_1"."RETIRED_FL" , "TCRdr_1"."REVERSED_FL" , "TCRdr_1"."ETL_CURR_ROW_FL" , "TCRdr_1"."ETL_LATE_ARRIVING_FL" ,
"TCRdr_1"."ETL_ACTIVE_FL" , "TCRdr_1"."ETL_ADD_DTS" , "TCRdr_1"."ETL_LAST_UPDATE_DTS"
FROM "DBO"."DIM_BILL_INV_ITEM" "TCRdr_1"
WHERE ( "TCRdr_1"."BILL_INV_ITEM_DID" >= @P1 ) ORDER BY "TCRdr_1"."BILL_INV_ITEM_DID" ASC
_____________________________________
UPDATE query:
UPDATE "DBO"."DIM_BILL_INV_ITEM"
SET "BILL_INV_ITEM_KEY" = @P1, "ETL_ROW_EFF_DTS" = @P2, "ETL_ROW_EXP_DTS" = @P3, "ROW_PROC_DTS" = @P4,
"SOURCE_SYSTEM" = @P5, "BILL_INV_KEY" = @P6, "CHRG_KEY" = @P7, "BILL_POL_PRD_KEY" = @P8, "EVENT_DTS" = @P9,
"PMT_EXCEPT_DTS" = @P10, "PROMISE_EXCEPT_DTS" = @P11, "INSTALLMENT_NO" = @P12, "LINE_ITEM_NO" = @P13, "INV_ITEM_TYPE_CD" = @P14,
"INV_ITEM_TYPE_CCD" = @P15, "INV_ITEM_TYPE_CST" = @P16, "INV_ITEM_TYPE_CLT" = @P17, "CURR_CD" = @P18, "INV_ITEM_AMT" = @P19,
"COMMENTS" = @P20, "INV_ITEM_TEXT" = @P21, "CSTM_PMT_GROUP_TEXT" = @P22, "EXCEPT_CMT" = @P23, "GROSS_SETTLED_FL" = @P24,
"RETIRED_FL" = @P25, "REVERSED_FL" = @P26, "ETL_CURR_ROW_FL" = @P27, "ETL_LATE_ARRIVING_FL" = @P28, "ETL_ACTIVE_FL" = @P29,
"ETL_ADD_DTS" = @P30, "ETL_LAST_UPDATE_DTS" = @P31 WHERE "BILL_INV_ITEM_DID" = @P32
gary1
gary1
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17652 Visits: 2354
Session locks for SELECT query:
Lock Type Mode Status Database Count Index Object Name
DATABASE S GRANT EDW_DEV 1
OBJECT S GRANT EDW_DEV 1 DIM_BILL_INV_ITEM

Session locks for Update query:
Lock Type Mode Status Database Count Index Object Name
DATABASE S GRANT EDW_DEV 1
OBJECT IX WAIT EDW_DEV 1 DIM_BILL_INV_ITEM

Thanks
gary1
gary1
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17652 Visits: 2354
I have used sp_WhoIsActive to find more info on blocking. Can you please advise what I can do to fix this blocking issue.
EXEC sp_WhoIsActive @get_task_info = 2, @get_additional_info = 1
--SPID 285 select query
<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>ON</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>
</additional_info>

***********************************************************
--spid 251 update query
<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>ON</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>1</row_count>
<command_type>UPDATE</command_type>
<block_info>
<lock_type>objectlock</lock_type>
<database_name>EDW_DEV</database_name>
<object_id>1540669032</object_id>
<schema_name>dbo</schema_name>
<object_name>DIM_BILL_INV_ITEM</object_name>
</block_info>
</additional_info>
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)

Group: General Forum Members
Points: 142884 Visits: 22077
Can you post the DDL (create table) script for the table DIM_BILL_INV_ITEM including triggers and indices please?
Cool
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (198K reputation)SSC Guru (198K reputation)SSC Guru (198K reputation)SSC Guru (198K reputation)SSC Guru (198K reputation)SSC Guru (198K reputation)SSC Guru (198K reputation)SSC Guru (198K reputation)

Group: General Forum Members
Points: 198096 Visits: 18538
gary1 - Wednesday, May 2, 2018 6:23 PM
Session locks for SELECT query:
Lock Type Mode Status Database Count Index Object Name
DATABASE S GRANT EDW_DEV 1
OBJECT S GRANT EDW_DEV 1 DIM_BILL_INV_ITEM

Session locks for Update query:
Lock Type Mode Status Database Count Index Object Name
DATABASE S GRANT EDW_DEV 1
OBJECT IX WAIT EDW_DEV 1 DIM_BILL_INV_ITEM

Thanks


well the easy option but probably not the most favoured, is enable row versioning for the reads.
Need full details first as requested above

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
gary1
gary1
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17652 Visits: 2354
Thanks,

Here is the DDL script for the table DIM_BILL_INV_ITEM. Please advise.

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DIM_BILL_INV_ITEM](
[BILL_INV_ITEM_DID] [int] NOT NULL,
[BILL_INV_ITEM_KEY] [varchar](100) NOT NULL,
[ETL_ROW_EFF_DTS] [datetime2](7) NOT NULL,
[ETL_ROW_EXP_DTS] [datetime2](7) NOT NULL,
[ROW_PROC_DTS] [datetime2](7) NOT NULL,
[SOURCE_SYSTEM] [varchar](10) NOT NULL,
[BILL_INV_KEY] [varchar](100) NOT NULL,
[CHRG_KEY] [varchar](100) NOT NULL,
[BILL_POL_PRD_KEY] [varchar](100) NOT NULL,
[EVENT_DTS] [datetime2](7) NOT NULL,
[PMT_EXCEPT_DTS] [datetime2](7) NULL,
[PROMISE_EXCEPT_DTS] [datetime2](7) NULL,
[INSTALLMENT_NO] [varchar](255) NOT NULL,
[LINE_ITEM_NO] [varchar](255) NOT NULL,
[INV_ITEM_TYPE_CD] [varchar](255) NOT NULL,
[INV_ITEM_TYPE_CCD] [varchar](255) NOT NULL,
[INV_ITEM_TYPE_CST] [varchar](255) NOT NULL,
[INV_ITEM_TYPE_CLT] [varchar](255) NOT NULL,
[CURR_CD] [varchar](255) NOT NULL,
[INV_ITEM_AMT] [numeric](18, 2) NOT NULL,
[COMMENTS] [varchar](1333) NOT NULL,
[INV_ITEM_TEXT] [varchar](1333) NOT NULL,
[CSTM_PMT_GROUP_TEXT] [varchar](1333) NULL,
[EXCEPT_CMT] [varchar](1333) NOT NULL,
[GROSS_SETTLED_FL] [varchar](1) NOT NULL,
[RETIRED_FL] [varchar](1) NOT NULL,
[REVERSED_FL] [varchar](1) NOT NULL,
[ETL_CURR_ROW_FL] [varchar](1) NOT NULL,
[ETL_LATE_ARRIVING_FL] [varchar](1) NOT NULL,
[ETL_ACTIVE_FL] [varchar](1) NOT NULL,
[ETL_ADD_DTS] [datetime2](7) NULL,
[ETL_LAST_UPDATE_DTS] [datetime2](7) NOT NULL,
CONSTRAINT [DBII_PK] PRIMARY KEY NONCLUSTERED
(
[BILL_INV_ITEM_DID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [DBII_AK1] UNIQUE NONCLUSTERED
(
[BILL_INV_ITEM_KEY] ASC,
[ETL_ROW_EFF_DTS] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
gary1
gary1
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17652 Visits: 2354
Trigger1:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[DIM_BILL_INV_ITEM_GAD] ON [dbo].[DIM_BILL_INV_ITEM] FOR DELETE AS
SET NOCOUNT ON;

UPDATE l SET [ACTION_CODE] = 'D'
FROM LOG_DIM_BILL_INV_ITEM l
JOIN deleted d ON d.BILL_INV_ITEM_DID = l.BILL_INV_ITEM_DID
WHERE l.[ACTION_CODE] = 'U'
;

INSERT INTO LOG_DIM_BILL_INV_ITEM (BILL_INV_ITEM_DID, BILL_INV_ITEM_KEY, ETL_ROW_EFF_DTS, ETL_ROW_EXP_DTS, ROW_PROC_DTS, SOURCE_SYSTEM, BILL_INV_KEY, CHRG_KEY, BILL_POL_PRD_KEY, EVENT_DTS, PMT_EXCEPT_DTS, PROMISE_EXCEPT_DTS, INSTALLMENT_NO, LINE_ITEM_NO, INV_ITEM_TYPE_CD, INV_ITEM_TYPE_CCD, INV_ITEM_TYPE_CST, INV_ITEM_TYPE_CLT, CURR_CD, INV_ITEM_AMT, COMMENTS, INV_ITEM_TEXT, CSTM_PMT_GROUP_TEXT, EXCEPT_CMT, GROSS_SETTLED_FL, RETIRED_FL, REVERSED_FL, ETL_CURR_ROW_FL, ETL_LATE_ARRIVING_FL, ETL_ACTIVE_FL, ETL_ADD_DTS, ETL_LAST_UPDATE_DTS, [ACTION_CODE])
SELECT BILL_INV_ITEM_DID, BILL_INV_ITEM_KEY, ETL_ROW_EFF_DTS, ETL_ROW_EXP_DTS, ROW_PROC_DTS, SOURCE_SYSTEM, BILL_INV_KEY, CHRG_KEY, BILL_POL_PRD_KEY, EVENT_DTS, PMT_EXCEPT_DTS, PROMISE_EXCEPT_DTS, INSTALLMENT_NO, LINE_ITEM_NO, INV_ITEM_TYPE_CD, INV_ITEM_TYPE_CCD, INV_ITEM_TYPE_CST, INV_ITEM_TYPE_CLT, CURR_CD, INV_ITEM_AMT, COMMENTS, INV_ITEM_TEXT, CSTM_PMT_GROUP_TEXT, EXCEPT_CMT, GROSS_SETTLED_FL, RETIRED_FL, REVERSED_FL, ETL_CURR_ROW_FL, ETL_LATE_ARRIVING_FL, ETL_ACTIVE_FL, ETL_ADD_DTS, ETL_LAST_UPDATE_DTS, 'D'
FROM deleted AS d
WHERE NOT EXISTS ( SELECT 1 FROM LOG_DIM_BILL_INV_ITEM AS l WHERE d.BILL_INV_ITEM_DID = l.BILL_INV_ITEM_DID )
;

GO

ALTER TABLE [dbo].[DIM_BILL_INV_ITEM] ENABLE TRIGGER [DIM_BILL_INV_ITEM_GAD]
GO

*******************************

Trigger 2:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[DIM_BILL_INV_ITEM_GAU] ON [dbo].[DIM_BILL_INV_ITEM] FOR UPDATE AS
SET NOCOUNT ON;

-- Log Change
INSERT INTO LOG_DIM_BILL_INV_ITEM ( D.BILL_INV_ITEM_DID, D.BILL_INV_ITEM_KEY, D.ETL_ROW_EFF_DTS, D.ETL_ROW_EXP_DTS, D.ROW_PROC_DTS, D.SOURCE_SYSTEM, D.BILL_INV_KEY, D.CHRG_KEY, D.BILL_POL_PRD_KEY, D.EVENT_DTS, D.PMT_EXCEPT_DTS, D.PROMISE_EXCEPT_DTS, D.INSTALLMENT_NO, D.LINE_ITEM_NO, D.INV_ITEM_TYPE_CD, D.INV_ITEM_TYPE_CCD, D.INV_ITEM_TYPE_CST, D.INV_ITEM_TYPE_CLT, D.CURR_CD, D.INV_ITEM_AMT, D.COMMENTS, D.INV_ITEM_TEXT, D.CSTM_PMT_GROUP_TEXT, D.EXCEPT_CMT, D.GROSS_SETTLED_FL, D.RETIRED_FL, D.REVERSED_FL, D.ETL_CURR_ROW_FL, D.ETL_LATE_ARRIVING_FL, D.ETL_ACTIVE_FL, D.ETL_ADD_DTS, D.ETL_LAST_UPDATE_DTS, [ACTION_CODE] )
SELECT D.BILL_INV_ITEM_DID, D.BILL_INV_ITEM_KEY, D.ETL_ROW_EFF_DTS, D.ETL_ROW_EXP_DTS, D.ROW_PROC_DTS, D.SOURCE_SYSTEM, D.BILL_INV_KEY, D.CHRG_KEY, D.BILL_POL_PRD_KEY, D.EVENT_DTS, D.PMT_EXCEPT_DTS, D.PROMISE_EXCEPT_DTS, D.INSTALLMENT_NO, D.LINE_ITEM_NO, D.INV_ITEM_TYPE_CD, D.INV_ITEM_TYPE_CCD, D.INV_ITEM_TYPE_CST, D.INV_ITEM_TYPE_CLT, D.CURR_CD, D.INV_ITEM_AMT, D.COMMENTS, D.INV_ITEM_TEXT, D.CSTM_PMT_GROUP_TEXT, D.EXCEPT_CMT, D.GROSS_SETTLED_FL, D.RETIRED_FL, D.REVERSED_FL, D.ETL_CURR_ROW_FL, D.ETL_LATE_ARRIVING_FL, D.ETL_ACTIVE_FL, D.ETL_ADD_DTS, D.ETL_LAST_UPDATE_DTS, 'U'
FROM DELETED AS D
WHERE D.ETL_ACTIVE_FL = 'Y'
AND NOT EXISTS ( SELECT 1 FROM LOG_DIM_BILL_INV_ITEM AS l
WHERE D.BILL_INV_ITEM_DID = L.BILL_INV_ITEM_DID )
;

-- Update the active flag
UPDATE T SET ETL_ACTIVE_FL = 'N'
,ETL_LAST_UPDATE_DTS = CURRENT_TIMESTAMP
FROM dbo.DIM_BILL_INV_ITEM AS T
JOIN INSERTED AS I ON I.BILL_INV_ITEM_DID = T.BILL_INV_ITEM_DID
;


GO

ALTER TABLE [dbo].[DIM_BILL_INV_ITEM] ENABLE TRIGGER [DIM_BILL_INV_ITEM_GAU]
GO
gary1
gary1
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17652 Visits: 2354
Can you please advise how I can fix this blocking?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search