T-SQL Tuesday #17: Using APPLY

I confess, I’m one of those guys who really didn’t know much about APPLY. Sure, I saw it in the occasional query I’d find on the ‘tubes, but I never really dug into what it was and why this piece of syntax was so important. Fortunately, this month’s T-SQL Tuesday has rolled around with an opportunity for me to do a little learning, so off to the Google-mobile!

Of course, you can easily find the MSDN article explaining apply, but there’s a much more detailed one I found on Simple Talk by Robert Sheldon. Conceptually, it’s pretty simple. APPLY, in both its CROSS APPLY and OUTER APPLY forms, allows you to call a table valued function for each row in your data set. In a lot of ways it’s like a join, but instead of two tables linked on a common field, you’re linking your query with a computed set of rows that you return with your function.

Now, I wish I could say I had some cool query to share, but tend to use APPLY in some simple ways to get query information. Here’s a query that makes use of the dynamic management functions dm_exec_query_plan and dm_exec_sql_text that gets a lot of work from me:

select
	er.session_id,
	d.name database_name,
	er.status,
	er.wait_type,
	eqp.query_plan,
	est.text
from sys.dm_exec_requests er
	join sys.databases d on (er.database_id = d.database_id)
	CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) eqp
	CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) est

Using this, you can get your text and your query plan for all queries currently running on your server. I like to use the query plans to troubleshoot long running queries, and the wait types help you identify the queries you might want to look at first.

Enjoy the other T-SQL Tuesday posts out there and huge thanks to Matt Velic(blog|twitter) for hosting this month!