March 27, 2023 at 5:06 pm
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
-------------------
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
March 28, 2023 at 8:29 am
Does your view definition include 'WITH SCHEMABINDING'?
March 28, 2023 at 3:37 pm
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
March 28, 2023 at 7:08 pm
PS A little more info:
Pretty maddening. But I'll keep checking
Thanks.
-- 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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy