Friday, April 27, 2012 10:11 PM
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 ..
Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
- Edited by Abhay_78 Friday, April 27, 2012 10:12 PM
Friday, April 27, 2012 10:22 PM
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.
Tuesday, May 15, 2012 6:50 AM
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