快照資料庫(Database Snapshots)可以提供資料庫特定時間點的靜態檢視，她是唯讀的資料庫，經常被使用在報表用途，由於資料靜止在指定的時間點，可以有效避免新交易造成報表間的數字差異。多年以後發現有個副作用，一直以為搬Data Page到快照資料庫只會影響一點點點效能，沒想到批次型的大量更新或刪除的交易，影響很明顯。
快照資料庫示意圖
左邊的來源資料庫是指原本的資料庫，右邊的疏鬆檔案則是快照資料庫建立的。
來源資料庫分頁被更新之後，系統會將修改前的分頁放到右邊的疏鬆檔案，快照集上的讀取時就會參考兩邊的分頁回傳指定快照集時間的資料。
建立測試環境
CREATE DATABASE [SnapshotDb]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'SnapshotDb', FILENAME = N'C:\SQL\SnapshotDb.mdf' , SIZE = 2GB , FILEGROWTH = 1GB )
LOG ON
( NAME = N'SnapshotDb_log', FILENAME = N'C:\SQL\SnapshotDb_log.ldf' , SIZE = 1GB , FILEGROWTH = 1GB)
GO
建立資料表T1，內含100萬筆資料
USE [SnapshotDb]
CREATE TABLE T1
(ID INT IDENTITY,C2 CHAR(2000),C3 uniqueidentifier, PRIMARY KEY(ID))
SET NOCOUNT ON
DECLARE @I INT = 0
BEGIN TRAN
WHILE @I < 1000000
BEGIN
INSERT INTO T1
VALUES (CONVERT(VARCHAR,@I),NEWID())
SET @I = @I + 1
END
COMMIT
對照組
在尚未啟用快照資料庫前，我們用批次交易更新100萬筆當中的10萬筆
SET STATISTICS IO ON
SET STATISTICS TIME ON
BEGIN TRAN T1
UPDATE T1
SET C2 = NEWID()
WHERE ID >= 300000 AND ID < 400000
ROLLBACK
執行結果:
資料表 'T1'。掃描計數 1，邏輯讀取 33463，實體讀取 3，讀取前讀取 33431，LOB 邏輯讀取 0，LOB 實體讀取 0，LOB 讀取前讀取 0。
SQL Server 執行次數:
，CPU 時間 = 407 ms，經過時間 = 486 ms。
(100000 個資料列受到影響)
SQL Server 執行次數:
，CPU 時間 = 187 ms，經過時間 = 183 ms。
更新花了0.486秒
實驗組
先建立快照資料庫
CREATE DATABASE SnapshotDb_dbss1800 ON
( NAME = SnapshotDb, FILENAME =
'C:\SQL\SnapshotDb_data_1800.ss' )
AS SNAPSHOT OF SnapshotDb;
GO
快照資料庫的Sparse file
空心的
重新執行剛剛的批次交易
SET STATISTICS IO ON
SET STATISTICS TIME ON
BEGIN TRAN T1
UPDATE T1
SET C2 = NEWID()
WHERE ID >= 300000 AND ID < 400000
ROLLBACK
執行結果:
資料表 'T1'。掃描計數 1，邏輯讀取 33463，實體讀取 0，讀取前讀取 0，LOB 邏輯讀取 0，LOB 實體讀取 0，LOB 讀取前讀取 0。
SQL Server 執行次數:
，CPU 時間 = 1640 ms，經過時間 = 14541 ms。
(100000 個資料列受到影響)
SQL Server 執行次數:
，CPU 時間 = 188 ms，經過時間 = 240 ms。
CPU Time增加到1640ms，更新花了14.5秒。
小結:
| 比較組別 | CPU Time | 執行時間 |
| 未啟用快照資料庫 | 0.407秒 | 0.486秒 |
| 啟用快照資料庫 | 1.640秒 | 14.5秒 |
- 小小的100萬筆Table差快30x。
- 報表跑完後就刪除快照。
- SQL Server 2016之後，每個SQL的版本都可以使用(Enterprise、Standard)，SQL 2014是Enterprise限定功能。
再20天，2017台北馬拉松
快照資料庫、快走資料庫
參考:
