Estimated number of rows to be read – sqlperformance. com
Don’t get me wrong – I love the Actual Rows Read property that we saw arrive in SQL Server’s execution plans in late 2015. Data recovery ios But in SQL Server 2016 SP1, less than two months ago (and considering we’ve had Christmas in between, I don’t think much of the time since then counts), we got another exciting addition – Estimated Number of Rows to be Read (oh, and this is somewhat down to the Connect item I submitted, both demonstrating that Connect Items are worth submitting and making this post eligible for this month’s T-SQL Tuesday, hosted by Brent Ozar ( @brento) on the topic of Connect items).
Let’s recap a moment…
when the SQL Engine access data in a table, it uses either a Scan operation or a Seek operation. Database data types And unless that Seek has a Seek Predicate that can access at most one row (because it’s looking for an equality match on a set of columns – could be just a single column – which are known to be unique), then the Seek will perform a RangeScan, and behaves just like a Scan, just across the subset of rows that are satisfied by the Seek Predicate.
The rows satisfied by a Seek Predicate (in the case of a Seek operation’s RangeScan) or all the rows in the table (in the case of a Scan operation) are treated in essentially the same way. Data recovery johannesburg Both might get terminated early if no more rows are requested from the operator to its left, for example if a Top operator somewhere has already grabbed enough rows, or if a Merge Operator has no more rows to match against. Iphone 5 data recovery software And both might be filtered further by a Residual Predicate (shown as the ‘Predicate’ property) before the rows even get served up by the Scan/Seek operator. Database operations The “Number of Rows” and “Estimated Number of Rows” properties would tell us how many rows were expected to be produced by the operator, but we didn’t have any information about how may rows would be filtered by just the Seek Predicate. Database index We could see the TableCardinality, but this was only really useful for Scan operators, where there was a chance that the Scan might look through the whole table for the rows it needed. Database crud It wasn’t useful at all for Seeks.
Getting plans out of the cache is where the estimated values come into their own. Drupal 8 database It’s not just that I’d prefer to not actually run potentially-expensive queries on customer databases. Data recovery disk Querying the plan cache is one thing, but running queries to get the actuals – that’s a lot harder.
With SQL 2016 SP1 installed, thanks to that Connect item, I can now see the Estimated Number of Rows to be Read property in estimated plans, and in the plan cache. Database 3 tier architecture The operator tooltip shown here is taken from the cache, and I can easily see that Estimated property showing 7,276. Data recovery orlando This is shown from Management Studio because Plan Explorer doesn’t yet call out this property explicitly:
This is something which I could do on a customer box, looking in the cache for situations in problematic plans where the ratio of Estimated Number of Rows to be Read and Estimated Number of Rows isn’t great. Database cardinality Potentially, someone could make a process that checked every plan in the cache, but it’s not something that I’ve done.
Astute reading will have noticed that the Actual Rows that came out of this operator was 149, which was much smaller than the estimated 1382.56. Database unit testing But when I’m looking for Residual Predicates that are having to check too many rows, the ratio of 1,382.56 : 7,276 is still significant.
Now that we’ve found that this query is ineffective without even needing to run it, the way to fix it is to make sure that the Residual Predicate is sufficiently SARGable. I data recovery software free download This query…