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.

I am going to give you a round up of links at the end if you want to learn more.

Why do we care?

We care because the SQL Server team is telling us, ever-so-gently, to care.

Sometimes row goals help, and sometimes row goals hurt.

Sometimes you add them by using TOP or ROWCOUNT, or simply by adding an EXISTS to your query.

Normally when you look at an execution plan, or read the XML (I know I’m not the only one…), you can see the number of rows that the optimizer guesses is going to come out of an operation.

If you run a TOP 10 query, the optimizer will prepare a plan for 10 rows.

The plan looks pretty much how you’d expect!

That index scan also estimates 10 rows.

If we use the new hint mentioned in the CU KB, we get a different-looking plan.

The line going from the Clustered Index Scan is a lot wider than before.

And indeed…

Ordinary World

Under most circumstances, row goals are totally fine.

However, sometimes the optimizer will apply one to an operation. And sometimes, that won’t go so well.

Row goals can cause the optimizer to:

• Cost things differently. It’s far less expensive for the TOP operator to ask for 10 rows, than for the entire users table to be scanned, and TOP to act as a filter.
• Choose different operators. The operators needed to return 10 rows quickly are far different than the operators needed to produce 1 million rows efficiently. Think about join choices, aggregate choices, etc.
• Choose different access methods: Seeks vs. Scans are heavily influenced by row goals.

If you’ve ever seen the way a query runs, and the plan that is produced by poor estimates, you can understand why these things are bad news.

Where can I see them?

Don’t go looking in SSMS just yet. If you get an actual or estimated plan from a query in SSMS, it’s not in the XML.

However, If you get them from the plan cache later, you can see them in the XML.

According to People Much Smarter Than Me®, SSMS strips out XML that it doesn’t recognize, so we’ll have to wait for the next version to drop before we can access it easily.

I’ll get this into sp_BlitzCache and sp_BlitzQueryStore shortly, though I’m not sure what kind of analysis I’ll be doing with them yet. If you have any ideas about what you’d want to see, please let me know!

Because our query is a TOP 10 with no filter, we get an estimate of 10 rows. Without that estimate, we’d read the entire table (7250740 rows).

Where can I learn more?

• • • Adam Machanic has a talk here.
    • Paul White has blog posts here and here, and a four part series here: #1, #2, #3, #4.
  • Joe Obbish has a blog post here and a Connect item here

Thanks for reading!

Brent says: This is one of my favorite things in 2017 because it shows how deeply the SQL Server team cares about query tuners these days. Looking back, calendar year 2017 felt like one big long Christmas for performance folks. If you haven’t been to a performance tuning class since 2016 came out, then seriously, you’re missing out on a ton of good stuff.