SQL Server Minimized log operation parsing, application [manuscript]

• Some of the operations of databases in SQL Server in Bulk_logged/simple mode take the form of minimizing log logging to reduce the amount of Tran log disk logs to improve overall performance.

  Here I'll briefly describe which operations will minimize logging in what circumstances. and how to minimize logs in a real-world production environment.

  Concept: SQL Server can improve system performance by minimizing the Tran log record operation when performing certain actions such as rebuild index when the appropriate conditions are met.

  Note: The log operation segment with minimized operations log is not recoverable by point-in-time

  Requires restore mode for simple or bulk logs

  Actions to minimize logs

  Create Index,alter Index Rebulid

  Bulk Import operation (Bcp,bulk insert)

  Select into

  BLOB data manipulation (using write, etc.)

  Insert Select (available under specific conditions after SQL 2008)

  Merge (Specific conditions)

  Application: The actual application process when we actually use the insert Select when the majority, the introduction of this

  About minimizing logs for insert select operations

  Aggregation table

  When the clustered table is empty, using the TABLOCK lock hint will minimize the log

  When the clustered table is non-empty, the log will not be minimized anyway

  Non-clustered tables

  When the heap table is empty, use TABLOCK lock hint, table row data, index data (nonclustered index) will minimize the log

  When the heap table is not empty, use the TABLOCK lock hint, the table has a nonclustered index, the row data, the index data is not minimized log

  Note: Minimize table non-replicated tables in the log

  In some documents where the heap table has an index non-empty, the heap row data is considered to minimize the log, which is actually wrong. See Figure B-2

  Clustered Table Instances

  Clustered empty minimized log graph A-1

  
  Create DATABASE Testbulkgouse masteralter database [Testbulk] SET RECOVERY bulk_logged with No_waitgouse testbulkgocreate Table T1 (ID int not NULL identity (), Dystr varchar ($), fixstr char), Goset nocount ondeclare @i intset @i=0while ( @i<20000) begin  INSERT into T1 (DYSTR,FIXSTR) VALUES (' AAA ' +str (rand () *100000000), ' BBB ' +str (rand () *100000000) )  set @[email protected]+1endcreate table tcls (id int, dystr varchar ($), fixstr char) gocreate UNIQUE CLUSTERED INDEX inx_id on dbo.tcls (ID) insert to DBO.TCLS with (TABLOCKX) SELECT * FROM dbo.t1----cluster table Emptyselect Operati On,context,[log Record length],allocunitname from Fn_dblog (null,null) where allocunitname like '%tcls% '

     Http://www.it165.net/database/html/201407/7428.html

SQL Server Minimized log operation parsing, application [manuscript]