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.

Advertisements
This entry was posted in SQLServerPedia Syndication, T-SQL Tuesday and tagged , . Bookmark the permalink.

2 Responses to T-SQL Tuesday #17 – APPLY Knowledge

  1. Matt Velic says:

    Thanks for joining in, Thomas!

  2. Pingback: T-SQL Tuesday #17 Roundup - APPLY Knowledge | Matt Velic

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s