May 2, 2018 at 6:02 pm
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
May 2, 2018 at 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
May 2, 2018 at 11:32 pm
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>
May 3, 2018 at 1:25 am
Can you post the DDL (create table) script for the table DIM_BILL_INV_ITEM including triggers and indices please?
😎
May 3, 2018 at 4:10 am
gary1 - Wednesday, May 2, 2018 6:23 PMSession 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" 😉
May 3, 2018 at 11:22 am
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
May 3, 2018 at 11:26 am
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
May 7, 2018 at 4:40 pm
Can you please advise how I can fix this blocking?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply