locked
LCK_M_S locks are matter to consider RRS feed

  • Question

  • Hi I have seen following waits on sql server and seen it LCK_M_S. So would that be matter to consider as performance point .

     select resource_type,
     --resource_database_id,ex.login_name,
        resource_description,
        resource_associated_entity_id,
        request_mode,
        request_type,
        request_status,
        request_lifetime,
        request_session_id,
        request_owner_type   
    from sys.dm_tran_locks  with (nolock)
    inner join  sys.dm_exec_sessions  ex on  ex.session_id=request_session_id
    where resource_database_id=DB_ID('CRM')
    and request_status='GRANT'
    --and request_session_id in () 

     


    • Edited by ashwan Wednesday, July 29, 2020 7:44 AM
    Wednesday, July 29, 2020 7:43 AM

All replies

  • The locks on the database in your second screenshot are perfectly normal. Each process that accesses a database holds a shared lock on it, so that no on can drop the database while the process is working.

    The wait stats with over 90% of the waits being LCK_M_S is more interesting, though. However, without knowing under which conditions the waits were collected, it is difficult to give advice, as I don't know how representative it is. I get the impression that this data is collected for a fairly small time span.

    LCK_M_S means that the process is waiting for a shared lock, which is needed to read data with the default isolation level READ COMMITTED. Such long waits indicates that the process is blocked by a writer. There are ways to deal with this, but as I said, there are too many unknonws here for me wanting to make any suggestions out of the blue.

    What does "SELECT @@version" report?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, July 29, 2020 8:18 AM
  • Hi Erland many thanks for quick reply .

    Microsoft SQL Server 2016 (SP2-CU3) (KB4458871) - 13.0.5216.0 (X64)   Sep 13 2018 22:16:01   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 


    • Edited by ashwan Wednesday, July 29, 2020 9:21 AM
    Wednesday, July 29, 2020 9:20 AM
  • Hi,

    The LCK_M_S wait type is when a thread is waiting to acquire a Shared lock on a resource and there is at least one other lock in an incompatible mode granted on the resource to a different thread.

    Try this query:

    select * from 
    sys.dm_os_waiting_tasks t
    inner join sys.dm_exec_connections c on c.session_id = t.blocking_session_id
    cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) as h1

    Also please refer to following posts:
    LCK_M_S

    SQL SERVER – LCK_M_XXX – Wait Type

    Best Regards,
    Cris

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 30, 2020 2:23 AM
  • Hi Cris Thank you for valuable  advice. how every I came nothing on this SQL and see some deadlocks 

    Friday, July 31, 2020 10:27 PM
  • Hi Cris Thank you for valuable  advice. how every I came nothing on this SQL and see some deadlocks 

    The query Cris gave you will only show blocking if there is a current blocking situation going on. (These people from MSDN Community Support often give answers that are off the mark.)

    There are no deadlocks in your screenshots. There is a wait type REQUEST_FOR_DEADLOCK_SEARCH. This is one of the many wait types you should ignore, because they are from background tasks, and you correctly do this in the second query.

    I believe this particular one, REQUEST_FOR_DEADLOCK_SEARCH, is related to the process that wakes up every five seconds to search for deadlocks.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, August 1, 2020 8:24 AM