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.
A query to show the current SQL Server object-level locks and the queries that are causing them.
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.
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.