3

I have a 9 million record table that is about 452MB in size (disregarding NC indexes). Below is physical stats and the columns

Physical stats

columns

As a simple example, if I dump the table into a temp table, the actual plan shows a whopping 42GB estimated and actual data size. See image below.

I suspect this is due to the two varchar(4000) columns which is causing the estimated row size to be larger than they are. However, I really don't think 42GB are being transfered - not sure how that would be possible.. I don't know if I'm seeing performance issues because of this but the query plan looks scary because of these wrong estimates. Why is the query plan so wrong on the estimated and actual table scans?

actual plan

Improve this question
3
  • 1
    When was the last time you rebuilt the clustered index or updated stats using WITH FULLSCAN?
    – Solomon Rutzky
    Commented Oct 21, 2015 at 16:28
  • 4
    If you do the math of 9,602,837 rows multiplied by the average row size of 4,496 bytes, you get 41,174MB required.
    – Hannah Vernon ♦
    Commented Oct 21, 2015 at 16:32
  • 3
    You could use the following query to determine the maximum and average sizes of those columns, and as @Martin Smith said in his answer adjust the max size of those columns: SELECT MaxLengthA = MAX(LEN(c.SomeDataA)) , AvgLengthA = AVG(LEN(c.SomeDataA)) , MaxLengthB = MAX(LEN(c.SomeDataB)) , AvgLengthB = AVG(LEN(c.SomeDataB)) FROM dbo.C;
    – Hannah Vernon ♦
    Commented Oct 21, 2015 at 16:37

1 Answer 1

Reset to default
6

SQL Server just uses a ballpark estimate for varchar that each value will on average be half the column capacity.

So each varchar(4000) will be estimated at 2,000 bytes. You have two of them so that explains the bulk of the 4,496 bytes estimate.

It seems that in reality you could reduce the maximum size of these columns.

This would reduce the estimated row size. Having an accurate estimated row size can help when there are memory consuming operators in the plan for getting an appropriately sized memory grant.

Improve this answer
4
  • Ah ok, that's kind of what I thought. Didn't know about the engines formula for column capacity. I was hoping it would use something along the lines of what's in dm_db_index_physical_stats or maybe stored in stats somewhere. Is there any other solution besides decreasing the column sizes? Updating stats with FULLSCAN? The performance impact is the overhead of getting an overestimated memory grant (which I guess is better than an understimated grant).
    – Gabe
    Commented Oct 21, 2015 at 17:07
  • 1
    No there's no way of adjusting it apart from changing the column size either at source or with a cast. The snippet of execution plan you have shown just has it going straight into an insert to a heap anyway so I don't think that the poor estimate would have any particular effects except if there are other indexes on that heap including those columns.
    – Martin Smith
    Commented Oct 21, 2015 at 17:12
  • Sorry, another followup question - why would actual data size be 40GB too? Because that's the grant that was estimated? So will estimated data size and actual data size usually be the same then?
    – Gabe
    Commented Oct 21, 2015 at 17:13
  • 1
    I suspect that is calculated from actual rows * estimated data size.
    – Martin Smith
    Commented Oct 21, 2015 at 17:15

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.