performance tuning

SQL Server 2017: Interleaved MSTVFs Vs Inline Table Valued Functions

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.…
Read More

SQL Server 2017: Interleaved Execution for MSTVFs

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)…
Read More

Breaking: SQL Server 2016 SP1 CU2 Supports ENABLE_PARALLEL_PLAN_PREFERENCE Hint

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…
Read More

SQL Server 2017: Statistics Information Comes To Query Plans

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…
Read More

The 2017 Adaptive Join Optimization Eats Bad TSQL For Breakfast

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…
Read More

Hold The FiIter: Startup Expression Predicates

Long distance information There was a rather interesting question posted on 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. —…
Read More