UPDATE is taking time and causing blocking

Question

UPDATE is taking time and causing blocking

Sam 1,476

Hi All,

We have a below UPDATE query which is causing a lot of blocking in QA env.

This is taking more than 4 hours and keep running and we see a waittype as

"(151ms)PAGEIOLATCH_SH:QA4_ORS:3(*)". Worst part is, the app team has scheduled this stmt at multiple times of the day. Even before the 1st UPDATE completes, the same UPDATE stmt comes up with a different sessionid? No idea why they are doing so but resulting in lot of heavy blocking. I want to check if they can first archive old data and then perform the UPDATE. but sooner or later as the data gets increased we get back to same problem.

The server has 8 cpu's. 128GB RAM and max server memory set to 105 GB.

This is a history table. There is no PRIMARY KEY on this table. Its a HEAP.

The table size is 74GB and it has 34,866,803 rows. Its a wide table with 90+ columns.

Please let me know if there is any better way to re-write this below query.

Attaching table structure, index details and Estimated plan.

indexes

SQL stmt

===========

UPDATE C_B_REF_IND_HIST

server settings

indexes

1-HEAP or clustered idx

update_frm

TableStructure.txt

Regards,

Sam

Sam 1,476

-- below is the UPDATE stmt which is causing issue

UPDATE C_B_REF_IND_HIST
SET FIRST_NM = '********',
    MIDDLE_NM = '********',
    LAST_NM = '********',
    YEAR_OF_BIRTH = '*****',
    AGE_RANGE = '********',
    GENDER_CD = NULL,
    LOCATION = '********',
    CITY = '********',
    REGION = '********',
    COUNTRY_CD = '********',
    ORGANIZATION = '********',
    JOB_TITLE = '********',
    PRIMARY_PHOTO = '********',
    LINKED_IN = '********',
    FACEBOOK = '********',
    TWITTER = '********',
    EMPLMT_DTL = NULL,
    EDU_DTL = NULL
WHERE FIRST_NM = '********'

Sam 1,476 Reputation points

2023-11-17T13:55:01.7333333+00:00

Estimated plan

https://www.brentozar.com/pastetheplan/?id=By2xN1BV6
Sam 1,476 Reputation points

2023-11-18T09:10:17.7633333+00:00

In addition to, creating a non-clustered index on first_nm column, is there a way to make it run faster? can it done in small chunks ?

Answer accepted by question author

0 additional answers

Your answer

Sam 1,476 Reputation points

2023-11-17T13:52:47.5766667+00:00

-- below is the UPDATE stmt which is causing issue

UPDATE C_B_REF_IND_HIST SET FIRST_NM = '********', MIDDLE_NM = '********', LAST_NM = '********', YEAR_OF_BIRTH = '*****', AGE_RANGE = '********', GENDER_CD = NULL, LOCATION = '********', CITY = '********', REGION = '********', COUNTRY_CD = '********', ORGANIZATION = '********', JOB_TITLE = '********', PRIMARY_PHOTO = '********', LINKED_IN = '********', FACEBOOK = '********', TWITTER = '********', EMPLMT_DTL = NULL, EDU_DTL = NULL WHERE FIRST_NM = '********'
Sam 1,476 Reputation points

2023-11-17T13:55:01.7333333+00:00

Estimated plan

https://www.brentozar.com/pastetheplan/?id=By2xN1BV6
Sam 1,476 Reputation points

2023-11-18T09:10:17.7633333+00:00

In addition to, creating a non-clustered index on first_nm column, is there a way to make it run faster? can it done in small chunks ?

Answer 1

Dan Guzman 9,411

In addition to, creating a non-clustered index on first_nm column, is there a way to make it run faster? can it done in small chunks ?

The non-clustered index on name is a good first step to avoid the full table scan and mitigate blocking. I suggest you create that index and monitor.

For next steps, consider changing one of the other indexes to clustered. If index key values are unique, explicitly specify unique as that can help SQL Server optimize queries better. Same with non-clustered indexes. The best candidate for the clustered index depends on the workload and queries.

Sam 1,476 Reputation points

2023-11-19T05:07:24.1+00:00

Thanks Dan

Share via

UPDATE is taking time and causing blocking

0 additional answers

Your answer