Nine Christmases ago, in late November 2015, Microsoft released Service Pack 3 for SQL Server 2012, and included a gift just for me! Only kind of just for me, really for everyone, but I think it fits for Kevin Chant’s T-SQL Tuesday topic for this month.
I had written to Santa (okay, the product group at Microsoft) to ask for this. It wasn’t quite “If I’m on the nice list, what I’d like is a unicorn train set”, but more like “If it’s not too much trouble, this thing here would be amazing…”
The thing was the ability to see the “number of rows read” by an Index Seek operation in a SQL plan containing the “Actuals” (post-execution). I wrote about it here: https://lobsterpot.com.au/blog/2015/12/12/a-new-superpower-for-sql-query-tuners-number-of-rows-read/, but if you’re not inclined to read that, the quick summary is that there are two predicates (filters) that can filter the data that an Index Seek spits out – the Seek Predicate (leveraging the order of the data inherent in the index, like finding the people in the phone book whose last names start with ‘F’) and Residual Predicate (actually just called ‘Predicate’, but filtering down whatever the Seek Predicate has found, but unable to use the order of data – like finding the people called ‘Rob’ in the phone book, after locating the surnames that start with ‘F’). The performance of the Seek that finds, say, five rows is very much dependent on whether those five are easily found, or whether they’re just five within millions of others. It’s even possible to have an Index Seek to find a single row, that performs no better than an Index Scan. https://lobsterpot.com.au/blog/2011/05/19/covering-schmuvvering-when-a-covering-index-is-actually-rubbish/
Before this feature came out in 2015, there was a trace flag (9130) we could use to see whether the Seek Predicate or the Residual Predicate was the more selective – more on that over here, I logged an official feature request through Connect, and even asked people in the product group I knew.
Anyway, they not only delivered the feature, but even said it was specifically because of my request!
So it felt very much like a Christmas gift that year. Very humbling, and I’m still really pleased about this feature, because I use it every time I’m tuning queries.
I hope you all get exactly what you’re dreaming of this year, like queries that run really fast.
@robfarley.com@bluesky (previously @rob_farley@twitter)
