T-SQL Tuesday 18: Common Table Expressions AKA CTE’s

T-SQL SQL Tuesday is brought to us this week by Bob Pusateri (blog|@SQLBob).  So Bob what is the topic this month?

“Have you ever solved or created a problem by using CTEs? Got a tip, trick, or something nifty to share? I’d love to see your posts about any of the above. Also don’t forget that T-SQL Tuesday is not limited to only T-SQL:”

So we aren’t limited to CTE’s but I do enjoy them, so off we go!

HOT FUZZ FIZZ BUZZ

I first learned about Common Table Expressions while studying for the 70-432 exam back in 2007.  They were neat, they were cool.  They have a lot of power when it comes to recursion.  The first time I used them in any practical sense was in a job interview.  I was asked the Fizz Buzz question.

I remembered reading a blog Rob Boek (blog|@robboek) had written about this exact same question, read it here, Fizz Buzz by Rob Boek. 

I thought it was simple. I thought it was elegant.  It was a great example of Set Based logic.  I was trying to make a good impression, and so I re-created it.  I was honest about it, when asked why I came up with the solution that I had.  It led to a great conversation about how we study as IT professionals, and learn from others.

“So Balls”, you say “We’re going to talk Fizz Buzz?”

Nope but if you’re interested in CTE’s you should stop and go read it, were going to talk about one of my favorite topics Compression.

SIMPLIFY THE COMPLEX

Common Table Expressions can be used as an alias to complex queries.  The example I’m going to work with today is one from the White Paper on Compression, Data Compression: Strategy, Capacity Planning and Best Practices by Sanjay Mishra.  I use some of the Queries from this white paper, to detect Scan and Update Usage Patterns of Indexes, in my Presentation on Page & Row Compression.  If you’re in the Orlando Area you can come see that presentation this Friday at SQL Rally!

The queries look at the sys.dm_db_index_operational_stats DMF to gage the Scan and Update Patterns of Indexes.  This is important when you are determining a Compression Strategy. 

If you have a High Percentage of Updates then you will experience more overhead with Page Compression, than if you had a High Percentage of Scans.  A good example is an OLTP system vs. an OLAP system, Reference Data vs. Data that is constantly in motion.

The queries are not overly complicated, but getting the information you want requires a certain amount of massaging.  A CTE can help this process by providing a wrapper for a very complex query so you can work with the returning data in additional ways.

We’ll start out with our 2 original queries and then move to the CTE that I’ve put together.

Original Query to Detect Scans

SELECT

     so.name AS tableName,

     si.name AS indexName,

     ios.partition_number AS [Partition],

     ios.index_id AS indexID,

     si.type_desc AS indexType,

     (ios.range_scan_count *100.0/(ios.range_scan_count +

     ios.leaf_delete_count + ios.leaf_insert_count + ios.leaf_page_merge_count + ios.leaf_update_count + ios.singleton_lookup_count)) AS percentScan

FROM

     sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL) ios

     JOIN sys.objects so

     ON so.object_id=ios.object_id

     JOIN sys.indexes si

     ON si.object_id = ios.object_id AND si.index_id = ios.index_id

WHERE

     (ios.range_scan_count + ios.leaf_insert_count + ios.leaf_delete_count + ios.leaf_update_count +ios.leaf_page_merge_count + ios.singleton_lookup_count)!=0

     AND OBJECTPROPERTY(ios.object_id, 'IsUserTable')=1  

ORDER BY

     percentScan;

GO

Original Query to Detect Updates

SELECT

     so.name,

     si.NAME AS [indexName],

     ios.partition_number AS [Partition],

     ios.index_id AS [IndexID],

     si.type_desc AS [IndexType],

     (ios.leaf_update_count *100/(ios.range_scan_count +

     ios.leaf_insert_count +

     ios.leaf_delete_count +

     ios.leaf_update_count +

     ios.leaf_page_merge_count +

     ios.singleton_lookup_count)) AS [Update_Percentage]

FROM

     sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios

     JOIN sys.objects so

     ON so.object_id = ios.object_id

     JOIN sys.indexes si

     ON ios.object_id=si.OBJECT_ID AND ios.index_id=si.index_id

WHERE

     (ios.range_scan_count + ios.leaf_insert_count + ios.leaf_delete_count + ios.leaf_update_count + ios.leaf_page_merge_count + ios.singleton_lookup_count) !=0

     AND OBJECTPROPERTY(ios.object_id, 'IsUserTable') =1

ORDER BY

     [Update_Percentage] ASC;

GO

Both of these queries will get you the information you need, but if you want to see the results side by side you would need to perform a Union.  Further manipulation would require you edit both queries, and could get complicated.  This is where our CTE can help simplify things.

We’ll start out with our expression where we set the CTE name, the Data to be returned, and then we define our query.  Final we’ll have a separate select were we can manipulate our CTE and return the data as we see fit.

Our CTE

WITH IndexUsage(tableName, IndexName, [Partition], IndexId, IndexType, Usage, Percentage)

AS (

SELECT

     (ss.name + '.'+ so.name)

     ,si.NAME

     ,ios.partition_number

     ,ios.index_id

     ,si.type_desc

     ,'UPDATES'

     ,(ios.leaf_update_count *100/(ios.range_scan_count +

     ios.leaf_insert_count +

     ios.leaf_delete_count +

     ios.leaf_update_count +

     ios.leaf_page_merge_count +

     ios.singleton_lookup_count))

FROM

     sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ios

     JOIN sys.objects so

     ON so.object_id = ios.object_id

     JOIN sys.indexes si

     ON ios.object_id=si.OBJECT_ID AND ios.index_id=si.index_id

     JOIN sys.schemas ss

     ON so.schema_id=ss.schema_id

WHERE

     (

          ios.range_scan_count +

          ios.leaf_insert_count +

          ios.leaf_delete_count +

          ios.leaf_update_count +

          ios.leaf_page_merge_count +

          ios.singleton_lookup_count) !=0

     AND OBJECTPROPERTY(ios.object_id, 'IsUserTable') =1

UNION All

SELECT

     (ss.name + '.'+ so.name)

     ,si.name

     ,ios.partition_number

     ,ios.index_id

     ,si.type_desc

     ,'SCANS'

     ,(ios.range_scan_count *100.0/(ios.range_scan_count +

     ios.leaf_delete_count +

     ios.leaf_insert_count +

     ios.leaf_page_merge_count +

     ios.leaf_update_count +

     ios.singleton_lookup_count))

FROM

     sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL) ios

     JOIN sys.objects so

     ON so.object_id=ios.object_id

     JOIN sys.indexes si

     ON si.object_id = ios.object_id AND si.index_id = ios.index_id

     JOIN sys.schemas ss

     ON so.schema_id=ss.schema_id

WHERE

          (ios.range_scan_count +

          ios.leaf_insert_count +

          ios.leaf_delete_count +

          ios.leaf_update_count +

          ios.leaf_page_merge_count +

          ios.singleton_lookup_count)!=0

     AND OBJECTPROPERTY(ios.object_id, 'IsUserTable')=1  

)

SELECT

     iu.tableName

     ,iu.IndexName

     ,iu.IndexType

     ,iu.Usage

     ,iu.Percentage

FROM

     IndexUsage iu

ORDER BY

     iu.tableName

     ,iu.IndexName

     ,iu.Percentage desc

   

   

Despite the complexity of the internal statements, out final select is simple and easy to change and manipulate.

There’s an old phrase, “Work Smarter Not Harder”.  Using CTE’s is a good way to achieve the smarter.  I hope you find this helpful.

Thanks,

Brad

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