SQL Server 最小化日誌操作解析,應用

標籤：des   style   blog   http   color   使用   os   資料   

Sql Server 中資料庫在BULK_LOGGED/SIMPLE模式下的一些操作會採用最小化日誌的記錄方式,以減小tran log落盤日誌量從而提高整體效能.

這裡我簡單介紹下哪些操作在什麼樣的情況下會最小化日誌記錄.以及現實生產環境中如何應用最小化日誌.

 

概念:SQL Server在滿足相應條件的基礎上時進行一些特定的操作如Rebuild Index時會進行最小化Tran Log記錄操作,從而改善系統效能.

注意:含最小化動作記錄操作段日誌無法按時間點恢複(point in time)

需要還原模式為簡單或大容量日誌

 

最小化日誌的操作

Create Index,Alter Index Rebulid

Bulk import操作(BCP,Bulk insert)

Select into

Blob資料操作(使用Write等)

Insert select(sql 2008後特定條件下可以)

Merge(特定條件)

 

應用:實際應用過程中我們實際使用insert select的時候居多,就此介紹

關於insert select操作的最小化日誌

聚集表

當聚集表為空白時,使用TABLOCK 鎖提示將會最小化日誌

當聚集表非空時,無論如何將不會最小化日誌

非聚集表

當堆表為空白時,使用TABLOCK鎖提示,表中行資料,索引資料(非叢集索引)都會最小化日誌

當堆表非空時,使用TABLOCK鎖提示,表中存在非叢集索引,則行資料,索引資料均非最小化日誌

注:最小化日誌中表非複製表

   一些文檔中在堆表有索引非空的情況認為堆行資料會最小化日誌,實際是錯誤的.見圖b-2中說明

聚集表執行個體

聚集空最小化日誌 圖a-1

create database testbulkgouse masterALTER DATABASE [testbulk] SET RECOVERY BULK_LOGGED WITH NO_WAITgouse testbulkgocreate table t1(id int not null identity (1,1),dystr varchar(200),fixstr char(500));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 @i=@i+1endcreate table tcls(id int ,dystr varchar(200),fixstr char(500))goCREATE UNIQUE CLUSTERED INDEX inx_id ON dbo.tcls (id)insert into dbo.tcls with(tablockx)select * from dbo.t1 ----cluster table emptyselect operation,CONTEXT,[Log Record Length],AllocUnitName from fn_dblog(null,null)where AllocUnitName like ‘%tcls%‘

                                             a-1

聚集非空非最小化日誌圖a-2

truncate table tclsDBCC SHRINKFILE (N‘testbulk_log‘ , 0, TRUNCATEONLY)insert into dbo.tcls with(tablockx) values  (100000,‘aaa‘,‘bbb‘)----made not empty clustered tablegoinsert into dbo.tcls with(tablockx)select * from dbo.t1 ----cluster table not emptyselect operation,CONTEXT,[Log Record Length],AllocUnitName from fn_dblog(null,null)where AllocUnitName like ‘%tcls%‘

                                            a-2

 

非叢集索引執行個體

非聚集非空堆表無索引執行個體 圖b-1

create table tnoncls(id int ,dystr varchar(200),fixstr char(500))goinsert into dbo.tnoncls with(tablockx) values (100000,‘aaa‘,‘bbb‘)----made not empty heap table no indexgoinsert into dbo.tnoncls with(tablockx)select * from dbo.t1  with(tablockx)----heap table not empty with no indexselect operation,CONTEXT,[Log Record Length],AllocUnitName from fn_dblog(null,null)where AllocUnitName like ‘%tnoncls%‘ 

 

                                        圖b-1

非聚集非空堆表含索引執行個體 圖b-2

truncate table tnoncls----truncate tableDBCC SHRINKFILE (N‘testbulk_log‘ , 0, TRUNCATEONLY)CREATE UNIQUE NONCLUSTERED INDEX inx_id ON dbo.tnoncls (id)----add non clustered indexinsert into dbo.tnoncls with(tablockx) values (100000,‘aaa‘,‘bbb‘)----made not empty heap table with indexgoinsert into dbo.tnoncls with(tablockx)select * from dbo.t1  with(tablockx)----heap table not empty with  indexselect operation,CONTEXT,[Log Record Length],AllocUnitName from fn_dblog(null,null)where AllocUnitName like ‘%tnoncls%‘----both datapage and indexpage full log

 

                                              b-2

 

關於trace flag 610

Sql2008新引進的TF,用於非空B-tree結構中仍可最小化日誌操作.

關於TF610的使用我個人建議是特殊情境謹慎使用.

一般來說我們在對非空表匯入資料的情境,堆表在Online的過程中最小化日誌鎖表本身就會影響線上的應用.聚集表資料在插入過程中大量匯入的可能性就更低.(好好的聚集表資料大量匯入,情況甚微).

TF610本身是為了減少記錄的tran-log大小而設計,並非加快匯入而設計.

使用TF610時注意:

1:特定情況下session級開啟 dbcc traceon(610)

2:當批量事務提交時所有資料頁需落盤,如果此之前沒有檢查點執行落盤會帶來大量的隨機IO從而導致效能下降,有時甚至不如全日誌記錄的插入.

3:避免單個事務過大.超大事務可能導致其他問題.

 

最小化日誌(Minimal Log)最佳實務

BULK_LOGGED模式:現實生產環境中的資料庫一般是簡單,或者全日誌. BULK_LOGGED模式使用常態下寥寥無幾.但當我們的資料操作中存在大量可最小化的日誌操作中(如索引重建維護)我們可以開啟BULK_LOGGED模式從而提高操作效率.

例:索引維護

1:選取操作時間視窗:日常全備份前

2:全備份完成後,人工幹預執行一次記錄備份.

3:修改資料庫模式由Full->BULK_LOGGED

4:大容量日誌操作(索引維護)

5:人工幹預備份日誌

6:重新調整為全日誌(模式)

 

BULK_LOGGED模式下是不會破壞日誌鏈,在這樣的模式下我們把Non point time的時間段降到了最低.

注:當資料庫有應用全記錄模式的情況下,如鏡像,不宜修改的資料庫模式而破壞應用,當全日誌情形下產生的大量日誌可能導致執行個體級的全域問題,應仔細權衡操作.

    對有審計需求的資料庫來說,注意具體審計需求:是否需要恢複到時間點.