[SQL SERVER]SQL2016-資料庫範圍組態選項

SQL Server 2016提供幾個資料庫範圍組態設定選項,

方便我們可以更簡單控制應用程式行為,

尤其預設啟用T4199和清除個人資料庫快取真是太讚了。

資料庫範圍組態可以把行為影響縮小至資料庫而非伺服器層級,

我以前遇到效能問題只能在TSQL範圍使用hint或option逐一修改調整,

現在真的不用那麼麻煩了。

--查看資料庫範圍組態
select * from  sys.database_scoped_configurations 

For Secondary表示AG支援這些選項,你可以把第二復本和主要復本設定不同選項。

Max DOP:該組態從Server範圍移至Database範圍,這改變真是太棒了,

不然之前都在TSQL 範圍使用option控制Max DOP。

Legacy Cardinality Estimation:使用舊的基數演算法(等同T9481使用version 70的CE model),

避免新基數演算法造成效能低落。

PARAMETER_SNIFFING:啟用該選項等同T4136或OPTIMIZE FOR UNKNOWN提示,

當然也還有其他方法來避開參數探測所引起的效能問題,但現在你可以更簡單控制該行為。

 

模擬一下參數探測:第一次查詢大資料量

第二次查詢小資料量,但因為參數探測導致估計值和實際值差距太大。

--關閉參數探測(將使用靜態資料取代所有本地變數和參數)
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = off;

--清除個人資料庫快取,不影響其他資料庫
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

關閉後在次執行相同SP

第一次查詢大資料量

第二次查詢小資料量

QUERY_OPTIMIZER_HOTFIXES:等同預設啟用T4199,我只能說晚啟用總比不啟用來的好。

Enjoy SQL Server 2016

 

參考

What's New in Database Engine

ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)

Database Scoped Configuration

基數估計 (SQL Server)

[SQL SERVER][Memo]了解參數探測行為(1)

[SQL SERVER][Memo]了解參數探測行為(2)

[SQL SERVER]Hekaton-- 新基數演算法