We already saw that correct data types could decrease the row size. Why is it important? Well, obviously one of the reasons is the database and backup file sizes. Although this is not the only and frankly not the main reason.
When SQL Server reads or writes data, the data page needs to be in the memory. If data page is not present in the buffer pool, SQL Server needs to read the page from the disk. Alternatively, when data page has been modified, SQL Server needs to write this page to the disk. Physical I/O is one of the most expensive operations. So it leads to the simple conclusion:
Bigger Row Size = Less Rows Per Page = More data pages per table = More I/O operations = Performance degradation
Unfortunately I/O is not only thing affected. RAM and Buffer Pool size are limited. More data pages need to be processed/read from disk, more “old” pages would be flushed away from the Buffer Pool.
As the side note – this is one of the biggest problems with non-optimized queries. Those queries are not only slow by themselves, those are also flushing Buffer Pool with unnecessary data and compromise the system performance in general. There are 2 reliable performance counters you can see in Performance Monitor (perfmon.exe) under “SQL InstanceBuffer Manager” group: Page life expectancy – indicates how long the data page stays in cache (production value should be > 300 seconds) and Buffer cache/hit ratio – indicates % of the cases when requested data page is already in the cache (production value should be >96-97%). If you monitor those counters and see low values there, most likely the problem is non-optimized queries (assuming, of course, that you have enough RAM available for SQL Server).
Let’s forget about Buffer Pool for a minute and just focus on I/O cost. Let’s create 2 tables and populate them with 50,000 records
LargeRow table row size is a little bit more than 2,015 bytes, so it would fit 4 rows per page. SmallRow table row size is just 22 bytes. Let’s see how many data pages every table has:
As we can see, LargeRows table uses 12,500 data pages when SmallRows uses 149.
Now let’s run some tests and see how fast is table scan:
As you can see scan of SmalRows table is about 45 times faster than scan of LargeRow.
Obviously we rarely scan entire table in real life. Although above is true for every partial scans. So always use appropriate data types.