Joins, parallel execution, memory grants, spills, and adaptive execution.
93 associated posts89 primary posts
Every single time
Really. Every single time. It started off kind of funny. Scalar functions in queries: no parallelism. Scalar functions in computed columns: no parallelism, even if you're not selecting the computed column. Every time I think of a place where someone could stick a scalar function into some SQL, it ends up killing parallelism. Now it's just sad.
With each new version of SQL comes a slew of new stuff
While some changes are cosmetic, others bewildering, and the rest falling somewhere between "who cares about JSON?" and "OH MY GOD TAKE MY MONEY!", but not really my money, because I only buy developer edition. Aaron Bertrand has done a better job finding, and teaching you how to find new features than I could. Head over to his blog if you want to dive in.
We had some great questions about trivial execution plans in SQL Server in our Advanced Querying and Indexing class a few weeks ago. Here's a little glimpse into what we talked about.
For really simple queries, SQL Server can use "trivial optimization". If there's a very limited number of ways to run the query, why do a bunch of fancy, CPU burning cost-based optimization? Just chuck the plan at the query and let it go!
When you work with the same database server day in and day out, you can get a bit blinded by your assumptions. You might just assume that the way SQL Server does it is how every database does it.
But check out these features from other database platforms:
We've all had this problem before - the production server is significantly more powerful than the development server. You tune a query and you're pretty sure you've made it faster, but there's that doubt in the back of your mind. There are so many things that can change performance - memory, CPU count, disk performance…
Parallelism can be confusing. A single query can have multiple operators that run at the same time. Each of these operators may decide to use multiple threads. You set SQL Server's "max degree of parallelism" to control the number of processors that can be used, but it's not immediately obvious what this means. Does this setting…
"Should a query get to use more than one CPU core?" That's an important question for your SQL Server. If you're not sure what parallelism is, get started by exploring the mysteries of CXPACKET with Brent. He'll introduce you to the the setting, 'Cost Threshold for Parallelism'.
Let's test Cost Threshold for Parallelism
I generate an estimated execution plan for the following query. I'm running against a copy of the StackOverflow database that doesn't have many indexes.
In most coding languages, functions are often-used blocks of code that can be reused from multiple locations, leading to less code – and cleaner code. SQL Server also lets us create functions that can be used the same way. They are reusable blocks of code that can be called from multiple locations. So, if you need to format phone numbers a certain way, or parse for specific characters, you can do so using a function.
Answer: Yep, sometimes it can.I used to think that higher degrees of parallelism followed a law of diminishing returns-- you could add more threads, but the benefits would taper off. But it's a bit more complicated than that. Microsoft's recommendation to be careful when setting maxdop to values over 8 is a warning worth heeding.Lowering maxdop…
When someone brings me a query to tune, I start with a few questions:
How long does it take to run now? I'm looking for a time at first, and later as I progress through tuning, I'll measure it more scientifically with logical reads and CPU cycles.
The fastest query is one you never execute. The premise is that one of the slowest parts of starting up an application isn't starting the application itself, it's loading the initial application state. This can become a problem when you're loading many copies of your application on many servers, especially you're in the cloud and…
Crystal meth helps you lose weight and feel better about yourself. It's true. Wikipedia says so. Sure, the side effects of methamphetamine are more than just anorexia and euphoria, but if you're looking to be an upbeat, skinny person, meth gets the job done. SQL Server has a couple of similar features that might make…
Here's my favorite SQL Server books for 2016-2014:
T-SQL Fundamentals - (updated for SQL 2016) - don't be fooled by this "fundamentals" title, because everybody who writes T-SQL queries needs this book. It's the manual we should have been given when we started, and everyone's going to learn something about concurrency, performance, and updates here.
