sql server - Does record size affect SQL performance?

Question

Our team is using Microsoft SQL Server, accessed using Entity Framework Core.

We have a table with 5-40 million records anticipated, which we want to optimize for high-velocity record create, read and update.

Each record is small and efficient:

• 5 integer (one of which is the indexed primary key)
• 3 bit
• 1 datetime

plus 2 varchar(128) - substantially larger than the other columns.

The two varchar columns are populated during creation, but used in only a tiny minority of subsequent reads, and never updated. Assume 10 reads and 4 updates per create.

Our question is: does it improve performance to put these larger columns in a different table (imposing a join penalty for create, but only a tiny minority of reads) versus writing two stored procedures, using one which retrieves the non-varchar columns for the majority of queries, and one which retrieves all columns when required?

Put another way: how much does individual record size affect SQL performance?

question from:https://stackoverflow.com/questions/65837073/does-record-size-affect-sql-performance

Answer 1

does it improve performance to put these larger fields in a different table (imposing a join penalty for create, but only a tiny minority of reads)

A much better alternative is to create indexes which exclude the larger columns and which support frequent access patterns.

The larger columns on the row will have very little cost on single-row operations on the table, but will substantially reduce the row density on the clustered index. So if you have to scan the clustered index, the having large, unused columns drives up the IO cost of your queries. That's where an appropriate non-clustered index can offload any scanning operations away from the clustered index.

But, as always, you should simply test. 40M rows is simple to generate, and then write your top few queries and test their performance with different combinations of indexes.