Using Modifications That Support Optimized Logging :: SQL Server 2012
You are a consultant for the IT department of a large retail company. The company has a
nightly process that first clears all rows from a table by using a DELETE statement, and then
populates the table with the result of a query against other tables. The result contains a few
dozen million rows. The process is extremely slow. You are asked to provide recommendations
for improvements.
- Provide recommendations for improving the delete part of the process.
- Provide recommendations for improving the insert part of the process.
Answers
- Regarding the delete process, if the entire table needs to be cleared, the customer
should consider using the TRUNCATE statement, which is minimally logged.
- Regarding the insert process, it could be that it’s currently very slow because it doesn’t
benefit from minimal logging. The customer should evaluate the feasibility of using
minimally logged inserts like the SELECT INTO statement (which would require dropping
the target table first), the INSERT SELECT statement with the TABLOCK option,
and others. Note that the recovery model of the database needs to be simple or bulk
logged, so the customer should evaluate whether this is acceptable in terms of the
organization’s requirements for recovery capabilities.