How can I check if an SQL table is locked for querying?

Question

For whatever reason one specific table in the database started to not to complete any query results on a specific table. It says 'executing query...' and not completes.

Like:

select * from foo

Other tables are returning rows.

How/where I can check if a table is locked?

Solution 1

Check this sample

    select  
    object_name(P.object_id) as TableName
    , resource_type
    , resource_description
    , request_mode
    , CASE REQUEST_MODE
                    WHEN 'S'        THEN 'Shared'
                    WHEN 'U'        THEN 'Update'
                    WHEN 'X'        THEN 'Exclusive'
                    WHEN 'IS'       THEN 'Intent Shared'
                    WHEN 'IU'       THEN 'Intent Update'
                    WHEN 'IX'       THEN 'Intent Exclusive'
                    WHEN 'SIU'      THEN 'Shared Intent Update'
                    WHEN 'SIX'      THEN 'Shared Intent Exclusive'
                    WHEN 'UIX'      THEN 'Update Intent Exclusive'
                    WHEN 'BU'       THEN 'Bulk Update'
                    WHEN 'RangeS_S' THEN 'Shared Range S'
                    WHEN 'RangeS_U' THEN 'Shared Range U'
                    WHEN 'RangeI_N' THEN 'Insert Range'
                    WHEN 'RangeI_S' THEN 'Insert Range S'
                    WHEN 'RangeI_U' THEN 'Insert Range U'
                    WHEN 'RangeI_X' THEN 'Insert Range X'
                    WHEN 'RangeX_S' THEN 'Exclusive range S'
                    WHEN 'RangeX_U' THEN 'Exclusive range U'
                    WHEN 'RangeX_X' THEN 'Exclusive range X'
                    WHEN 'SCH-M'    THEN 'Schema-Modification'
                    WHEN 'SCH-S'    THEN 'Schema-Stability'

        ELSE NULL
        END AS REQUEST_LOCK_MODE

FROM   sys.dm_tran_locks   AS L
       join sys.partitions AS P 
        on L.resource_associated_entity_id = p.hobt_id

REF: http://sqlblog.foxraven.com/2012/01/check-to-see-if-table-is-locked.html

Solution 2

There are several ways to check what is blocking the query. The simplest way is to start the built-in Activity Monitor.

An equally simple way is to look in sys.dm_exec_requests and look at the wait_type, wait_time, wait_resource and blocking_session_id columns for the query that is blocked (use session_id to identify it).

For a more complex, but more informative, way watch How to Use sp_WhoIsActive to Find Slow SQL Server Queries.