Database Reference
In-Depth Information
Figure 32-25.
Database with multiple data and delta files
Using a separate delta file to log deletions allows SQL Server to avoid modifications in data files and random
I/O in cases when rows are deleted. Both data and delta files are append-only. Moreover, when files are closed, they
become read-only. The size of the data files depends on the amount of memory installed on the server. SQL Server
uses approximately 16MB data files when the server has up to 16GB of RAM, or 128MB files when the server has more
memory, closing files when they are full or during a manual
CHECKPOINT
operation.
When SQL Server needs to load in-memory OLTP data to memory, after a restart for example, it loads only the
non-deleted versions of rows using delta files as the filter. It checks that a row from a data file is not deleted and is not
referenced in the delta files. Based on the results of this check, a row is either loaded to memory or discarded.
The process of loading data is highly scalable. SQL Server creates one thread per logical CPU, and each thread
processes an individual pair of data and delta files. In a large number of cases, the performance of the I/O subsystem
becomes the limiting factor in data loading performance. Figure
32-26
illustrates the data loading process.
