| Downloads |
|---|
| 129916.zip |
At some point, every SQL Server developer or DBA has wanted the ability to perform operations in the database with absolutely no logging in the transaction log. Many people think this is possible by setting the recovery mode to SIMPLE. Let me assure you that’s simply not true. You can, however, achieve what is referred to as a minimally logged load with certain operations under specific conditions. When an operation meets the conditions and the database is set to SIMPLE or BULK LOGGED recovery mode, the information that gets written to the transaction log is drastically reduced compared with what’s written in a fully logged operation.
Typically, all the information required to roll forward or roll back the transaction is written to the log. With a minimally logged operation, essentially just the IDs of the data or index pages affected by the operation are written to the log—that is, just enough information to recover the transaction without supporting point-in-time recovery. As a result, considerably less data is written to the log, which can significantly reduce the amount of time to complete the operation and the load on the server.
Necessary Conditions
As I mentioned, you can achieve a minimally logged load with certain operations under specific conditions. The types of operations that can take advantage of minimal logging include the following:
- SELECT INTO operations
- Certain bulk import operations, such as BULK INSERT, INSERT . . . SELECT, and bcp
- CREATE INDEX, ALTER INDEX REBUILD, and some DROP INDEX operations
- Operations that insert or append large object (LOB) data types
Certain conditions might also need to be satisfied for operations to obtain the minimally logged behavior. Take, for example, the bulk import operations that load data into an existing table. The conditions for that table include the following:
- It can’t be replicated.
- It must have the table locking (TABLOCK) hint specified.
- It must be empty if it has indexes.
If any of these conditions are not met, the operations will be fully logged regardless of the database’s recovery mode. You can find more information about which operations can be minimally logged and their conditions in SQL Server Books Online (BOL).
The Results Don’t Lie
I created a demo script, Test_Min_Logging.sql, that performs a series of tests to determine how much data is actually written to the transaction log file for certain operations under specific conditions. It tests three types of operations: SELECT INTO, BULK INSERT, and CREATE CLUSTERED INDEX. Although these tests don’t represent all the types of operations in which you can achieve a minimally logged load, they’ll certain give you a taste of the benefits you can reap. (You can download this script by going to the top of this page and clicking the 129916.zip hotlink.)
Test_Min_Logging.sql determines how much data is written to the log file by querying the sys.dm_io_virtual_file_stats dynamic management function (DMF), as shown here:
SELECT CAST(([num_of_bytes_written] /
1048576.0) AS NUMERIC(10,2)) AS [MBs]
FROM sys.dm_io_virtual_file_stats(DB_ID
('TestML'),2) ;
The script runs this query before and after each operation, then subtracts the results, giving the amount of data (in megabytes) that the operation wrote to the transaction log. The script also reports the number of rows affected by each operation and the size of the resulting table for reference purposes.
Table 1 shows the results. As you can see, the SELECT INTO and CREATE CLUSTERED INDEX operations are greatly influenced by the recovery mode. For the SELECT INTO operation, 70 percent less data was logged in SIMPLE recovery mode compared with FULL recovery mode. The percentage was even higher for the CREATE CLUSTERED INDEX operation—98 percent less data was logged in SIMPLE recovery mode compared with FULL recovery mode.
Table 1: Comparing Minimally Logged and Fully Logged Operations
Similarly, the results show that BULK INSERT operations are greatly influenced by the TABLOCK hint. Specifying TABLOCK resulted in 82 percent less data being logged compared with when that hint wasn’t specified—no matter whether a small or large flat file was being bulk inserted.
Take Advantage of Minimal Logging
The savings in the amount of data written to the transaction log can be huge when you take advantage of minimal logging. The larger the data set being manipulated, the larger the savings will be. One word of caution, though: I’m not recommending that you change your production databases to SIMPLE recovery mode just to get minimally logged loads. However, almost everyone can take advantage of minimal logging in some operation. For example, don’t make the mistake of importing your data to staging tables inside your production databases and incur the overhead of a fully logged operation when it’s not necessary. By using a staging database that’s set to SIMPLE recovery mode to do your initial imports, you can avoid the costly overhead of logging all that data. You’ll see a drastic reduction not only in I/O but also in the time it takes to finish these types of operations.
