Record Size Question in SQL 2005 2

[Auguy]

I know the record size in 2000 is a little over 8000 bytes and 2005 has increased this limit. Is there any concerns about going over 8000 bytes in 2005? I thought I read a few posts on various sites that said this might be a performance concern. The reason I ask is I have a record where there might be 8-10 varchar columns that would exceed 8000 bytes. I could possibly break this into two tables. This isn't going to be a huge table and also not in a high volume transaction environment. I'm guessing I shouldn't be too concerned.

Auguy
Sylvania/Toledo Ohio

 

[gmmastros]

In SQL2000, you were limited to 8060 bytes per row. Things like Text and nText stored 16 bytes in the row, which was just a pointer to another location where the actual data was stored.

Also with SQL2000, you could define columns in a table that exceeded the 8060, but you would get an error if the data exceeded 8060 bytes.

With SQL2005 and up, things changed a little. You are still limited to 8060 bytes for the "in row data". If you add data to the row that causes it to exceed the 8060 bytes, SQL will move the data out of the row for you automatically. There is a performance penalty for doing this.

When you have columns that can hold large amounts of text, you can create another table that has a foreign key back to the original table and use it to store the large text. This approach is preferred because it keeps the main table narrow and narrow tables have significantly better performance.

The reason there is an 8060 byte limit is because SQL Server stored data to disk in 8K chunks. Each row has some overhead, so you are reduced to 8060 bytes. When SQL reads data from disk, it always loads the entire 8K chunk. Always. If this 8k of data contains just one row and you have 1,000,000 rows in the table, SQL would need to go to the disk 1,000,000 times to read the whole table. If your in-row data fits in 80 bytes, that's approximately 100 rows per 8K data, so SQL would only need to go to the disk 10,000 to read the entire table. That's 100x less work, therefore better performance.

Often times, large text columns are only used for individual reports (and edit screens), but there are usually a ton of reports that don't use this data.

For example, suppose you were tracking job applicants. You would want to store, name, phone, address, etc... You may also want to store the entire resume in a text (varchar max) column. You probably wouldn't run reports where you show the Resume column for 1,000's of applicants. You would run reports that show highest level of education, the city where they live, etc... If you wanted to print the details for certain applicants, you would probably want to include the resume.

The reports that don't include the large text columns will be much faster if the text column is in another table.

That being said, you are likely to see a difference only if your table is very large. For small-ish tables, there's not likely to be any (noticeable) difference in performance.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Auguy]

Thanks George, great explanation. I hope others can bebefit from this information.

Auguy
Sylvania/Toledo Ohio

 

[Mike Lewis]

George,

I commend you for your very informative explanation of the 8060-byte issue.

When I try to explain to people how important it is to keep their data types small - for example, by not using Datetimes if a SmallDatetime will suffice - I often get the fatuous resonse: Why bother; disk space is cheap.

I do my best to explain that the issue has got nothing to do with the cost of disk space, but rather with the desirability of packing as many logical rows as possible into each physical 8K page. But it's sometimes hard to get this across.

Maybe I'll just refer to our post in future.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[gmmastros]

Thanks Mike.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Take a look at this very good blog post


How SQL Server stores data

and a few other good blogs:

Inside the Storage Engine Anatomy of a record
Advanced T-SQL Tuning - Why Internals Knowledge Matters

PluralSight Learning Library