How SQL Server estimates rows and how estimate errors affect plan choices.
60 associated posts57 primary posts
Secret Squirrel When you modify a table with multiple indexes, SQL Server may choose either a narrow plan, if it doesn't think all that many rows are going to change, or a wide plan if it thinks many will. In narrow plans, the work SQL Server has to do to modify many indexes is hidden…
The first paper on cost-based query optimization was published in 1979 by Patricia Selinger from IBM Research. This paper laid the basic framework for optimizing relational queries that is still in place today. While there have been many technical enhancements since 1979, query optimizers still fail to pick the best plan when faced with a complex SQL query.
This Is A Parallel Query
This Is A Temp Table
[crayon-6a6ece6a2c695998915748/]
This Is A Parallel Insert Into A Temp Table
sp_prepare For Mediocre
You may remember me from movies like Optimize for… Mediocre? and Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)!
Great posts, Kendra!
:thinking_face:
When most people think about computed columns, they don't think about cardinality estimates.
Heck, I'm not sure most people think about cardinality estimates. At all. Ever.
Monte Carlo Or Bust
In Part 3, I showed you how two queries with TOP clauses can produce wildly different plans.
To figure some stuff out about why the plans changed, let's focus on the Nested Loops Join plan.
Row Whats?
Like it or not, the optimizer has row goals.
We're not going to go terribly in-depth here, because the point is just to make you aware that this is in executions plans, and I'll be adding it to the FRK in an upcoming release.
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
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…
I recently saw a server with trace flag 2453 configured. I hadn't come across this trace flag before, so I did a little research. Microsoft says it allows "a table variable to trigger recompile when enough number of rows are changed". This can lead to a more efficient execution plan. Trace flag 2453 is available in SP2 or greater for SQL Server 2012, CU3 or greater for SQL Server 2014 and RTM or greater for SQL Server 2016.
This comes up during Office Hours once a week Which pretty much gives it a 100% hit rate. I'm writing this mostly to have something to reference when people ask. Trace flags If you restore a database on a SQL Server 2014 or greater instance, you have choices to make. You can either set the…
Using the StackOverflow database, let's check out Krock's query. He's a competitive fella, and he's looking to find users who signed up for StackOverflow after he did, but who have a higher reputation than he does. I'm going to simplify the query a little here: [crayon-6a6ece6a2d819778571699/] The Users table has a clustered index on the Id…
These things used to confuse me so much
Despite having worked at a Market Research company for a while, I know nothing about statistics, other than that project managers have all sorts of disagreeably subjective phrases for describing them. Vast majority, convincing plurality, dwindling minority, et al. Less talky, more picture.
When I started getting into SQL Server, and learning about statistics, I heard the same phrases over and over again, but wasn't exactly sure what they meant.
During some testing with SQL Server 2014's new cardinality estimator, I noticed something fun: the new CE can give you different index recommendations than the old one. I'm using the public Stack Overflow database export, and I'm running this Jon Skeet comparison query from Data.StackExchange.com. (Note that it has something a little tricky at the…
When you migrate to SQL Server 2014, rather than turning on the new cardinality estimator right away, I recommend sticking with the old cardinality estimator initially by leaving your database in SQL Server 2012 compatibility mode. Let the database settle down for a few weeks, let folks air out their grievances, and give yourself some time to make sure the system is working at least as well as the old system.
Update 2016: Microsoft recognized this problem, and added a new way to add powerful query hints to SQL Server 2016.
The QUERYTRACEON hint can be particularly useful for testing the new cardinality estimator in SQL Server 2014 on individual queries using Trace Flag 2312, if your database is still using compatibility level 110:
If you've been following the blogosphere, or heading to SQL Saturdays, you'll know that many folks are excited about the brand new SQL Server cardinality estimator (CE for short). As Kendra showed in her article The SQL 2014 Cardinality Estimator Eats Bad TSQL for Breakfast, the new CE provides dramatically improved query plans. What if…
Sometimes I run into a query plan that just shocks me. It's like looking up and suddenly seeing an adult dressed as a squirrel riding a unicycle down a busy city street. You have to stop and ask yourself, "Did that really just happen?" (I live in Portland, Oregon, so yeah, I've seen that.) A…
SQL Server needs to have a reasonably correct estimate of how many rows your query is going to handle. If it doesn't, things can go terribly, terribly wrong. Most often, the problem is that SQL Server under estimates its work. In this case, the query optimizer thinks it has just a little bit of work…
Building an execution plan is a lot like going shopping. Before I leave the house, I need to think about a few things: How many items do I need? I take a glance at my grocery list to see roughly how many things I'm shopping for. What stores am I going to visit? I can't…
