RiCo技術農場

今天我在SQL PASS逛到一則有趣的討論，內容如下圖:

主要就是3-5TB約3百多萬個影像檔和文字檔，是否可以存放在SQL SERVER呢?目前該網友是放在FileServer，

並使用分享資料夾方式來管理。我透過這篇來分享為什麼我選擇LOB資料類型不應存在SQL SERVER。

 

我先來介紹一下資料表或索引配置單位總類如下

IN_ROW_DATA:

包含所有資料 (除了大型物件 (LOB) 資料之外) 的資料列或索引資料列。

頁面類型是 Data 或 Index。

LOB_DATA:

以下列一或多種資料類型儲存的大型物件資料：text、ntext、image、xml、varchar(max)

、nvarchar(max)、varbinary(max) 或 CLR 使用者定義型別 (CLR UDT)。

頁面類型是 Text/Image。

ROW_OVERFLOW_DATA:

可變長度資料，以超過 8,060 位元組資料列大小限制的 varchar

、nvarchar、varbinary 或 sql_variant 資料行來儲存。

頁面類型是 Text/Image。

上面我是拿BOL直接貼上去，直接重點就是資料小於8000 bytes和資料大於8000 bytes兩種類型，

下面我將用實例來闡述SQL SERVER怎麼儲存LOB資料類型。

create table tblunitsVarN(c1 varchar(8000))
create table tblunitsVarMax(c1 varchar(max))
 
insert into tblunitsVarN select REPLICATE('a', 8000)
insert into tblunitsVarMax select REPLICATE('a', 8000)

--查看資料表或索引大小和其他資訊
select
    OBJECT_NAME([object_id])        AS TableName,
    alloc_unit_type_desc,
    page_count                      ,
    avg_page_space_used_in_percent  ,
    record_count,
    min_record_size_in_bytes,
    max_record_size_in_bytes,
    forwarded_record_count          
from sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.tblunitsVarN'), NULL, NULL , 'DETAILED');

只要資料長度不超過8000 bytes，SQL SERVER一律配置IN_ROW_DATA。

-->8001 bytes
insert into tblunitsVarMax select REPLICATE(CONVERT(varchar(max), 'a'), 8001);

超過8000 bytes,則Pointer(指標)儲存IN_ROW_DATA，但其餘字串全部存放LOB_DATA，兩個page之間透過pointer連結。

來看一下真實世界資料表可能如下

create table myfiletable
(
Serial bigint identity 
,filename nvarchar(30)
,filecontent nvarchar(max)
,filessummary nvarchar(4000)
)

insert into myfiletable select N'我是RiCo', REPLICATE(N'x', 2000), REPLICATE(N'x', 2000) --< 8060 bytes
insert into myfiletable select N'RiCo是我', REPLICATE(N'x', 2000), REPLICATE(N'x', 2000) --< 8060 bytes

全部配置IN_ROW_DATA。

insert into myfiletable select N'RiCo技術農場', REPLICATE(cast(N'x' as nvarchar(max)), 2030), REPLICATE(N'x', 2000) --> 8060 bytes
insert into myfiletable select N'RiCo Studio', REPLICATE(cast(N'x' as nvarchar(max)), 2030), REPLICATE(N'x', 2000) --> 8060 bytes

一筆資料大小超過8060 bytes，SQL SERVER把超過部分配置LOB_DATA。

現在我模擬一個較大資料

insert into myfiletable select N'波多野結依', REPLICATE(cast(N'x' as nvarchar(max)), 300000), REPLICATE(N'x', 2000)

LOB_DATA漂亮花費page:78(這數字真的是巧合~XD)。

資料表使用硬碟空間

EXEC sp_spaceused N'dbo.myfiletable';

才5筆資料就要712kb，如果資料量一多，buffer pool應該會很忙，當然如果你記憶體大到不像話的可能對效能沒感覺。

 

來看一下page內容

--查詢資料對應page_id
select * 
from myfiletable 
cross apply sys.fn_PhysLocCracker(%%physloc%%)--%%physloc%%顯示資料物理位置

dbcc traceon(3604)

dbcc page(7,1, 5076475, 3)

結論:

LOB資料類型應不應該儲存在SQL SERVER呢?

答案:是看你檔案大小和你要怎麼使用。

 

如果你決定使用SQL SERVER來存放LOB資料類型，檔案大小都超過650MB，

並且使用者也很常查詢該資料表，那麼這就是一個嚴重錯誤決定，

因為LOB會耗用大量硬碟空間、page、記憶體、索引碎片和頻繁的頁面分割及buffer pool flush(假設記憶題不足)，

同時你也要考慮索引維護成本和資料庫備份成本，這決定對查詢效能造成很大影響，因為每一page可以存放的資料變少了，

也對很常查詢欄位影響更大。實務上，我面對錯誤決定的處理，

是請對方切資料表來處理(透過TEXTIMAGE_ON指定其他檔案群組)，把LOB資料類型切開，

如有需要在透過join來處理，雖然還是無法避免存取LOB的成本，

但卻可改善常用查詢欄位(如filename、filetitle..等)密度(每一page可以存放更多資料)，

索引維護也可以避免LOB資料類型所帶來的困擾，但我還是不太建議使用SQL SERVER來存放LOB資料類型存。

 

如果你決定使用SQL SERVER來存放LOB資料類型，檔案大小都不超過512kb，

並且使用者也很常查詢該資料表，那麼這決定並沒有什麼不妥。

 

如果你決定使用SQL SERVER來存放(B)LOB資料類型，檔案大小都超過2GB，

並且使用者也很常查詢該資料表，那麼請好好想想Keep Simple、Keep Stupid，

雖然FILESTREAM可以加快存取資料效能，但使用FILESTREAM也是有許多優點和缺點。

 

如果你決定使用Azure SQL Database來存放(B)LOB資料類型，檔案大小都超過2GB，

並且使用者也很常查詢該資料表，那麼記得信用卡額度調高一點。

 

Varchar(n)、varchar(max)、nvarchar(n)、nvarchar(max)、varbinary(max)、

FILESTREAM都各有優缺點(但別讓nvarchar(max)、varchar(max)遍地開花)，

請針對你的檔案大小和使用行為好好決定，因為決定錯誤會造成很大的效能問題。

 

參考

In-Row Data

Row-Overflow Data Exceeding 8 KB

ntext、text 和 image (Transact-SQL)

Table and Index Organization

What’s the Point of Using VARCHAR(n) Anymore?

sys.dm_db_index_physical_stats (Transact-SQL)

INFO：DBCC PAGE 命令的說明

SQL Server - Find Physical Location of Records

Where are my Rows? – Using the %%physloc%% Virtual Column

[SQL SERVER][Performance] text 資料類型轉換 varchar(max)的影響