Post reply

Help finding the right the wait type that blocked my query 1 day ago.

  • March 6, 2022 at 10:43 pm

    #3994450

    I am diagnosing an issue with my sql server that happened 1 day ago. There was a query that was modifying the schema of some of my tables (turning system versioning on), this caused that the queries that were using these tables started to get slow because of locking. I am interested to see if there is a way in SQLServer to see the wait type that a session was waiting for in a moment of time (1 day ago). What is the normal procedure to investigate this type of issues? https://omegle.onl/

    • This topic was modified 2 years, 8 months ago by  zabuza.

  • March 7, 2022 at 3:49 am

    #3994508

    If you were not capturing it at the time, then that data is lost.

    But if you were performing object-level changes on tables, you would have blocking - those changes require Schema-Modify locks, which are even more restrictive than an exclusive (TABLOCKX) lock, and flipping on versioning isn't necessarily lightweight. No other session can access any portion of a table while that change is taking place (regardless of transaction isolation level), and that change cannot begin until all other threads presently holding locks complete their work and release their locks. New threads will either block the command thread or wait behind it for whatever lock they were trying to get.

    The normal approach would be to schedule any such exclusive table-altering activities when you cause the least impact, and to watch the server during the change by querying sys.dm_exec_requests to see how many threads are waiting on locks while being blocked by the command thread. I believe in the case you described above, you would see threads waiting on Schema-Stability locks (lock type Sch-S, wait type LCK_M_SCH_S), with the Wait Resource shown being the object id of the table in question. If the thread that alters the table is blocked, it will be waiting with LCK_M_SCH_M while attempting to acquire the Sch-M lock in order to alter the table.

    Eddie Wuerch
    MCM: SQL

  • March 7, 2022 at 7:20 am

    This was removed by the editor as SPAM

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

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