Estimated row size value of Actual Execution plan

Question

I have a table with 3 nvarchar (255) columns (there are more columns).In the actual execution plan I see the estimated row size as 780bytes which is fine (255x3 + some overhead). Now If I change the length to 100 , 50,50 the estimated row size of the actual execution plan is 215 which is OK ...

My questions are :

1: What is the significance of estimated row size in overall resource consumption by the query .Does it take more memory .The subtree cost is same and a varchar column does not take extra space if the actual string is say 5-10 character.

2: Is there a way we can see the actual row size ? any formula ? 

3: HOW CAN I find the rows per page ..

Regards

---

Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/

Answer 1

The rows per page is going to vary with the actual data, sys.dm_db_index_physical_stats will give you an overview.

As for the guestimates in plans, no doubt they have some algorithm which afaik goes undocumented, but I suppose it maps approximately to estimated page reads.

Josh

Answer 2

In coverage of your first question, the estimated row size is a factor in determining the memory grant for things like sorting.

You can read pretty detailed coverage of the topic here: http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx

---

Jason R. Hall MCSE, MCSD, MCPD.NET 2.0