Welcome to another T-SQL Tuesday! This week is hosted by Matt Velic (blog|twitter), so Matt what is this week about?
APPLY Knowledge
Recently on Twitter, I heard the claim that “If you don’t understand the APPLY operator, your skills are somewhere around the 50th percentile at best.” While I believe that Adam was giving a warning to self-proclaimed experts (possibly one he might have been interviewing at the time…), I also believe that we could take it as a challenge as a T-SQL blogging community to learn more about APPLY and the ways in which we can use it in our work.
Please share how you use this wonderful feature. Maybe you know how APPLY works inside and out? Perhaps you’ve got a fantastic user defined function (UDF) to share? Or maybe your experience revolves around using Dynamic Management Functions (DMFs) in your never-ending quest for SQL Server performance? Let the community know as it is time to study!
All right, here we go then this is all about the Apply!
START WITH THE BASICS
The Apply operator was added to SQL Server with SQL 2005. Apply is a logical join operator, it allows you to perform a join on a Table Valued Function. I think the most common way that people use Apply is to get the SQL Text or Query Plan using DMV’s.
SELECT
er.session_id
,st.text
,qp.query_plan
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp
The previous query will return any currently executing query on your system. Using the CROSS APPLY is like using a left join, but on a Table Value Function, there is also an OUTER APPLY which is similar to the functionality as a right join. In this case we are Using CROSS APPLY to return the Text of the SQL Statement being executed, as well as the Query Plan.
The Query Plan can have some over head associated with it, if you’re on a busy system I would recommend commenting out the Query Plan unless you’re truly looking for it.
SURE BUT CAN IT PLAY THE VIOLIN
So the DMV route is pretty well known, but how do you apply this in a non-troubleshooting capacity? For this next example I’m using the AdventrueWorks2008R2 Database.
So let’s toss out a Case Scenario. Our boss has come to us, there is an application that the company uses and as part of the expanded functionality we’ll be adding a lookup of Customer’s information. We need to provide a list of Customer Names on demand, or the Name of a particular Customer, that are identified to us as People and not businesses. (Not a complicated Scenario, but hey let’s start out practical).If you Expand AdventureWorks2008R2 | Programmability | Functions| Table-valued Functions, you’ll see dbo.ufnGetContactInformation. This Table Valued Function is exactly the type that we can use in the CROSS APPLY Statement.
Our Table Value Function accepts the @PersionID parameter, and we will be using the SALES.CUSTOMER table. We take a quick look at the table to see what details we pull back.
SELECT
*
FROM
SALES.Customer
We see that names are not stored in this table, but we know we can retrieve them through our function. Okay we don’t know that, but if you right click on the function and script it out you can read through it and see what it is doing.
So let’s use our CROSS APPLY statement.
SELECT
ci.FirstName
,ci.LastName
,ci.JobTitle
,sc.AccountNumber
FROM
SALES.Customer sc
CROSS APPLY dbo.ufnGetContactInformation(sc.PersonID) ci
So now we can see that this can be used in a non-troubleshooting capacity. So let’s think about a couple other possibilities. Let’s select an order being delivered and we want to know the last update in its location. We have a customer looking at an item on a web page, and we want to display a coupon for the item.
There are a lot of possibilities for CROSS APPLY, I would suggest watching the MCM Waits and Queues video by Paul Randal (blog|twitter) this link to the Demo will cover a great scripts by Glenn Berry (blog|twitter) using CTE’s & one by Joe Sack (blog|twitter) that makes a really good use of OUTER APPLY.
/*
How do you clear the server's wait
stats?
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
=================================================
BY: Glenn Berry & Paul Randal
CTE to get wait stats.
This CTE get's the wait stats as they are occuring on the
system and give's you the total seconds of wait, the
# of occurances, as well as the top percentage of where
your wait time is coming from.
The Exclusions of certian wait types were recommendations
by Paul Randal in the MCM wait types video series
==================================================
*/
WITH Waits AS
(SELECT
wait_type,
wait_time_ms /1000.0 AS Waits,
(wait_time_ms - signal_wait_time_ms)/1000.0 AS Resources,
signal_wait_time_ms/1000.0 AS Signals,
waiting_tasks_count AS WaitCount,
100.0 * wait_time_ms /SUM(wait_time_ms) OVER()AS Percentage,
ROW_NUMBER()OVER
(ORDER BY wait_time_ms DESC)AS RowNum
FROM sys.dm_os_wait_stats
WHERE
wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR','LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT','XE_DISPATCHER_JOIN','SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
)
SELECT
W1.wait_type,
CAST(W1.Waits AS DECIMAL(12,2))AS Wait_S,
CAST(w1.Resources AS DECIMAL(12,2))AS Resources_S,
CAST(W1.Signals AS DECIMAL(12,2))AS Signal_S,
W1.WaitCount,
CAST(W1.Percentage AS DECIMAL(12,2))AS Percentage,
CAST(AVG(W1.Signals)/ W1.WaitCount AS DECIMAL(12,8))AS AvgWait_S,
CAST(AVG(W1.Resources)/ W1.WaitCount AS DECIMAL(12,8))AS AvgRes_S
FROM
Waits AS W1
INNER JOIN Waits AS W2
ON
W2.RowNum <=W1.RowNum
GROUP BY
W1.RowNum,W1.wait_type,W1.Waits,W1.Percentage, w1.Resources, w1.WaitCount,w1.Signals
HAVING
SUM(W2.Percentage) -W1.Percentage <99
/*
=================================================
BY: Joe Sack
This will give you the waits as they are happening
on a system, and give you the query text, as well
as the execution plans as they are occuring
I added the @@SPID to the query so we would not
see our own query plan come back while trouble
shooting
==================================================
*/
Select
DB_NAME(est.dbid) AS DatabaseName,
owt.session_id,
owt.wait_duration_ms,
owt.wait_type,
owt.blocking_session_id,
owt.resource_description,
es.program_name,
est.text,
est.dbid,
eqp.query_plan,
es.cpu_time,
es.memory_usage
from sys.dm_os_waiting_tasks owt
INNER JOIN sys.dm_exec_sessions es ON
owt.session_id=es.session_id
INNER JOIN sys.dm_exec_requests er ON
es.session_id=er.session_id
OUTER APPLY sys.dm_exec_sql_text(er.plan_handle) est
OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) eqp
WHERE es.is_user_process=1
AND es.session_id<> @@spid;
Here are the Scripts, but go watch to video to see how to use them!
Thanks,
Brad
