SQL Server 2017 Archives - Page 2 of 2

Performance Tuning

SQL Server 2017: Interleaved Execution for MSTVFs

May 15, 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

Performance Tuning

Breaking: SQL Server 2016 SP1 CU2 Supports ENABLE_PARALLEL_PLAN_PREFERENCE Hint

May 12, 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 post.
Puddin'
Here are the test queries I ran.
[crayon-6a883c656b283990642328/]
And here are the boring results!

Performance Tuning

SQL Server 2017: Statistics Information Comes To Query Plans

May 12, 2017

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.

Adaptive Joins And SARGability

May 5, 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?

Adaptive Joins And Local Variables

May 4, 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

May 3, 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 PASS last October. My pupils dilated like I just found the bottom of a bottle of Laphroaig 18.

The 2017 Adaptive Join Optimization Eats Bad TSQL For Breakfast

May 2, 2017

Cheeky
If you thought that title sounded familiar, you sure were right.

That's called a classical reference.

Performance Tuning

Look Ma, Adaptive Joins

April 26, 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.

And here's the tool tip info!

Performance Tuning

SQL Server 2017: query_store_wait_stats

April 22, 2017

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.

SQL Server 2017: Unpublished Gems

April 21, 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 upgrade, lazybones.

Hidden in SQL Server 2017 CTP v1.1: sys.dm_db_stats_histogram

December 19, 2016

It's Friday night, so I'm waiting for new CTP Releases
As soon as I got the email, I started reading the release notes. Some interesting stuff, of course.

Batch mode queries now support “memory grant feedback loops,” which learn from memory used during query execution and adjusts on subsequent query executions; this can allow more queries to run on systems that are otherwise blocking on memory.