Post reply

SQL blocking caused by running ALTER VIEW script

  • 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, 9 months ago by  webrunner.
    • This topic was modified 1 year, 9 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

  • March 28, 2023 at 8:29 am

    #4165863

    Does your view definition include 'WITH SCHEMABINDING'?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • March 28, 2023 at 3:37 pm

    #4166061

    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

    #4166136

    PS A little more info:

    1. I was able to create a view with the same code but a slightly different name. Worked instantly.
    2. Even trying to drop the original view causes blocking.
    3. 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, 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