[SQL SERVER]Hekaton-- 延遲持久新特性

2014-05-07

1676
0
SQL_SERVER

SQL 2014 這次改善重點都圍繞OLTP，SQL SERVER積極想成為OLTP霸者意圖越來越明顯，

身為Clould OS一份子，效能和高可用性必然不可馬虎，

如透過延遲持久特性來解決交易記錄檔寫入緩慢問題，

緩衝區(buffer pool)擴充(透過SSD)解決記憶體問題，

而最大亮點則是透過in-memory技術(將資料表轉移到記憶體)解決I/O問題，

高可用性方面則是透過Azure輕易達陣，

整體來說SQL2014希望降低調校OLTP成本，

我試用後不可否認某些新特性真的會讓我想立即升級SQL2014，

今天我先來介紹延遲持久新特性。

SQL2014以前交易只有完全持久性，這意味交易過程中，

SQL SERVER要確認相關交易紀錄都寫入交易紀錄檔後，才會把控制權返回給client(交易ACID)，

這往往造成很多開發人員不喜歡包交易，因為如果交易過久，表示Lock資源久，

資源Lock久 Block可能伴隨而來(畢竟你的AP和SQL SERVER都是服務多人)，

Block時間久相對Deadlock就會發生，這一連串過程導致大多數開發人員不包交易，

而大多數人也常問我，SQL SERVER 可否停掉Lock Manager或是如何調整Lock釋放資源或時間...等，

在OLTP中這確實帶給不少人麻煩，

雖然我們都知道要盡量縮短交易(如移除非交易邏輯、批次處理..等參考造成交易記錄檔案暴增的常見原因)，

即可降低Block產生頻率，但調校過程是需要花時間，

現在延遲持久新特性將讓你保有交易ACID，且降低Block產生頻率，

重點是你無須釋放任何調校SQL暗黑密技，只需安裝SQL2014並設定啟用延遲持久即可，

下面我簡單測試延遲持久和完全持久兩者，在交易效能方面的差距。

延遲持久

1.減少交易紀錄檔I/O 競爭(批次非同步寫入

2.縮短交易時間

3.I/O作業較完全持久性少

4.資料遺失風險(可恢復風險)

先來看看完全持久交易效能和IO

--關閉延遲交易
ALTER DATABASE [AdventureWorks2012] SET DELAYED_DURABILITY = DISABLED WITH NO_WAIT

--建立測試資料表
drop table transTest
create table dbo.transTest
(
    id INT NOT NULL IDENTITY(1,1),
    c1 INT NOT NULL DEFAULT 78,
    c2 VARCHAR(50) NOT NULL DEFAULT 'rico'
)

--存放該資料表IO檔案統計
IF OBJECT_ID('tempdb..#Before') IS NOT NULL
drop table #Before;
select * into #Before from sys.dm_io_virtual_file_stats(DB_ID('transTest'), NULL);

--新增2萬筆資料
begin
     set nocount on;
     declare @i INT =1;
     while (@i <= 20000)
     begin
         insert into dbo.transTest( c1, c2 )
         values(default, default);
         set @i += 1;
     end;
end;

--存放該資料表IO檔案統計(後)
IF OBJECT_ID('tempdb..#After') IS NOT NULL
drop table #After;
SELECT * INTO #After FROM sys.dm_io_virtual_file_stats(DB_ID('transTest'), NULL);

--結果
WITH myresutl AS
(
        SELECT
            b.database_id, b.[file_id],
            a.num_of_reads - b.num_of_reads as num_of_reads,
            a.num_of_bytes_read - b.num_of_bytes_read as num_of_bytes_read,
            a.num_of_writes - b.num_of_writes as num_of_writes,
            a.num_of_bytes_written - b.num_of_bytes_written as num_of_bytes_written,
            a.io_stall_write_ms - b.io_stall_write_ms as io_stall_write_ms
        FROM
            #Before as b
            INNER JOIN #After a ON b.database_id = a.database_id AND b.[file_id] = a.[file_id]
),
DBIO as
(
        SELECT
            DB_NAME(d.database_id) as DBName,
            CASE WHEN mf.[type] = 1 THEN 'log' ELSE 'data' END as file_type,
            mf.[name] as [file_name],
            SUM(d.num_of_bytes_written) as [io_bytes_written],
            SUM(d.num_of_writes) as [writes],
            SUM(d.num_of_reads) as [reads],
            SUM(d.num_of_writes + d.num_of_reads) as [io],
            sum(d.io_stall_write_ms) as exectime
        FROM
            myresutl d
            INNER JOIN sys.master_files mf ON d.database_id = mf.database_id AND d.[file_id] = mf.[file_id]
        GROUP BY
            DB_NAME(d.database_id),
            CASE WHEN mf.[type] = 1 THEN 'log' ELSE 'data' END
            ,mf.[name]
)
SELECT
        DBName, file_type, [file_name], [io], [reads], [writes],
        CAST(1.0 * [io_bytes_written]/(1024*1024) as DECIMAL(12, 2)) as io_mb_written
        ,case when writes>0 then CAST(1.0 * [io_bytes_written]/[writes] as DECIMAL(12, 2)) else 0   end as io_bytes_per_write
        ,[exectime] as 'exectime(ms)'
FROM DBIO
where DBName='AdventureWorks2012'
ORDER BY file_type;