Product has been added to your cart.

SQL Server

How Scalar Functions Can Stop You From Getting Adaptive Joins

Last Updated May 1, 2021

Adaptive Joins, Execution Plans, SQL Server 2017

If you’re not aware of the performance problems scalar valued functions can (and often do) cause, well, uh… click here. We’ll talk in a few days. If you are, and you’re worried about them crapping on Adaptive Joins, follow along. The big question I had is if various uses of scalar valued functions would inhibit…

SQL Server 2017: Interleaved MSTVFs Vs Inline Table Valued Functions

Last Updated May 12, 2017

Adaptive Joins, Execution Plans, SQL Server 2017

But is it faster? Now, I know. There are very few “always” things out there in SQL Server. This is also true for functions. A lot of the time — I might even say most of the time, inline table valued functions are going to be faster that scalar and multi statement table valued functions.…

SQL Server 2017: Interleaved Execution for MSTVFs

Last Updated May 15, 2017

Execution Plans, SQL Server, SQL Server 2017

What I don’t want you to take away from this Is that I want you to start using Multi Statement Table Valued Functions all over the place. There are still problems with them. Backed by table variables Lots of hidden I/O cost Number of executions may surprise you One important current limitation (May-ish of 2017)…

[Video] Office Hours 2017/05/10 (With Transcriptions)

Last Updated May 13, 2017

Brent Ozar Unlimited Team

SQL Server, Videos

This week, Tara and Richie discuss increasing the size of tempdb, splitting database across several files, tracking DML changes to a table, upgrading different versions of service packs, recommendations for improving T-SQL skills, trace flags, replication, Always On Availability Groups, compatibility level of databases, and more… Here’s the video on YouTube: You can register to…

Breaking: SQL Server 2016 SP1 CU2 Supports ENABLE_PARALLEL_PLAN_PREFERENCE Hint

Last Updated May 13, 2017

Execution Plans, SQL Server, SQL Server 2017

Hot Diggety! After reading an excellent tip by Dmitry Pilugin about the new hint for forcing a parallel plan in SQL Server 2017, I figured I’d test it out on SQL Server 2016’s latest CU to see if it had been back ported yet. Good news! It has. Obviously. That’s the title of the blog…

SQL Server 2017: Statistics Information Comes To Query Plans

Last Updated May 15, 2017

Execution Plans, SQL Server 2017, Statistics

Warm fuzzies I think this is how my mom would have felt if I went to college. You see, once you stop cargo culting around index fragmentation, and disk queue length, you start to realize which things are actually important to query performance. Things like statistics, and cardinality estimation. And, sure, having the right indexes…

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

Last Updated May 7, 2017

Brent Ozar Unlimited Team

SQL Server, Videos

This week, Brent, Tara, and Richie discuss choosing batch sizes, SQL 2017 on Linux, how much memory to leave on the OS for the SQL server, parameter sniffing, allocation unit size of SQL Server, triggers, X-Files, aliens and much more! Here’s the video on YouTube: You can register to attend next week’s Office Hours, or subscribe to…

Adaptive Joins And SARGability

Last Updated May 12, 2017

Adaptive Joins, SQL Server 2017

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

Adaptive Joins, SQL Server 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

Adaptive Joins, SQL Server 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

Adaptive Joins, SQL Server 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…

Pocket Square

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

Last Updated April 30, 2017

Brent Ozar Unlimited Team

SQL Server, Videos

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

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

Brent Ozar Unlimited Team

SQL Server, Videos

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…