July 16, 2014 at 11:57 am
I think I have this straight, but I'm posting here because I want to be sure. I'm advocating moving away from a particular practice of using varchar(max) or nvarchar(max) for all columns in an import work table.
When SQL Server examines a table to do a memory grant, it uses half the size of a variable field in its estimation so a varchar(max) would be 1 GB (approximately) for each column, each row.
If I have a table with 100 varchar(max) columns, SQL Server is going to try to reserve 100 GB (approximately) for every row the query plan estimates the query will return.
If that query plan estimates a return of 1,000 rows, SQL Server will want to reserve 100 TB (approximately) for the query!
SQL Server query plan memory grants tend to be limited to 25% of the available memory on the server, meaning we're guaranteeing lots of hard drive use and seriously limiting any other query that can be run.
I've also read that there is a performance penalty[/url] in using varchar(max).
Finally, BOL states that varchar(max) only uses the space in storage that is actually used by the data entered and therefore can be in-line in the table. However, a SQL Server user group lecture I went to recently stated that varchar(max), varbinary and XML types are automatically stored as LOB, no matter how large they are. Not taking up the full space, but not in-line either, stored like a SLOB, if I recall correctly. I'd love to know which is correct.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply