SQL Saturday 74 Jacksonville

Hello Dear Reader, If you're visiting looking for SQL Saturday Slide Decks and info please go to the Resources page, all info is there!  Just Click Here, and come back on Monday for my SQL Saturday Roundup!

Thanks,

Brad

Transparent Data Encryption & Backwards Compatibility

We were working with Chris Mitchell (Twitter) from the Microsoft Technology Center in Atlanta the other day, out of the Microsoft Office in Tampa.  The Office Has a Beautiful and very distracting view, see the picture below, of the Bay area, my friend Dan Taylor(blog|Twitter) took the photo.

One of those views that makes you love living in Florida!  

But I digress, we were having a really great conversation about features that are enabled or disabled when you enter a database into 80 Compatibility level, but are running on a 2008 R2 Instance.  

When having discussions you typically throw things out by features (or at least that is what I’ve heard people do), Database Compression will not work, Filestream will not work, Encryption will not work.  But when we brought up encryption I asked does that mean Transparent Data Encryption will not work.  Neither of us knew off hand.

I’ve presented on this topic so I’ve got some scripts on standby, so I opened one up fired it off, changed the Compatibility Level, and it still worked. 

“So Balls”, you say “Prove It.”

Dear Reader I wouldn’t have it any other way!

AND….. HERE….. WE…… GO!

First we’ll create our Demo Database

/*

Create TDE Database for demo

*/

USE master

GO

IF EXISTS(select * from sys.databases where name=N'TDE')

BEGIN

    DROP DATABASE TDE

END

CREATE DATABASE TDE

GO

When we use Transparent Data Encryption we need to First Create a Master Key and a Certificate in the Master Database.

/*

Create the Master Key for the

SQL Server

*/

USE master

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD='brad1'

GO

/*

Create the Certificate that will be used

for database level encryption

*/

CREATE CERTIFICATE DatabaseCertificate WITH SUBJECT='NunyaBiznes'

GO

Now that we’ve got those let’s alter our databases compatibility level to 80, SQL 2000.

USE TDE

GO

/*

Let's Alter Our Database

And Place It in 80,SQL 2000,

Compatability Mode

*/

ALTER DATABASE TDE

SET COMPATIBILITY_LEVEL = 80

Now let’s Create our table, and Insert some data.  You’ll see that I have default values set up to simulate an SSN. 

/*

Create Table for filler data

*/

IF EXISTS(SELECT * FROM SYS.tables WHERE name='tdeData')

BEGIN

    DROP TABLE dbo.tdeData

END

CREATE TABLE dbo.tdeData(

    ID int IDENTITY(1,1) NOT NULL

    ,nameText varchar(100) default 'fakeSSN'

    ,ssnText varchar(100) default '111-11-1111'

    ,fillerText char(5000) default 'a'

)

GO

/*

Create filler data for TDE demo

*/

DECLARE @i int

SET @i = 0

WHILE (@i < 15000)

    BEGIN

         INSERT INTO tdeData DEFAULT VALUES

         SET @i = @i +1

    END

I do this in Demo’s because I want people to see that when you insert data into a Data File or a back it up to a Backup File, the data is in plain text.  That is part of the reason you use TDE, because it adds an additional layer of protection.  So let’s backup our data Pre Encryption, and look at it in a Hex Editor.

Look at that there is our social security number 111-11-1111!  Now let’s double check our Compatibility Level. 

And now let’s enable encryption. We create a Database Encryption Key using our Certificate we made earlier, and specify our algorithm.  Then we set the database encryption to on.

/*

Create Database Encryption Key

*/

USE TDE

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE DatabaseCertificate

GO

/*

Set The Encryption On

*/

ALTER DATABASE TDE

SET ENCRYPTION ON

 We’ll use a DMV to validate the encryption process that SQL MVP Jasper Smith(blog) wrote that I’ve loved to use for years now.

SELECT

    CASE e.encryption_state

                WHEN 0 THEN 'No database encryption key present, no encryption'

                WHEN 1 THEN 'Unencrypted'

                WHEN 2 THEN 'Encryption in progress'

                WHEN 3 THEN 'Encrypted'

                WHEN 4 THEN 'Key change in progress'

                WHEN 5 THEN 'Decryption in progress'

    END AS encryption_state_desc,

      e.percent_complete,

    DB_NAME(e.database_id) AS DatabaseName,

         c.name as CertificateName,

            e.encryption_state

    FROM sys.dm_database_encryption_keys AS e

    LEFT JOIN master.sys.certificates AS c

    ON e.encryptor_thumbprint = c.thumbprint

   

You just execute this after you set Encryption On and watch the internal process work, the size of the database will determine how long it runs.  For a large database this is a great DMV to show you just how long the process is taking.  And now we are encrypted! 

So let’s do another backup and open that up in a Hex Editor to see if we can find our SSN.

And as you can see our file space looks quite a bit different, and we couldn’t find the string for our SSN

WHAT IN THE NAME OF PRO-WRESTLING DOES THIS MEAN!?

First off it means that if you have to keep your database in 80 Compatibility Level, you can still use Transparent Data Encryption to secure your backups and your Data Files at rest. Your log files will be encrypted too, but this occurs at a VLF, virtual log file, level.  So the full log will not be encrypted until all VLF's have been over written.

Some other pretty cool possibilities, I asked Paul Randal (blog|twitter) what part of the Storage Engine handled the Encryption and Decryption of pages. 

I know Access Methods handles Compression, because the pages are stored in memory in a Compressed State before being passed back to the Relational Engine.  But Pages are decrypted when stored in memory?  So I didn’t think it was the Access Methods. 

Paul confirmed that it was indeed the Buffer Manager that handles TDE. 

So that leads me to think some ROCKING things may be possible, if you know what the Buffer Manager IS COOKIN!

Thanks,

Brad

SQL Saturday 74 Jacksonville, Last Stop before SQL Rally

This Saturday is SQL Saturday #74 in Jacksonville, FL.  This will be the last time that I present on Data Compression in SQL Server, before SQL Rally in May.

This will be my first time in Jacksonville, but I’ve been told by many experienced SQL Saturday Alumni that the location is beautiful, and the event is always a good one.  We are on the University of North Florida Campus, in the College of Computing and Engineering Building, for the address Click Here. 

There is a great line up for this SQL Saturday.  You’ve got great Community Leaders & MVP’s like Kevin Boles (twitter), Andy Warren (blog|twitter), Plamen Ratchev (blog|twitter), Kendal Van Dyke (blog|twitter), Don Stevic (blog|twitter), Adam Jorgensen (twitter), Jose Chinchilla (blog|twitter), Troy Gallant (blog|twitter), Brian Knight , and many many more.  I also have it on good authority that this Balls fellow will be there.  For the full event schedule Click Here.

So far over 450 people have pre-registered to Attend, and you can be one of them the cost is A WHOLE LOTTA NOTHIN!  That’s right Dear Reader, the only cost is your time, you showing up and getting free training in SQL from the cast listed above, Click Here to Register.

This is also the last SQL Saturday in the US before SQL Rally, and I’m psyched. 

“But Balls,” you say, ”What if I’m not going to SQL Rally?”

Even more reason to attend!  Some of the presentations given today will be given at the Rally!  Not only that, but your feedback can help make the SQL Rally better.  

Every presentation I do I look for feedback to figure out if there is anything for my presentation that I’m missing or could be doing differently. 

So come out and enjoy a day of free training with your peers, discuss SQL, and if you find a place to leave feedback help influence the presenters to be better at what we do!

Thanks,

Brad

Cloudy With A Chance of SQL

We’ve all heard the phrases “The Cloud” and that Microsoft is “All in on the Cloud”. We manage SQL Server, we Develop on SQL Server, and we support SL Servers.  We make our living supporting a product that our companies use that was made by Microsoft.  Some are predicting that the Cloud will change the world, and plenty more just don’t buy it.

“So Balls,” you say,”As DBA’s what does this Cloud thing mean for us?” 

Well Dear Reader it means learning something new, which is always a good thing.   The Cloud offers us SQL Azure.

WHO IS THIS SQL AZURE, AND WHO SAID HE COULD TAKE MY BIRD SEED DATABASES

 SQL Azure is a totally different version and build of SQL, depending on the release you could get a different version number but executing SELECT @@VERSION should return 10.25.****.

SQL Azure by default disables DMO’s that work below an instance level.   You set up a SQL Azure Server, which is a logical container similar to a SQL Instance.  However, this is not the type of server that you would create Multiple instances on.  Your database will be automatically replicated to three separate locations, load balanced, and supported by High Availability. 

While those features are all sound pretty sweet, there are quite a few things that you cannot do, to see the list click here.

One thing that won’t be going out of style with anyone that uses Azure is from the Database on up.  By that I mean Indexing, Performance Tuning, and general Database Design.

NOTHING TO SEE HERE

I think one of the biggest drawbacks to the cloud is that you cannot look at the DMO commands or Perfmon for anything at a server level.  If an application starts to have a hiccup, the database and its performance normally come in to question at some point.  Having access to Server level objects helps us to know things are doing good, or not so good.

Then again, Azure is something new.  First we don’t always virtualize it.  Second if we virtualize it, we don’t cluster and replicate it to three different locations.  Third we don’t LOAD BALANCE it.  

So if we did have access to the server, there may be a lot of new things we would have to teach ourselves.   Personally I’d like that opportunity, but that could just be me.  

So we’ve got to Trust Dear Reader that everything is going just fine with the servers.  I’m sure there is a support staff we could contact if need be, but not having done that before I could not tell you the cost nor how to do it.

SQL VS SQL

So let’s take a look at some of the Pro’s and Con’s to this

Pro’s

Time Savings – You won’t be setting up database jobs, no SQL Agent, no Maintenance Tasks

HA/DR- Your High Availability is built in, replicated 3 times, Clustered, Load Balanced, and if one of your Virtual Machines Fails, another will be spun right up.

Hardware-You can stand up Hardware much faster, you don’t have to cost out the depreciation of hardware, you are charged by the usage.  Grant Fritchey (Blog|Twitter) wrote about the Cost of SQL Azure, go read that for more great info!

SLA’s-Microsoft has been working with Internet Facing Applications for quite some time, cough cough MSN, Hotmail, Microsoft.com, Technet, I hope you get the point.  They have published SLA’s that guarantee 99.95% uptime.

Security-When you go to MVA to learn about Azure there is a Security Model, that show’s how Microsoft is going above and beyond to provide a secure data center experience.

Con’s

Server Access- As we discussed before, your trouble shooting will only go so deep, you won’t be able to look at his for yourself.

No SQL Agent- Okay I get you want to take server management because you’re doing that, but how do I defragment my Indexes, what if I want to run a batch script, or have a job to update statistics.

Trust- When my boss says take a look at that SQL Server and see if anything is wrong, I’m limited, unless I involve boots on the ground that I don’t have a current working relationship with.  This will probably get tackled over time, but it does take time.

Lack of features- I posted that earlier, but there are things like XML, Filestream, and Encryption that some applications will need to have.

“So Balls”, you say “Do we use this or what?”

The places I see this being of most benefit, if you have a business group that needs to move fast, you can have a SQL Server, or app servers for that matter, set up in the blink of an eye and the swipe of a credit card.   I can see some places this will work great.  And I can see some places this will not.  I’m going to Total Recall it here Dear Reader, you’ve got to decide on your own, and like just about everything else SQL “It Depends”.

GO LEARN SOMETHING ALREADY!

Right now the Microsoft Virtual Academy is offering free training courses in Cloud technologies.  I would recommend you sign up and take the courses.  There are video’s, PDF’s and quizzes on Introduction To SQL Azure, Introduction to Windows Azure, Planning, Building and Managing a Private Cloud, and Windows Azure Security Overview.

 (By the way everyone end's up being #1 when you take All the tests, while I'd love to be super special you'll get to be #1 as well)

I’ve already taken my training and it was well spent time!  Go and learn something!

Thanks,

Brad

Let's Talk About Indexes: An Introduction

A very good friend of mine, whose got this kick-ass job working for the President J, wrote me a question about indexes.  And I thought how better than a phone call and a follow up blog talking about indexes in general.  The question centered on the uses of Clustered Indexes vs. Non-Clustered Indexes and the use for each.

CLUSTERED vs NON-CLUSTERED

The good news is this really isn't a VS kind of thing.  Clustered Indexes and Non-Clustered Indexes are both indexes.  They are both made of of an internal B-Tree structure.  

When you think of a B-Tree, think of your standard Hierarchy.  There are 3 named levels to a B-Tree structure.  The Root Level which is our top level and contains values that direct a path towards the next level, the Intermediate level and just like the Root Page points to the next level of Data, and finally the Leaf Page.

One of difference between a Clustered Index and a Non-Clustered Index is that a Clustered Index physically sorts the Data, based off of the Clustered Index.  Another difference is you can only have one Clustered Index per Table, but you can have multiple Non-Clustered Indexes.  The Clustered Index is most commonly thought of as a Primary Key, (the Clustered Index doesn't have to be the Primary Key, but that could get a little confusing so we'll save that for another day).

So we'll say that our B-Tree up above is using the letters of the Alphabet as it's Clustered Index.  I'll issue a quick T-SQL Select to get our letter.

SELECT

    *

FROM dbo.alphabet

WHERE

    letter='G'

If I wanted to go get the letter G, at the Root Level I deduce that G comes after A and before J so I would take a pointer and go to the Intermediate level that contained A, C, F, and H, From there I would further deduce that G is after F and before H, and I would travel down to our Leaf Page where I would find the letter G.

*IF your query's first Search Argument is the Clustered Index, and your range of values are narrow, then you will use your Index structure in a manner called a Seek.  If you have to read the entire table to get the contents of your information back, it is called Scan.  Scan's have their place and are not necessarily a bad thing.  But it is entirely dependent on the amount of data being returned, and the route taken to search for it.

One of the best examples I can think of to describe what an Index does, and further illuminate our B-Tree Structure, and show how  Clustered Indexes and  Non-Clustered Indexes  work together is the good old Phone Book.  If I told you to find Bradley Ball's address in the phone book you've got 2 ways of finding my name.

1. Turn through the book 1 page at a time until you get to Bradley Ball, and then read his address.

2. Flip to the B's (our Clustered Index) and scan until you get to Ball.  Now Scan Ball for the first name of Bradley (our Non-Clustered Index), and find the address.  You just performed a Seek, using the search arguments of Last Name and First Name to find Address.  

So now let's toss out a little vocabulary, to help further this discussion along.

HEAP- A heap is a table that has no clustered index.  Data is stored on a head in the order that it is inserted.  This means it is easily fragmented, and could require scanning as selecting data would require a full scan of the heap in order to find it.

CLUSTERED INDEX- A Clustered Index is a Unique value, that determines the physical sort order of the data as it is stored on a page.  The leaf level of a Clustered Index is the data of the table.  Clustered indexes have a limited key size of 900 bytes.

NON-CLUSTERED INDEX- A Non-Clustered Index is an index placed on a row in a Table, the leaf level of a Non-Clustered Index contains the data that makes up the Non-Clustered Index, as well as a physical pointer to the leaf level of the Clustered Index.  You can have many Non-Clustered Indexes on a Clustered Index Table, or a Heap.  Non-Clustered Indexes have the same 900 byte limitation and are limited to 16 columns, you can get around this using Include Columns.

"So Balls", you say, "Indexes sound great, we should put them on EVERYTHING! Right?"

I'm Glad you asked Dear Reader, and the response, regarding OLTP systems, is NO.

BALANCING ACT

Every Index that you place on a table has a cost of additional overhead.  You want to use them to speed up your database, but if you're not careful you could grind it to a halt.  

If you have a table with a Clustered Index, when you insert and update pages, and GROW the data, you are increasing your over head, slightly and it's worth it, to make sure that your Root and Intermediate level pages have the correct values to point to your leaf pages.

When you add a Non-Clustered Index, every time you insert a value for that Index you write to your table AND your Non-Clustered Index.  So if you Add another, and another, and another, then suddenly every time you write 1 record to your table, you could have many, many, many writes to keep all of your Indexes updated.

One of the first things I look at when I get a call about a "performance" problem on a database is to look at the indexes.  I've had instances where I have found tables with more indexes than columns.

COVERING INDEXES

The final high level topic that I want to talk about is Covering Indexes.  A Covering Index is an index that satisfies all of the return requirements of a query.  You could take all of the fields that you want to return, limit 16, in a query and if you add them to a single Non-Clustered Index you can do so.

One way to make a covering index is to add each column to the Non-Clustered index, another is to add additional columns as Include Columns.  Include columns allow you to get around the 900 byte limitation of an index, because Include columns are not stored as key values.  There are some data types that are not allowed to participate in Included Columns, Text and Image datatypes.  You can have 1023 Included columns on an index.

I just want to point out thought that, 1023 should not be a goal.  Every value you add, will add additional overhead to your leaf level Non-Clustered index.  Be sure to take baselines before and after, in order to know if your Index changes are having a positive affect on your query performance.




Thanks,

Brad

Don't Be Afraid, Not Being The Smartest Person In The Room

"I don't have to be the smartest person in the room". I once didn't take a job because of this phrase.  It was probably one of the best things that ever happened to me and it worked out quite well for a friend of mine.  But I'm getting ahead of myself, let me tell the story from the beginning.

DON'T BE AFRAID

A little philosophy first.  I'm not going to go Ayn Rand or Immanuel Kant on you, but as Louis Davidson (Blog|Twitter) would say, "We're drinking my flavor of Kool-AID today.

I don't have to be the smartest person in the room.  I don't, just don't need to.  Sometimes you will be, sometimes you won't.  If you don't worry about it, then it takes a lot of pressure and stress off.

"But Balls", you say, "Why are you afraid of being smart!?"

Ahh but that's just it Dear Reader, I'm not Afraid.

We've all been in the room with someone that THINKS they know everything.  We've all worked with someone, that just always had to be right.  Have you ever worked with someone like this on the day they were wrong?  

I have a better question would you want to be them on the day they are proven wrong?  No I wouldn't either.  If you always HAVE to be right, then being wrong is a crushing blow.

People can be afraid of a lot of things.  They can let themselves be ruled by fear, fear that your boss won't listen, fear that you don't know as much as the guy sitting next to you, Fear of Not Being the Smartest Person in the room.

I've seen this back fire on so many people that I've always wanted to take a different approach.  I find that you never know where the best idea will come from on a team.  It could be the developer that has been written off as lazy, that has a great out of the box idea.  It could be the obnoxious DBA that has some insight into the best way to layout a project's architecture.  It could be a Server Engineer that has really good insight on code management and migration.

If you make yourself open to the possibility that everyone has something to contribute, then you don't close yourself off to ideas.  And again you take a lot of weight off of your shoulders.

ROOM TO GROW AND STRETCH

A couple jobs ago I was looking for a new position.  I had reached what I felt was a ceiling.  I had been a Sr. Developer, mentored others, gotten my SQL Certifications, and filled the roles of a Sr. DBA & Sr. Developer.  I had worked on some cool projects, and really loved the people I had worked with.  

However, I wanted to grow and we were a very top heavy team.  A lot of people had been with the company for years, many had their Masters in IT or MBA's, MCAD's, and MCSE's brilliant people that I'm still very happy to call friends.  But with a great experienced group the only way to move into the full time job I wanted was for someone to leave.

HE'S NOT THE MIZ BUT HE'S AWESOME!

So I struck out to find greener pastures.  One particular job had advertised itself as Sr. DBA position.  I applied, and found that the person leaving the job was Awesome.  The company hated to loose Awesome, the client that Awesome served was thrilled with them, and Awesome had no desire to go but was moving to follow his wife's career.

We met several times over a month, and I went on-sight to meet the client.  The more I learned about the position, the more it didn't feel like a good fit.  I found that the only reason they wanted a DBA and were requiring an MCITP for SQL was because Awesome had that and they wanted Awesome II.  It was more of an Analyst/Team Lead position.

While meeting the client, I was asked to explain how I would describe myself as a Manger.  And I said "I'm not afraid, not to be the smartest person in the room.  I think everyone brings something to the table, I like to listen, I like to consider what the boots on the ground have to say, I don't think that my position entitles me to ideas that are more brilliant than anyone else, and as a Manager I'm not afraid to cheer someone on who's smarter than I am in a subject.  If I find someone smarter than I am, I want to learn from them, so I can be as educated as possible on the subject at hand."

I'M THE BEST AT WHAT I DO EVEN THOUGH WHAT I DO ISN'T VERY NICE IS BE A DBA

To be honest I thought it was a pretty good answer, and I'm still satisfied with it.  When we left the client site, the person who would have been my future manager, tore into me. 

I believe the coversation started out with "I DON'T KNOW WHAT IN THE HELL THAT SHUCK'S GOOD OLD BOY, HUMBLE CRAP WAS......" Needless to say I was taken back.   But I stuck to my guns, which probably only served to enrich the colorful nature of the rest of our conversation.  

The remainder of the conversation centered on how I always, ALWAYS, needed to be the smartest person in the room.  As I explained a little earlier, this flies in the face of how I work.  You can take a job where you compromise yourself, but I can't see how you can do that and be happy.

I told my wife later, that had I already taken the job the tone of the conversation would have left me worried that I would have been fired.  We talked about it that evening, and even though it would have been a significant raise, SIGNIFICANT RAISE, I asked if she would be alright with me passing.  My very wise and beautiful wife replied by saying that she didn't know why I was still even considering it.

IT ALL WORKED OUT

The day before I decided to pass a brilliant friend of mine, Perfect, found themselves without a job.  When I had worked with Perfect previously, Perfect had been an Analyst that had been a Team Lead over Developers, and was a perfect for this job.

I got Perfect's resume, and called and informed the recruiter I had been dealing with that I wouldn't be taking the job replacing Awesome.  Despite protests, I told them it may not seem like it, but I've got a person for this job whose Perfect.  I didn't have any hard feelings.  I wasn't the right person, and they weren't the right fit for me.  But I was going to send them Perfect's resume, and they should set up a meeting as soon as possible.

Perfect still works there, Perfect loves their job and is very successful.  And me, I found a little job up in D.C. working for the President, that led me to the Great State of Florida where I was very happy to land.

I guess my only parting advice Dear Reader would be don't be afraid of a someone who is smarter than you.  Embrace them, learn from them, and work hard.  At the end of the day how can anybody be unhappy with learning something new, and making themselves better.

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