SQL Server Consulting, Education, and Training
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
This year at the PASS Data Summit, I’m presenting my precon The Foundations Of SQL Server Performance Tuning:
Whether you want to be the next great query tuning wizard, or you just need to learn how to start solving tough business problems at work, you need a solid understanding of not only what makes things fast, but also what makes them slow.
I work with consulting clients worldwide fixing complex SQL Server performance problems. I want to teach you how to do the same thing using the same troubleshooting tools and techniques I do.
I’m going to crack open my bag of tricks and show you exactly how I find which queries to tune, indexes to add, and changes to make. In this day long session, you’re going to learn about hardware, query rewrites that work, effective index design patterns, and more.
Before you get to the cutting edge, you need to have a good foundation. I’m going to teach you how to find and fix performance problems with confidence.
Goals:
Understand which metrics matter for performance
Using Extended Events and Query Store to find problems
Solving locking, deadlocking, and general query performance issues
A lot of the time when I teach performance tuning, I like to show attendees a specific query with some specific problem(s), and how I go about fixing those in different scenarios.
But a lot of the time when I talk to clients and other folks in the SQL Server community, they have a hard time even knowing where to start and what to look for.
In this session, I want to show you how I figure out what kind of shape a server is in, performance-wise, and how I use different clues to figure out which queries to go after.
Anyone can find a slow query; I want to find the queries that users complain about when their loading screens sit there spinning for a millennia. And that’s what I want to show you how to do.
Think of yourself like a conductor in front of an orchestra of totally untuned instruments. You may find some things:
All attendees will get a coupon code to my full training catalog.
Along with that, everyone loves these t-shirts, so I’ll be bringing them back.
I’m also going to be making a new one that is TOP secret, along with some spankin’ new stickers for you to slather your laptops with.
Only precon attendees will get one of these.
If you were thinking about coming to my @SQLBits precon but needed a bribe, how about a T-shirt? pic.twitter.com/Hj9VHK7PRq
— Erik Darling Data (@erikdarlingdata) February 10, 2022
Thanks for reading, and see you there!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
Like in yesterday’s post about Cost Threshold For Parallelism, changing MAXDOP settings will have a universal effect on the workload.
This is true whether you change it at the server level for all databases, or at the database level using a database scoped configuration for a single database.
It is a guardrail to prevent unwanted conditions as a whole, like excessive concurrent parallel queries causing worker thread starvation (THREADPOOL waits), or just pushing CPU to 100% for extended periods of time.
You may also find times when parallel threads within a single query are more prone to involvement in blocking or deadlocking.
The problem is similar to yesterday’s post, and requires some level of attention to detail at the query level after you change MAXDOP.
Don’t just look at all the various CX waits, pronounce that there are fewer of them, and call the job done.
You should examine parallel query metrics to make sure that runtimes are still adquate.
If we take a single query and run it at reduced DOPs, you can see the pattern I’m talking about.
Yesterday, we looked at DOP 8 vs. DOP 1, and I’m including a DOP 1 run here too. It’s just not as important, unless you’re the kind of wackadoo SharePoint admin who changes MAXDOP to 1.
(I’m going to be honest with you here, it’s been YEARS since I’ve seen a SharePoint database, or looked at the SharePoint documentation to see if the MAXDOP 1 requirement still exists).
When you change DOP, it’s up to you to monitor (you can use Query Store for this) execution and CPU time to look for big regressions.
Keeping the above picture in mind:
Taking a full three seconds longer at DOP 1 is probably out the door for queries that users care about, but that’s more likely from changing Cost Threshold For Parallelism.
A diligent caretaker for SQL Server may even look for critical queries that currently use parallel execution plans, and time them with a lower DOP to make sure there’s no substantial regression before making the change.
If there’s a big increase in duration, add the higher DOP hint to queries where it makes sense.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
First, I’m not suggesting that anyone should be using the default value for Cost Threshold For Parallelism. It’s old and moldy and not a good fit for most workloads functioning on modern hardware.
My apologies to Azure SQLDB users who can’t change this setting and leave it up to Microsoft to maybe manage it for them based on ???
Some people out there really like fiddling with settings in a usually ill-informed reaction to Some Script They Found On The Internet, without reading the fine print.
Constantly pushing Cost Threshold For Parallelism up higher because there’s “too much parallelism” is usually a bad strategy, for several reasons.
You should be looking at:
Just to put some easy numbers out there, let’s say you have a query that runs at DOP 8 and:
If you push Cost Threshold For Parallelism up to the point where that query no longer qualifies for a parallel plan and runs at DOP 1, it will:
See why this isn’t a bold and exciting strategy embraced by experts around the world?
Of course, in the real world, parallelism rarely scales perfectly linearly. That’s okay, as long as you’re seeing a reasonable reduction in wall clock time.
In this picture, I’ve captured the same query running at DOP 8 and 1.
What I said above holds close enough to true.
While we used more CPU time at DOP 8, we only used about 2.5 seconds more of it, and we used it for about 3 fewer seconds overall.
This is the efficiency tradeoff parallelism offers, and this is especially desirable for queries that have to process a lot of data.
Sticking with DOP 8, I’d rather process 10 million rows across 8 threads than 10 million rows on a single thread (especially in Row Mode, but it would be still be preferable in Batch Mode).
Anyway, if you’ve got Cost Threshold For Parallelism set to the default, you should probably bump it up. I still think 50 is a good starting place to get you to a spot where you can make sane decisions.
Remember, global settings are the guard rails for your workload. They are the general rules you want queries to use.
After you set up the guard rails, it’s up to you to define the exceptions.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.