Post reply

Question on Locks

  • July 9, 2019 at 10:03 am

    #3661055

    Hi All,

    I have a question on objects and lock information. Is there a way to tell if any lock has been taken for an object and which spid has taken that lock? The reason, why I am asking is, in our environment many users like dev team, testing team and business users will use the database server and access respective databases.

    Sometimes they just ping me and ask "that we are seeing slowness on <<specific db>>" , Then I would ask them what is slow? is there any specific query? some users will provide queries and some not. Such users, would ask "can you please check if there are any LOCKS applied on a <<specific table>> ...??

    Its difficult for me to tell by tablename , and what spid is holding so and so LOCK and so any subsequent queries/spids are getting blocked or slow? Other problem is, they don't have VIEW SERVER STATE permission on the database server. So, they will ask this type of questions?

    So, want to know is there a way to tell currently so and so lock has been acquired on a given object/table and this is the spid which is holding the lock? Any DMVs I can look at to fetch this information?

    We have sql 2012 and above environments.

    Thanks,

    Sam

  • July 9, 2019 at 10:23 am

    #3661059

    Google knows a lot about locks and SQL server. The only reason not to use it first  only when you need personalized answer.

    But in your case I would use sp_whoisactive procedure which is valuable free tool for ad-hoc monitoring used by many-many DBAs .

    http://whoisactive.com/

    Adam Machanic did very good job!

     

     

  • July 9, 2019 at 11:29 am

    #3661076

    Second, and enthusiastic, vote for sp_whoisactive.

    And yes, there are Dynamic Management Views (DMVs) that are specific for locking and blocking. Microsoft has an article on how you can query them yourself to get the information you need. However, other than educational purposes, I wouldn't recommend this. Instead, get sp_whoisactive (just repeating, free) and use that. It does a more thorough job than you'll do on your own.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • July 9, 2019 at 3:25 pm

    #3661210

    Thanks Andrey and Grant.

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

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