What do these different properties in SQL Server's (Azure Synapse's) Estimated Execution Plan mean?

Question

I'm trying to work on the statistics, and as a part of it, I'm trying to look at the execution plan of certain SELECT * commands with a WHERE condition on a particular column.

What I keep getting is the below fields (example output):

I don't really know what these properties mean. I'm trying to perform a before-stats-update and after-stats-update difference in these results, and I don't see much change.

Can someone please throw some light here? Would be very helpful to understand this information.

Answer 1

The optimizer uses statistics and row counts to estimate the number of rows that will be consumed and produced by each operator in the query tree. For a simple leaf Get like this, it is estimated to be executed 1 time and will return 13.2M rows. The row width is estimated to be 2544B. If your Get was on the inside of a nested loops join (presumably with another Get on the outer), then you could get multiple scans of the inner table and the Estimated Number of Executions would potentially be > 1. That would then be also shown in the Estimated Number of Rows for All Executions as being a multiple of the 13.2M number. The I/O costs are zero for this case, but they would represent a cost for the scan that helps the optimizer compare this path versus other paths during its search of the plan space.

For a normal user, the way that you can examine if updated/better statistics would help your query is to try running the query with "set statistics profile on" (note: it has a bit of overhead, so don't run like this unless needed to do validations manually) before and after updating stats. You can then look at the per-operator actual vs. estimated row counts to see if things got better. Also, the query store will record runtime information (though not per-operator information) which can give you a summary over your whole workload of how it is performing.