Brent Ozar Unlimited - Today's posts from Brent Ozar Unlimited for 05/19/2017

Today's posts from Brent Ozar Unlimited for 05/19/2017
Here's our latest blog post.
Is this email not displaying correctly?
View it in your browser.
Erik Darling says:

What Do You Want to See in a Free Query Store Tool?

I love Query Plans

No seriously, I love digging into query plans. Even writing XQuery to dig into them in sp_BlitzCache. I was both honored and horrified to take over development of it from Jeremiah.

It’s a beast, but it’s my kind of beast. At around 4500 lines of code, when you want to make a change, well, &^%$ happens sometimes.

It used to make sense

When the plan cache was the only bar in town, that’s where you clinked your glasses.

It was okay that sometimes it was packed and sometimes it was dead and sometimes they were out of what you wanted to order.

With the advent of the Query Store, it was like, well, the plan cache got gentrified.

You didn’t have to worry about restarts anymore, there was some GUI love, and much more in the way of information persisted to views that you used to have to go gnarling through XML to pull out if you wanted to do any searching on your own.

All that XML processing was slow and expensive, too. I had a couple copy/paste incidents with Code From The Wild© on servers I was desperately hoping to find some problems on (in the process I probably created some problems).

Kick it around and talk some more

I’d been planning, and I’ve actually started and decided I hated, several iterations of a stored procedure to go at Query Store. Due to Other Things® coming up and all the usual excuses (not to mention the perilously low adoption rates for versions of SQL that actually have Query Store on them — we just don’t have much 2016 work coming through the door) I kept putting it off.

But then vNext/2017 happened.

And that damn Wait Stats DMV.

And now I’m just a little too excited about getting something written.

But I’d love to hear from you, first.

What questions would YOU like to ask the Query Store?

Best Laid Plans

My vision for the moment is to bring a lot of the same information and warnings out of plans, and probably the same options for sorting (CPU, reads, etc.), but with the opportunity to ask some more interesting questions up front.

This doesn’t mean sp_BlitzCache is going away. As long as there’s a plan cache, and people using versions of SQL prior to 2016, well, there will probably be a need for it. At least until they’re all out of support. Ho ho ho, ha ha ha.

It also gives me a chance to tame some of that absolutely insane dynamic SQL.

So, please, if there’s anything out there you’d like to see, figure out if it’s possible, whatever, leave a comment.

I’ll be here forever, writing XQuery and dynamic SQL, so you don’t have to.

Register for GroupBy June to see sessions on JSON, containers, columnstore, CI, and more.

Read the comments.

Erik Darling says:

Do SQL Server 2017’s Adaptive Joins Work with Cross Apply or Exists?

I think I’ve mentioned that the most fun part of new features is testing them with old ideas to see how they react.

It occurred to me that if Adaptive Joins didn’t work with APPLY, I might cry.

So, here goes nothin’!

Cross

Simple Cross Apply…ies can use Adaptive Joins, though at first glance there’s nothing special or different about this plan from the regular Inner Join version.

SELECT u.Id, ca.Score
  FROM dbo.Users_cx AS u
CROSS APPLY (
  SELECT p.Score
  FROM dbo.Posts AS p
  WHERE  p.OwnerUserId = u.Id
  AND u.LastAccessDate >= '2016-12-11'
) ca

Backwards pants

I do have to point out that Cross Apply used to only be implemented as a Nested Loops Join. I learned that many years ago from one of the best articles written about Cross Apply by Paul White. That changed recently — it’s possible to see it implemented with a Hash Join in at least 2016. I’ve seen it crop up in Cross Apply queries without a TOP operator.

It may have been in previous versions, but… Yeah. ColumnStore before 2016.

Anyway, the Cross Apply with a TOP operator does appear to skip the Adaptive Join and favor Parallel Nested Loops, as you can see in this particularly Machanic-y query.

SELECT u.Id, ca.Score
  FROM dbo.Users_cx AS u
CROSS APPLY (
  SELECT TOP 2147483647 p.Score
  FROM dbo.Posts AS p
  WHERE  p.OwnerUserId = u.Id
  AND u.LastAccessDate >= '2016-12-01'
) ca

PNL4LYFE

Outer

Outer Apply suffers a rather gruesome fate, where neither implementation gets an Adaptive Join.

SELECT u.Id, ca.Score
  FROM dbo.Users_cx AS u
OUTER APPLY (
  SELECT p.Score
  FROM dbo.Posts AS p
  WHERE  p.OwnerUserId = u.Id
  AND u.LastAccessDate >= '2016-12-11'
) ca

SELECT u.Id, ca.Score
  FROM dbo.Users_cx AS u
OUTER APPLY (
  SELECT TOP 2147483647 p.Score
  FROM dbo.Posts AS p
  WHERE  p.OwnerUserId = u.Id
  AND u.LastAccessDate >= '2016-12-11'
) ca

No one likes you anyway

Sad face. I still haven’t gotten anything to show up in my Extended Events session for why Adaptive Joins were skipped for certain queries.

 

Exists

Exists not only doesn’t get an Adaptive Join, but… MY EXTENDED EVENTS SESSION FINALLY SORT OF TELLS ME WHY!

I mean, it doesn’t make any sense, but it’s there.

Like your alcoholic aunt.

SELECT u.Id
  FROM dbo.Users_cx AS u
WHERE EXISTS (
  SELECT 1
  FROM dbo.Posts AS p
  WHERE  p.OwnerUserId = u.Id
  AND u.LastAccessDate >= '2016-01-01'
)

I’m going to skip showing you a non-Adaptive plan, because you’ve seen enough of those.

Here’s what the Extended Events session shows. The query in the XE session and the query I ran are slightly different because I was testing different permutations (I paid like $7 for that word) to see if it made any difference.

Ho hum.

Got that? eajsrUnMatchedOuter.

I’m going to say that the next time a bartender tries to cut me off.

Not Exists

Would you believe that Not Exists gets an Adaptive Join, but Exists doesn’t?

SELECT u.Id
  FROM dbo.Users_cx AS u
WHERE NOT EXISTS (
  SELECT 1
  FROM dbo.Posts AS p
  WHERE  p.OwnerUserId = u.Id
  AND u.LastAccessDate >= '2016-12-01'
)

And… would you believe that there’s a bug in the query plan that makes the lines all woogy?

Fire Hydrant

Sir, I’m going to need you to step out of the vehicle.

Territory

I just love looking at and experimenting with this stuff! I hope you’ve been enjoying reading about it.

Thanks for still doing that!

Register for GroupBy June to see sessions on JSON, containers, columnstore, CI, and more.

Read the comments.

Copyright © 2017 Brent Ozar Unlimited®, All rights reserved.
We're sending you this because you signed up for the BrentOzar.com Community. If you no longer want these community recaps, please unsubscribe. We're all about staying at Inbox Zero too.
Our mailing address is:
Brent Ozar Unlimited®
9450 SW Gemini Dr
ECM #45779
Beaverton, OR 97008

Add us to your address book

About Brent Ozar Unlimited

Brent Ozar Unlimited's highly specialized experts focus on your goals, diagnose your toughest IT pains, and prescribe remedies.