march 28, 2024
Optimizing SQL Server Disk Space Queries
Introduction
Managing disk space is key for keeping SQL Server up and running smoothly. How fast and stable your database is can really depend on how many times it’s hitting the disk and how much free space you’ve got in your storage. In this piece, we’re diving into ways to make server interactions with disk space more efficient.
Recovery model
The recovery model is a database feature that:
- Handles how transactions get logged;
- Decides if the transaction log needs backing up;
- Figures out what kind of recovery operations are available.
Let’s look at the different kinds of recovery models.
| Types of Recovery Models | Dеscriptiоn | Risk of loss of performance results |
|---|---|---|
|
Simple |
Logs are not backed up. |
Changes since the last backup are not protected. In the event of a disaster, these changes will have to be made again. |
|
Full
|
Backup copies of logs are created. |
If a significant part of the log is corrupted, it is required to restore the changes made in the database since the last backup. |
|
Bulk-logged |
Backup copies of logs are created. |
If a significant part of the log is corrupted, it is required to restore the changes made in the database since the last backup. |
Based on our experience, «Full» and «Bulk-logged» types overload the database log disks. This leads to performance degradation. We recommend setting the «Simple» value in your projects.
The «Recovery model» parameter is set in the database parameters:
Delayed Durability
«Delayed Durability» is a transaction resilience setting. This setting can be configured as follows:
- «Disable»: Using this setting, all transactions recorded in the database are fully durable regardless of the commit level settings (DELAYED_DURABILITY= [ON | OFF]).
- «Allowed»: With this setting, the durability of each transaction is determined at the transaction level — DELAYED_DURABILITY = {OFF | ON}.
- «Forced»: If this option is selected, all transactions committed in the database are delayed durable.
Let’s define the types of transaction durability:
- Fully durable transactions write the transaction log to disk before control is returned to the client.
- Delayed durable transaction resilience is achieved through asynchronous writing of logs to disk. Transaction log entries are held in a buffer and written to disk once the buffer fills up or during a buffer flush.
We recommend using the «Forced» type for your projects. Based on our company’s internal experience, at the hardware level, this setting reduces the average sizes of transfer blocks sent to transaction log files, and as a result, optimizes the queues on the disk with database logs by 4-8 times.
The «Delayed Durability» parameter is set in the database settings:
