T-SQL Tuesday 17: Apply

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

T-SQL Tuesday 16: Aggregation

So it’s that time again, T-SQL Tuesday.  This week our event is Sponsored by Jes Borland (Blog|Twitter).  So Jes what’s the subject?

“I want to hear how you solved business problems with aggregate functions. I want to see your cool T-SQL tricks. How are aggregates used in SSRS, SSAS, or SSIS? What have you learned about aggregate functions?”

So Dear Reader, Aggregation it is!

SOME THINGS JUST GO TOGETHER

We’ve all probably done our fair share of fancy aggregates, I have to say that most of the code I’ve used aggregates for is for business functionality and sitting with clients that I’ve worked for in the past.  I think we all have a nightmare report, script, or task that we accomplished using aggregates and while those things make for interesting stories, they aren’t that useful.  I’d like to give you something that you can use, (if you don’t already have a script to do what I’ve done here). 

Sometimes the most useful things are simple, like PB & J (simple and delicious!).  Here are a couple of queries that I use that have the SUM aggregate function in them.  Simple but effective.

The first is a quick script that extracts the information from DBCC SHOWFILESTATS and uses the "undocumented" sp_msforeachdb in order to gather total space used by all datafiles on an instance, total space reserved for all data files on an instance, then a breakdown by datafile per database, lastly we take the info from DBCC SQLPERF(LOGSPACE)and sort by the log file size.

The second script is of the same concept.  You can use the built-in reports “Disk Usage by Tables” to get the same info, but sometimes it’s just nice to build a script to work for you.  This script uses sp_spaceused and the "undocumented" sp_msforeachtable in order to build a list of tables by row number and the Reserved size for Data. (I use the Second script on my Compression demos to help you find an appropriate table (size wise there are multiple steps J) while examining compression).  Then I do a SUM function on each of the fields to show an aggregate of your database by user table.

SCRIPT1 – Get Database Size Stats

/*============================================================

--@SQLBalls: SQLBalls@gmail.com

--http://www.SQLBalls.com

--T-SQL TUESDAY 16 Aggregation

--

--This Sample Code is provided for the purpose of illustration only

--and -is not intended to be used in a production environment. 

--THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS"

--WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING

--BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR

--FITNESS FOR A PARTICULAR PURPOSE.

==============================================================*/

IF exists(SELECT * FROM tempdb.sys.tables WHERE name like '#myTable%' and name not like'#myTable2%')

BEGIN

    DROP TABLE #myTable;

END

CREATE TABLE #myTable (

                      [myID] [int] IDENTITY(1,1) NOT NULL,

                      fileid varchar(4),

                      fileGroup varchar(4),

                      TotalExtents int,

                      usedExtents int,

                      DBName sysname,

                      filename1 varchar(8000));

GO

                     

IF exists(SELECT * FROM tempdb.sys.tables WHERE name like '#myTable2%')

BEGIN

    DROP TABLE #myTable2;

END

CREATE TABLE #myTable2(

                      dbName sysname,

                      logSize float,

                      spaceUsed float,

                      dbStatus int

                      );                   

GO

INSERT INTO #myTable

EXEC ('sp_msforeachdb @command1="use [?]; dbcc showfilestats"');

GO

                     

INSERT INTO #mytable2

EXEC( 'dbcc sqlperf(logspace)');

GO

SELECT

((SUM(usedExtents)*64)/1024) AS DatabaseUsedSpaceinMB

,((SUM(totalExtents)*64)/1024) AS DatabaseSizeinMB

FROM #myTable;

GO

SELECT

    DBName

    ,usedExtents

    ,TotalExtents

    ,((usedExtents*64)/1024) AS DatabaseUsedSpaceinMB

    ,((totalExtents*64)/1024) AS DatabaseSizeinMB

    ,(((totalExtents*64)/1024)-((usedExtents*64)/1024)) as DataFileFreeSpace

 FROM

    #mytable

 ORDER BY

    ((totalExtents*64)/1024) DESC;

 GO

SELECT

    *

FROM

    #myTable2

ORDER BY

    logsize DESC;

GO

DROP TABLE #myTable;

DROP TABLE #myTable2;

GO

SCRIPT2-Get Table Size Stats

/*============================================================

--@SQLBalls: SQLBalls@gmail.com

--http://www.SQLBalls.com

--T-SQL TUESDAY 16 Aggregation

--

--This Sample Code is provided for the purpose of illustration only

--and -is not intended to be used in a production environment. 

--THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS"

--WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING

--BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR

--FITNESS FOR A PARTICULAR PURPOSE.

==============================================================*/

IF EXISTS(SELECT * FROM TEMPDB.sys.tables WHERE name like '#myTable%')

BEGIN

    DROP TABLE #myTable

END

CREATE TABLE #myTable(

                               [Name] sysname,

                               [Rows] int,

                               [Reserved] varchar(500),

                               [Data] varchar(500),

                               [Index_Size] varchar(500),

                               [Unused] varchar(500)

                               );

INSERT INTO #myTable

EXEC ('sp_msforeachtable @command1="sp_spaceused [?]"');

GO

SELECT * FROM #myTable

ORDER BY

    [rows] DESC,

    LEFT([reserved],(LEN([reserved])-3)) DESC;

SELECT

    SUM(CAST(LEFT([reserved],(LEN([reserved])-3)) as int)) as [Total_Reserved(KB)]

    ,SUM(CAST(LEFT([data],(LEN([data])-3)) as int)) as [Total_Data(KB)]

    ,SUM(CAST(LEFT([Index_Size],(LEN([Index_Size])-3)) as int)) as [Total_IndexData(KB)]

    ,SUM(CAST(LEFT([Unused],(LEN([Unused])-3)) as int)) as [Total_UnusedSpace(KB)]

FROM  #myTable

GO

DROP TABLE #myTable;

  

Happy T-SQL Tuesday I hope you enjoy the scripts!

Thanks,

Brad