SQL blocking caused by running ALTER VIEW script

Question

SQL blocking caused by running ALTER VIEW script

webrunner

SSC-Dedicated

Points: 31862
More actions
March 27, 2023 at 5:06 pm

#4165614
Hello experts,
One of our developers tried to alter a view and reported to me that it was taking a long time. When I checked, it turns out that, for some reason, running the ALTER script is consistently causing database blocking. I ran a trace to capture the blocked process report. Below is an excerpt from it.
<blocked-process-report monitorLoop="119647">
<blocked-process>
<process id="processbefdeb88c8" taskpriority="0" logused="0" waitresource="OBJECT: 77:366624349:0 " waittime="4930" ownerId="625185462" transactionname="implicit_transaction" lasttranstarted="2023-03-27T10:50:40.090" XDES="0xbc516de400" lockMode="IS" schedulerid="2" kpid="4288" status="suspended" spid="200" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2023-03-27T10:50:40.093" lastbatchcompleted="2023-03-27T10:50:40.090" lastattention="1900-01-01T00:00:00.090" hostname="1.1.1.1" hostpid="0" loginname="app_login" isolationlevel="read committed (2)" xactid="625185462" currentdb="77" currentdbname="MyDB" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128058">
<executionStack>
<frame line="26" stmtstart="1798" stmtend="34274" sqlhandle="0x03004d007793276910daed00a4af000001000000000000000000000000000000000000000000000000000000"/>
</executionStack>
<inputbuf>
Proc [Database Id = 77 Object Id = 1764201335] </inputbuf>
</process>
</blocked-process>
<blocking-process>
<process status="suspended" waitresource="OBJECT: 77:366624349:0 " waittime="5033" spid="80" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2023-03-27T10:50:39.987" lastbatchcompleted="2023-03-27T10:50:39.980" lastattention="2023-03-27T10:49:43.893" clientapp="Microsoft SQL Server Management Studio - Query" hostname="MySSMSHost" hostpid="8084" loginname="DOMAIN\MyLogin" isolationlevel="read committed (2)" xactid="625185408" currentdb="77" currentdbname="MyDB" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame line="15" stmtstart="820" sqlhandle="0x01004d0022e6182a00f8f303bd00000000000000000000000000000000000000000000000000000000000000"/>
</executionStack>
<inputbuf>
...
Does anyone know, generally speaking, how to find out why an ALTER statement would cause blocking this way? It seems like a weird outcome of just trying to change SQL code.
Thanks for any help!
-- webrunner
- This topic was modified 1 year, 8 months ago by webrunner.
- This topic was modified 1 year, 8 months ago by webrunner.
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Does your view definition include 'WITH SCHEMABINDING'?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

webrunner SSC-Dedicated Points: 31862 More actions · Answer 2

Hi Phil,

I checked, and the view definition does not include 'WITH SCHEMABINDING'. But thanks for this info - it will come in handy.

Next I am going to review the SQL itself. It UNIONs 5 select statements and I want to see if it somehow is taking locks while trying to compile.

Will post back when I learn more.

Thanks again!

-- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

webrunner SSC-Dedicated Points: 31862 More actions · Answer 3

PS A little more info:

I was able to create a view with the same code but a slightly different name. Worked instantly.
Even trying to drop the original view causes blocking.
I also tried removing the db from its availability group to see if that was somehow related. But the same blocking behavior happens.

Pretty maddening. But I'll keep checking

Thanks.

-- webrunner

This reply was modified 1 year, 7 months ago by webrunner.

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html