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.
  1. Provide recommendations for improving the delete part of the process.

  2. Provide recommendations for improving the insert part of the process.
  1. Regarding the delete process, if the entire table needs to be cleared, the customer should consider using the TRUNCATE statement, which is minimally logged.

  2. 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.