T-SQL Tuesday #17 – APPLY Knowledge

This month’s T-SQL Tuesday is brought to us by Matthew Velic (blog | twitter). He passed on a tweet from Adam Machanic (blog | twitter), which suggests that we should all know and understand the APPLY operator, and so Matt has decided that this should be the topic of this month’s T-SQL Tuesday.

Now, here’s the kicker.  As I’m mostly working with SQL Server 2000 these days, I don’t have enormous familiarity with this as it was introduced with SQL Server 2005, and I don’t make much use of it.  However, in true Blue Peter style, here’s one I prepared earlier.

The Requirement

A query to show the current SQL Server object-level locks and the queries that are causing them.

The Query

SELECT
    dmTL.resource_type,
    db_name(dmTL.resource_database_id),
    dmTL.request_mode,
    dmTL.request_type,
    dmTL.request_status,
    dmTL.request_session_id,
    ST.Text
FROM sys.dm_tran_locks dmTL
    LEFT JOIN sys.dm_exec_sessions dmES
        ON dmTL.request_session_id = dmES.session_id
    LEFT JOIN sys.dm_exec_requests dmER
        ON dmer.session_id = dmtl.request_session_id
    CROSS APPLY sys.dm_exec_sql_text (dmER.sql_handle) AS ST
WHERE
    resource_type = 'Object'
AND request_session_id <> @@SPID

(One of these days I’m going to do something about getting properly formatted & colour-coded queries into this blog…)

See below for an example of this query in action.

OK, the query causing the lock in this example was artificial, but it still shows the sort of output you would get.  There’s a list of the lock modes buried in the documentation for sp_lock.

If you want to run this query yourself, but you’re not a sysadmin, then you’ll need to have VIEW SERVER STATE permissions.

The Explanation

What, you want more?  OK.  The bulk of this query is involved with taking a session ID and getting the SQL Handle needed to look at the actual query text.

The CROSS APPLY bit takes the SQL Handle figured out by the rest of the query and runs the TVF sys.dm_exec_sql_text to return the SQL code being executed that generated the lock.

The final bit, request_session_id <> @@SPID, is there to remove anything arising from the session used to run the query.