Did you know that simply setting the Database Recovery Model to BULK_LOGGED does not guarantee minimal logging when inserting?
At the very least, for performance:
- Recovery Model should be set to BULK_LOGGED.
- Destination table should not be replicated.
- Table must be empty if indexes are on it.
- TABLOCK should be used (Where applicable).
- Import should be done during times of low DB load or during specified downtime.
The following table describes logging that will happen during an insert when the Recovery Model is BULK_LOGGED.
| Non Clustered Index | Clustered Index | |||
| Yes | No | Yes | No | |
| Table Has Data | Index | Minimal | Full | Minimal |
| Table Empty | Minimal | Minimal | Minimal | Minimal |
Remember also that if you are inserting in batches, only the first batch is into an empty table, the other inserts are into a table with data. The best solution is to always insert into an empty table with no indexes with the BULK_LOGGED Recovery model set.
Checking the recovery model is as easy as
SELECT DATABASEPROPERTYEX('[databasename]','Recovery'); |
If you are using SQL Server Enterprise Edition, remember that you can rebuild indexes ONLINE. This means that indexes can be rebuilt without locking the table. This means users can still perform searches (naturally without the indexes) while the indexes are rebuilding.
