6

(I don't believe this question is a duplicate of this question from 8 years ago, as I'm not asking about the advantages of oversized columns, I'm asking about the behaviour demonstrated in the linked article below.)

This recent (2017) article from SQLPerformance.com demonstrates how varying the maximum length n for a varchar(n) column affects query plan row-size estimates and sorting-buffer size estimates that can lead to subpar performance and memory allocation warnings.

In it, the author claims (emphasis mine):

From here we see that, the bigger the column definition, the higher the estimated row and data size. In this simple query, the I/O cost (0.0512731) is the same across all of the queries, regardless of definition, because the clustered index scan has to read all of the data anyway.

But there are other scenarios where this estimated row and total data size will have an impact: operations that require additional resources, such as sorts.

When I read that claim (in bold) I was surprised because I thought that SQL Server would get fairly accurate row-size estimates from its sampled STATISTICS objects maintained on those same tables. Especially given the SELECT AVG(LEN(email)) query in the article shows that no column has a value exceeding 77 characters.

The article also explicitly performs an ALTER INDEX ALL ON dbo.Table REBUILD - which this DB.SE posting says will will also automatically update STATISTICS.

(though I'm surprised that the word "statistics" doesn't appear anywhere in the SQLPerformance article at all - so maybe in the author's case the statistics weren't updated at all due to some machine-configuration and they didn't notice?)

Does SQL Server only use the varchar column length limit for row-size estimates? If not, then why does the SQLPerformance article describe the same?

1 Answer 1

9

Does SQL Server only use the varchar column length limit for row-size estimates? If not, then why does the SQLPerformance article describe the same?

Correct. SQL Server uses only the varchar (max specified) length when estimating row size. The SQLPerformance article accurately describes the estimated row size measurement.

The longer answer

In his example in the linked article, Aaron rebuilds all indexes to ensure all versions of the query have an equal playing field as far as both index size and statistics so that the execution plans for all cases are "ideal" and (as the experiment proved) almost equal, but not quite.

Statistics are used to estimate how many rows will be returned, not how much memory is granted for the execution of a query.

In the article, Aaron says (emphasis mine):

SQL Server has to take into account how much data might be in a column. It doesn't do this based on actually profiling the data, and it can't make any assumptions based on the <= 201 histogram step values. Instead, it has to estimate that every row holds a value half of the declared column size. So for a varchar(4000), it assumes every e-mail address is 2,000 characters long.

Aaron's reference to "the histogram step values" is a reference to the statistics histogram. The statistics histogram contains knowledge of at most 201 data values from the table. It knows the actual length of those (up to 201) explicit values, but it has no idea about the values in between those.

Additionally, Statistics are based on a sample of data, so there may be rows that were not analyzed as part of the sample, and relying on min/max/avg length of data from stats would be another opportunity for outdated or unrepresentative samples to adversely affect query execution.

4
  • Thank you for the answer! Do you know why SQL Server doesn't gather statistics on data length though? It seems like a surprising omission. Also, only 201 histogram bins seems... small when you consider that many tables may hold millions or billions of rows.
    – Dai
    Commented Oct 4, 2020 at 11:58
  • My last sentence is the best I can do to say why the SQL Server team doesn't try to use stats for estimating row length. Ultimately, remember that stats and row length are both estimates used to find an execution plan that is good enough. SQL Server's whole query optimization engine is based on getting a "good enough plan" that can be cached to use for all future executions.
    – AMtwo
    Commented Oct 4, 2020 at 12:00
  • I feel that average or max sample estimates would be more accurate in most cases compared to data models with blanket high maxlen, but I am sure the SQL Server developers have better insight how their product is used.
    – eckes
    Commented Oct 4, 2020 at 14:02
  • There very well could be a better way, and if you have ideas you can enter them on the SQL Server User Voice page. But the current behavior is pretty simple, and Aaron's post on SQLPerformance is accurate
    – AMtwo
    Commented Oct 4, 2020 at 16:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.