SQL Server

There’s a famous saying Non-SARGable predicates don’t get missing index requests. And that’s true! But can they also stifle my favorite thing to happen to SQL Server since, well, last week? You betcha!© One Sided I’m going to paste in some queries, each with something non-SARGable. There are two tables involved: Users and Posts. Only…

Adaptive Joins And Local Variables

Last Updated May 12, 2017

With new features I really love kicking the tires to see how they work with existing features, and if they fix existing performance troubleshooting scenarios. One issue that I see frequently is with local variables. I’m not going to get into Cardinality Estimator math here, we’re just going to look at Adaptive Join plan choice…

Anatomy Of An Adaptive Join

Last Updated May 12, 2017

I don’t like it unless it’s brand new When new features drop, not everyone has time to jump on top of them and start looking at stuff. That’s what consultants with nothing better to do are for. I’ve been excited about this feature since talking to The Honorable Joseph Q. Sack, Esq. about it at…

The 2017 Adaptive Join Optimization Eats Bad TSQL For Breakfast

Last Updated May 12, 2017

Cheeky If you thought that title sounded familiar, you sure were right. That’s called a classical reference. So here we are, three whole years later, and yet another improvement to the engine promises to fix performance issues forever and ever. While this isn’t exactly an entirely new cardinality estimator, it’s an entirely new branch in…

First Responder Kit Re-Release: The Hubris Of Titles

Last Updated May 2, 2017

First Responder Kit, SQL Server

The Gods of the Copy Book Headings Frowned Upon Me So yesterday, I pushed out a new FRK. You could say I FRKed up, and you’d be right. Not so much See, before I push stuff to master, I do a round of testing in our AWS lab to find version-specific bugs and make sure…

First Responder Kit Release: Ça plane pour moi

Last Updated May 1, 2017

First Responder Kit, SQL Server

Ah, May. Spring has sprung, and young people’s minds turn towards seasonal allergies. Special thanks to @digitalohm for the Differential Restore code and general sp_DatabaseRestore spiffiness. May you live to an age that can only be described in Unix. You can download the updated FirstResponderKit.zip here. sp_Blitz Improvements #857 Do you have any idea how much…

[Video] Office Hours 2017/04/26 (With Transcriptions)

Last Updated April 30, 2017

This week, Erik – all by his lonely self – talks about troubleshooting remote queries, his thoughts on SQL Server 2017, licensing, index rebuilds, index fragmentation, why you shouldn’t restart SQL server every day, and much more! Here’s the video on YouTube: You can register to attend next week’s Office Hours, or subscribe to our podcast to…

Look Ma, Adaptive Joins

Last Updated May 12, 2017

Execution Plans, SQL Server 2017

This probably won’t seem like a big deal soon But I just got the optimizer to pick an Adaptive Join! It took a few tries to figure out what would cause some guesswork to happen, but here it is. I hope Joe Sack has strong ribs. And here’s the tool tip info! Dynamic Duo This…

SSMS 2017 Is Now Available For Download

Last Updated May 17, 2017

SQL Server

If you’re into that sort of thing You can head over here to download it. I just got it installed, and I’ll be updating this post as I find stuff. In case you’re curious, this is a whole new install, not just an update. I had to reinstall some stuff to get it to show…

Hold The FiIter: Startup Expression Predicates

Last Updated April 26, 2017

Development, SQL Server

Long distance information There was a rather interesting question posted on dba.stackexchange.com recently about CASE expression order of execution with an OR predicate. Mouthful, I know! When I saw it, I got all “I HAVE A REALLY GOOD ANSWER” and started to write demo queries. Then I wrote this blog post instead. Sorry, j.r. —…

[Video] Office Hours 2017/04/19 (With Transcriptions)

Last Updated April 22, 2017

This week, Brent, Tara, Richie, and Erik discuss SQL Server 2017 CTP 2, quorum configuration for a 4-node availability group, database sharding, premature optimization, parameter sniffing, tracking query plans within cursors, and what’s coming up in the new version of Paste The Plan. Here’s the video on YouTube: You can register to attend next week’s Office Hours,…

SQL Server 2017: query_store_wait_stats

Last Updated May 12, 2017

Query Store (QDS), SQL Server 2017, Wait Stats

The missing link Back when the Query Store first got announced, I was amazed. The amount of information it gathered and persisted between reboots was such a big improvement over the plan cache, plus being able to force plans was really cool. I was always surprised that wait stats weren’t a part of that, especially…

SQL Server 2017: Unpublished Gems

Last Updated May 12, 2017

SQL Server 2017

Sneaky, sneaky, sneaky Release notes never seem to tell the full story, do they? At some point, you have to take matters into your own hands and go hunting for, as Paul Harvey would say, “The Rest Of The Story”. So what did I find? New stuff! Brand. New. Stuff. For you. If you ever…

SQL Server 2017: Quick Rundown

Last Updated May 12, 2017

SQL Server 2017

ICYMI Playing with SSMS in the lab Microsoft released CTP 2 of SQL Server vNext (well, yesterday today, but who knows when this thing will hit?). There’s some great new stuff in the Engine in this release that I’m just crazy about getting to know better. Going through the Release Notes made me feel things…

[Video] Office Hours 2017/04/12 (With Transcriptions)

Last Updated April 15, 2017

DISCLAIMER: this week, we learn that a naked Erik Darling looks like a bear covered in squid ink. Thankfully, we don’t learn that via the webcams. Aside from that, Brent, Richie, and Erik also discuss updating stats and rebuilding indexes, feature request for Paste the Plan, included columns’ effect on DML, CLR store procedures, SQL command and…

SQL Server 2017 Release Date: May 31?

Last Updated May 12, 2017

Brent Ozar

SQL Server 2017

At the SQLbits conference in the UK, Victoria Holt sat in on a session about the State of the SQL Nation. She writes: “SQL Server vNext will be launched this year with the UK launch 31 May 2017.” Keep in mind that “launch” is a marketing term, very different from “release.” (Even “release” is tricky…

[Video] Office Hours 2017/4/5 (With Transcriptions)

Last Updated April 10, 2017

This week, Erik, Tara, and Richie discuss altered tables and update statements, parameter sniffing issues, their issues with Azure, a resource for column store indexes, AlwaysON availability groups, killing sleeping connections, and fill factor value for indexes. Here’s the video on YouTube: You can register to attend next week’s Office Hours, or subscribe to our podcast to…

First Responder Kit Release: Now With 100% More Working Code Than The Irish Space Program

Last Updated April 7, 2017

First Responder Kit, SQL Server

Every April I think about this girl I grew up with named April who smelled like Cheez-Wiz. She died tragically in a home for the blind on Taco Tuesday. Special thanks to @jeffrosenberg , @mrthomsmith , @rwhoward for contributing this month. Your complimentary oxygen is now available. The rest of you can go download the First…

[Video] Office Hours 2017/03/26 (With Transcriptions)

Last Updated April 1, 2017