德瑞克：SQL Server 學習筆記

[Hyper-V] 安裝 Windows 11 Pro 虛擬機器（使用本機帳戶 Local Account 登入）

noreply@blogger.com (DerrickLaLa) — Fri, 1 Aug 2025 16:56:00 +0800

[Hyper-V] 安裝 Windows 11 Pro 虛擬機器（使用本機帳戶 Local Account 登入）

更新日期：2025/07/30

ISO 檔版本：

Win11_24H2_Chinese_Traditional_x64.iso（Windows 11 24H2），是 Win11 24H2 版本。

前言

因測試需求，需在 Hyper-V 中安裝 Windows 11 Pro 虛擬機器。
本文紀錄整個安裝流程，特別是如何跳過 Microsoft 帳號登入，改為使用本機帳戶(Local Account) 登入。

虛擬機設定重點

1. 虛擬機世代選擇：第 2 代

Windows 11 基於 UEFI 架構，需選擇第 2 代虛擬機。
若選錯為第 1 代，會無法順利啟動安裝。

2. 網路設定：未連線

在安裝過程中，有些場景無法使用網路，或不希望強制登入 Microsoft 帳號，因此建議先設定為「未連線」。
後續可手動安裝虛擬交換器與網路驅動程式。

3. 啟用信賴平台模組（TPM）以符合 Windows 11 安裝需求

在 Hyper-V 虛擬機器中安裝 Windows 11 時，若未正確設定虛擬硬體，可能會出現以下錯誤：

「此電腦目前不符合 Windows 11 系統需求」

這通常是因為虛擬機未啟用信賴平台模組（TPM）等 Windows 11 安裝所需的條件。

解決方法：啟用 TPM （Enable Trusted Platform Module）

在 Hyper-V 管理員中

設定 [安全性]，勾選 [啟用信賴平台模組]

說明補充:

TPM 2.0 是 Windows 11 強制需求之一，用於增強安全性與裝置驗證。

在 Hyper-V 管理員中:

啟用信賴平台模組（Enable Trusted Platform Module）
啟用安全啟動（Enable Secure Boot）啟用安全啟動（Enable Secure Boot）

若仍遇到問題，可一併檢查以下設定：

虛擬處理器需至少為 2 核心
記憶體配置建議至少 4 GB
開機硬碟為 UEFI 格式（VHDX），非 MBR
設定第 2 代虛擬機

4. 跳過 Microsoft 帳號登入，使用本機帳戶的操作方式

在安裝過程中，預設會要求連線網路並登入 Microsoft 帳號。若要改為使用本機帳戶 (Local Account)，請依以下步驟操作：

在 [這是正確的國家或地區嗎] 畫面

按下鍵盤快捷鍵: Shift + F10
呼叫出命令提示字元視窗

可以看到命令提示字元視窗

在命令提示字元視窗，輸入以下指令後按下 Enter：

oobe\bypassnro

備註

oobe\bypassnro：解除網路要求，允許在無網路狀態下進一步選擇本機帳戶建立
OOBE 是什麼？

Out‑of‑Box Experience（開箱體驗）的縮寫，指的是使用者第一次開機或啟動新裝置時，從建立帳戶、接受授權條款、選擇語言鍵盤，到連接網路與設定隱私偏好等一系列畫面與流程
在 Windows 11 中，OOBE 就是第一次進入系統前的那段初始設定流程。這套流程被設計來逐步引導使用者完成必要設置，降低操作複雜性並提供清晰指示

系統會自動重開機，重新進入 OOBE 安裝流程。

在 [讓我們將您連線到網路] 畫面

點選下方的 [我沒有網際網路]

在 [誰會使用這台裝置] 畫面

輸入使用者帳號，也就是本機帳戶(Local Account)
設定密碼

安裝完成後，可以使用本機帳戶(Local Account) 登入到 Windows 11 系統。

小結

使用 Hyper-V 安裝 Windows 11 Pro 並不複雜，但若需跳過 Microsoft 帳號強制登入，記得使用 oobe\bypassnro 指令來解鎖本機帳戶設定選項，這對無網路環境或測試需求是實用的。

[參考文件]

Windows 11安全啟動
下載 Windows 11: 選擇下載適用於 x64 裝置的 Windows 11 磁碟映像 (ISO)
NO Microsoft Account Needed! Windows 11 Setup Bypass (LATEST 6/2025)

Install SQL Server 2019 on Windows Server 2019: 安裝抓圖

noreply@blogger.com (DerrickLaLa) — Wed, 22 Apr 2020 12:30:00 +0800

版本資訊：
SQL Server 2019 RTM - 15.0.2070.41 (X64)

Build Information	Note
SQL Server Version	SQL Server 2019
Product Version	15.0.2070.41
Product Level	RTM
Product Edition	Enterprise Edition (64-bit)
Database Internal Version	904

SQL Server 2019 的圖型介面工具：SSMS、SSDT，需要額外下載安裝。

版本資訊

-- 01_SQL Server 2019 + Windows Server 2019

-- 02_資料庫相容性層級：150

安裝抓圖

Install SQL Server 2019 on Windows Server 2019

https://photos.app.goo.gl/TwioHWoq2GrKnbpBA

Query version, edition and update level

V2: Query SQL Server system and build information

適用於 SQL Server 2019
加上作業系統平台
使用 SQL Server 2017 新增加的 sys.dm_os_host_info

-- V2_SQL Server system and build information
-- Applies to: SQL Server 2019
  
SELECT @@SERVERNAME 'InstanceName',
 CASE WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductVersion')) like '8%' THEN 'SQL Server 2000'
  WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductVersion')) like '9%' THEN 'SQL Server 2005'
  WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductVersion')) like '10.0%' THEN 'SQL Server 2008'
  WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductVersion')) like '10.5%' THEN 'SQL Server 2008 R2'
  WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductVersion')) like '11%' THEN 'SQL Server 2012'
  WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductVersion')) like '12%' THEN 'SQL Server 2014'
  WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductVersion')) like '13%' THEN 'SQL Server 2016'
  WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductVersion')) like '14%' THEN 'SQL Server 2017'
  WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductVersion')) like '15%' THEN 'SQL Server 2019'
  ELSE 'unknown'
  END AS 'SQLServerVersion ', 
 CASE WHEN SERVERPROPERTY('IsClustered') = 1 AND SERVERPROPERTY('IsHadrEnabled') = 1 THEN 'Failover Cluster + Availability Groups'
  WHEN SERVERPROPERTY('IsClustered') = 1 THEN 'Failover Cluster'
  WHEN SERVERPROPERTY('IsHadrEnabled') =1 THEN 'Availability Groups'
  ELSE 'unknown'
 END AS 'High-Availability', 
 host_distribution 'HostDistribution', host_release 'HostRelease',
 SERVERPROPERTY('ProductVersion') 'ProductVersion',
 SERVERPROPERTY('ProductLevel') 'ProductLevel',
 SERVERPROPERTY('Edition') 'ProductEdition',
 SERVERPROPERTY('ProductUpdateLevel') 'ProductUpdateLevel', 
 SERVERPROPERTY('ProductBuildType') 'ProductBuildType', 
 SERVERPROPERTY('ProductUpdateReference') 'ProductUpdateReference', 
 DATABASEPROPERTYEX('master','Version') 'DatabaseInternalVersion'
FROM sys.dm_os_host_info 
GO

Reference

Install SQL Server 2017 on Windows Server 2016
https://sharedderrick.blogspot.com/2017/10/install-sql-server-2017-on-windows.html

[SQL Server] Query version, edition and update level - 版本、版次、編號
https://sharedderrick.blogspot.com/2017/08/sql-server-query-version-edition-and.html

Sysprep 變更 SID(Security Identifier)，以 Windows Server 2019 為例

noreply@blogger.com (DerrickLaLa) — Tue, 21 Apr 2020 08:30:00 +0800

Sysprep 變更SID(Security Identifier)，以 Windows Server 2019 為例

在使用虛擬機器時，採取「複製虛擬機器」的方式來建置多台虛擬機器，可簡化安裝建置的時間。

但每台虛擬機器的電腦安全性識別碼(SID，Security Identifie)，因為採取複製方式，導致其電腦安全性識別碼都重複相同。

影響

這個問題會影響到各種工作群組環境中的安全性，而且卸除式媒體安全性在具有多重相同電腦 SID 的網路中也會受到影響。

使用系統準備(Sysprep)工具，會將電腦設定為重新開機時，建立新的電腦安全性識別碼(SID)。

系統準備(Sysprep)的功能

從 Windows 映像中移除電腦特定的資訊，包括電腦的安全識別碼（SID）。這可讓您捕獲映射並將它套用到其他電腦。這就是所謂的電腦一般化(generalizing)。
從 Windows 映像卸載電腦特有的驅動程式。
將電腦設定為開機至 OOBE，以準備電腦以傳遞給客戶。
可讓您將回應檔案（自動安裝）設定新增至現有的安裝。

注意事項

在完成變更SID(Security Identifier)後，系統相關的組態會重置，例如：
設定時區、鍵盤、電腦名稱、登入帳戶的密碼、「Windows 需要重新啟用」等。

Sysprep 圖形化使用者介面

示範環境：
Windows Server 2019 Datacenter 版本。

-- 01_使用whoami，user參數，查詢SID

-- 02_使用檔案總管，執行C:\Windows\System32\Sysprep目錄下的Sysprep.exe。

使用參數：
系統清理動作：進入系統全新體驗(ODBE)。
勾選：一般化。
關機選項：重新開機。

Sysprep 會重設安全性識別碼 (SID)，清除任何系統還原點以及刪除事件記錄檔

-- 03_再度使用whoami查詢，觀察到：電腦名稱、安全性識別碼 (SID)等，都已經重設。

參考資料

Sysprep 圖形化使用者介面，變更SID(Security Identifier)，以 Windows Server 2016 為例
http://sharedderrick.blogspot.com/2017/02/sysprep-sidsecurity-identifier-windows.html

Sysprep 命令列，變更SID，以 Windows Server 2016 為例
http://sharedderrick.blogspot.com/2017/02/sysprep-sid-windows-server-2016.html

Sysprep （系統準備）總覽
https://docs.microsoft.com/zh-tw/windows-hardware/manufacture/desktop/sysprep--system-preparation--overview

Windows server 2019 安裝

noreply@blogger.com (DerrickLaLa) — Mon, 20 Apr 2020 20:20:00 +0800

在 Windows Server 2019 Datacenter 版本上設定基礎環境，準備作為安裝 SQL Server 2019 版本之用

版本：Windows Server 2019 Datacenter
編號：10.0.17763.1098

安裝歷程的抓圖

https://photos.app.goo.gl/pXy5wueKYuCG58NE6

Enabled SET XACT_ABORT ON in SSMS, 在 SSMS 啟用 XACT_ABORT

noreply@blogger.com (DerrickLaLa) — Sat, 1 Dec 2018 18:03:00 +0800

延續前一篇文章：BEGIN TRAN with XACT_ABORT

若需要以此 SSMS 為單元，所建立的 Connection，都要啟用 XACT_ABORT，可以在 SSMS 執行以下設定

影響範圍

啟用後，僅套用在此 SSMS 新建立的 Connection，不影響 SQL Server。
由此 SSMS 新建立的 Connection，即刻適用。但對既有的 Connection 則不受影響。

若有需要，可以關閉 SSMS，再度開啟 SSMS。

Enabled SET XACT_ABORT ON in SSMS,
在 SSMS 啟用 XACT_ABORT

01. 在 SSMS ，點選上方工具選單，Tools ，選擇 Options。

02. 在 Options 視窗

點選 Query Execution，SQL Server，Advanced。
或是，在 Search Options 方塊，輸入： XACT 關鍵字。

03. 在 Advanced \ Specify the advanced execution settings 對話方塊

勾選: SET XACT_ABORT ON

-- figure 01_SSMS_SET_XACT_ABORT_ON

影響範圍

啟用後，僅套用在此 SSMS 新發出的 Connection，不影響 SQL Server。
由此 SSMS 發出的新 Connection，即刻適用。但對既有的 Connection 則不受影響。

若有需要，可以關閉 SSMS，再度開啟 SSMS。

Reference

BEGIN TRAN with XACT_ABORT
http://sharedderrick.blogspot.com/2018/11/begin-tran-with-xactabort.html

查詢是否有啟用 XACT_ABORT 選項
http://sharedderrick.blogspot.com/2008/09/xactabort.html

設定 user options 伺服器組態選項
https://docs.microsoft.com/zh-tw/sql/database-engine/configure-windows/configure-the-user-options-server-configuration-option?view=sql-server-2017

SET XACT_ABORT (Transact-SQL)
https://docs.microsoft.com/zh-tw/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-2017

@@TRANCOUNT (Transact-SQL)
https://docs.microsoft.com/zh-tw/sql/t-sql/functions/trancount-transact-sql?view=sql-server-2017

BEGIN TRAN with XACT_ABORT

noreply@blogger.com (DerrickLaLa) — Tue, 27 Nov 2018 22:12:00 +0800

若需求是

遇到執行階段的錯誤，希望系統能自動回復 Rollback 整個交易 Transaction。
則請使用 BEGIN TRAN 並且設定 SET XACT_ABORT ON。

當 SET XACT_ABORT 是 ON 時，如果 Transact-SQL 陳述式產生執行階段錯誤，就會終止和回復整個交易。

例如：資料表不存在、違反外部索引鍵等。

有啟用 SET XACT_ABORT ON，當遇到執行階段錯誤時，系統自動回復 Rollback 目前交易，查詢 @@TRANCOUNT = 0。

BEGIN TRAN with enable XACT_ABORT

當 SET XACT_ABORT 是 ON 時，

如果 Transact-SQL 陳述式產生執行階段錯誤，就會終止和回復整個交易。
例如：資料表不存在、違反外部索引鍵等
SET XACT_ABORT 不會影響到如語法錯誤之類的編譯錯誤。

01. 僅使用 BEGIN TRAN，執行 Script:

資料表 Products 存在，第 1 句 SELECT 執行成功，有回傳資料。
但 SELECT 資料表 Products_XXX 不存在，執行時系統回傳 Error Message 208: Invalid object name。

問題：

第 2 句 SELECT 執行失敗，但先前已經啟用 BEGIN TRAN，這會造成什麼影響呢？

-- figure 01_BEGIN TRAN without XACT_ABORT

02. 使用 @@TRANCOUNT ，檢視目前連線已經啟用 BEGIN TRAN 的數量。

回傳 1，表示已經有 1 個 BEGIN TRAN 存在。
若沒有 BEGIN TRAN，預設回傳 0。

-- figure 02_Have opened a transaction, @@TRANCOUNT = 1

03. 若嘗試去關閉該連線。

在 SSMS 產生警示的對話方塊，該連線有未認可的交易(uncommitted transaction)。
點選 No，可以退回 Rollback Transaction。

-- figure 03_Get_uncommitted_transaction

BEGIN TRAN with SET XACT_ABORT ON

當 SET XACT_ABORT 是 ON 時，

如果 Transact-SQL 陳述式產生執行階段錯誤，就會終止和回復整個交易。
例如：資料表不存在、違反外部索引鍵等
SET XACT_ABORT 不會影響到如語法錯誤之類的編譯錯誤。

01. 在新的連線上，使用 SET XACT_ABORT ON 與 BEGIN TRAN。

資料表 Products 存在，第 1 句 SELECT 執行成功，有回傳資料。
但 SELECT 資料表 Products_XXX 不存在，執行時系統回傳 Error Message 208: Invalid object name。

問題：

先前已經使用 SET XACT_ABORT ON 與 BEGIN TRAN。其中，第 2 句 SELECT 因資料表不存在而執行失敗，這會造成什麼影響呢？

-- figure 11_Get_Error

02. 使用 @@TRANCOUNT ，檢視目前連線已經啟用 BEGIN TRAN 的數量。

回傳 0，表示沒有任何 BEGIN TRAN 存在。
若沒有 BEGIN TRAN，預設回傳 0。

這是因為當 SET XACT_ABORT 是 ON 時，
如果 Transact-SQL 陳述式產生執行階段錯誤，就會終止和回復整個交易。

-- figure 12_ @@TRANCOUNT = 0

03. 對比兩者的差異

兩邊 SQL Script 執行後，在第 2 句 SELECT 資料表 Products_XXX 不存在，系統回傳 Error Message 208: Invalid object name。
有啟用 SET XACT_ABORT ON，當遇到執行階段錯誤時，系統自動回復 Rollback 目前交易，查詢 @@TRANCOUNT = 0。

-- figure 21_Compare

Sample Code

20181126_BEGIN TRAN with XACT_ABORT

https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

Reference

SET XACT_ABORT (Transact-SQL)

https://docs.microsoft.com/zh-tw/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-2017

@@TRANCOUNT (Transact-SQL)

https://docs.microsoft.com/zh-tw/sql/t-sql/functions/trancount-transact-sql?view=sql-server-2017

查詢是否有啟用 XACT_ABORT 選項

http://sharedderrick.blogspot.com/2008/09/xactabort.html

設定 user options 伺服器組態選項

https://docs.microsoft.com/zh-tw/sql/database-engine/configure-windows/configure-the-user-options-server-configuration-option?view=sql-server-2017

Find out Statistics used to Execution Plan from Plan Cache - DBCC TRACEON(8666), 由 Plan Cache 取得執行計畫所使用的統計值

noreply@blogger.com (DerrickLaLa) — Mon, 29 Oct 2018 20:33:00 +0800

啟用 TRACEON(8666) 後，在該 Session 就可以:

由 Plan Cache (Memory) 取得 Execution Plan 所使用的 Statistics
由 Include Actual Execution Plan / Display Estimated Execution Plan ，取得 Execution Plan 所使用的 Statistics
在 Execution Plan 上會額外提供 Internal Debugging Information(內部偵錯用訊息) ，這包含了 Statistics 細節資訊。

例如，可以取得:
StatName、ColName、ModCtr、RowCount、Threshold、Reason 等資訊。

此為 Undocumented Trace Flags: DBCC TRACEON(8666)
適用版本: SQL Server 2008 以上的環境

檢視 ModTrackingInfo 元素

StatName: _WA_Sys_00000003_014935CB
ColName: ContactName
ModCtr: 91
RowCount: 91
Threshold: 500
Reason: small table

Find out Statistics used to Execution Plan from Plan Cache - DBCC TRACEON(8666),

由 Plan Cache 找出執行計畫所使用的統計值 - - DBCC TRACEON(8666)

本文以下介紹 2 種用法:

(1) 由 Plan Cache (Memory) 取得 Execution Plan 所使用的 Statistics -
啟用 TRACEON(8666) ，並執行 sys.dm_exec_query_plan

用法：

啟用 DBCC TRACEON(8666) 後，可以使用 sys.dm_exec_query_plan 由 Plan Cache ，取得 Query Optimizer 在 Execution Plan 所使用的 Statisitics。

功能：

將內部偵錯用訊息存放於 Execution Plan 內，這包含了 Statistics 細節資訊。

若沒有啟用 DBCC TRACEON(8666) ，則 sys.dm_exec_query_plan 顯示為 NULL，沒有資料。

(2) 由 Include Actual Execution Plan / Display Estimated Execution Plan ，取得 Execution Plan 所使用的 Statistics
- 啟用 TRACEON(8666)

用法：

若搭配指定的 SQL Query，使用 Include Actual Execution Plan / Display Estimated Execution Plan，可以取得 Query Optimizer 在 Execution Plan 所使用的 Statistics。

功能：

將內部偵錯用訊息存放於 Execution Plan 內，這包含了 Statistics 細節資訊。

(1) 由 Plan Cache (Memory) 取得 Execution Plan 所使用的 Statistics
啟用 TRACEON(8666) ，並執行 sys.dm_exec_query_plan

用法：啟用 DBCC TRACEON(8666) 後，可以使用 sys.dm_exec_query_plan 由 Plan Cache ，取得 Query Optimizer 在 Execution Plan 所使用的 Statisitics。
功能：將內部偵錯用訊息存放於 Execution Plan 內，這包含了 Statistics 細節資訊。
若沒有啟用 DBCC TRACEON(8666) ，sys.dm_exec_query_plan 的 [Statistics] 顯示 NULL，沒有資料。

01. 使用 dm_db_stats_properties: 傳回資料表的所有 Statistics 屬性

Table: Customers, Orders
已經存在 15 個 Statistics

-- figure 01_dm_db_stats_properties Returns properties of statistics for the specified database object

02. 執行 SQL Query。

僅查詢單一資料表。
DBCC FREEPROCCACHE: 清除 Plan Cache，此為非必要，在本範例中，也僅是用於重置環境參數。

-- figure 02_Removes plan cache and Query Table with Auto Create Statistics

03. 啟用 TRACEON(8666) ，並執行 sys.dm_exec_query_plan

用法：啟用 DBCC TRACEON(8666) 後，可以使用 sys.dm_exec_query_plan 由 Plan Cache ，取得 Query Optimizer 在 Execution Plan 所使用的 Statisitics。
功能：將內部偵錯用訊息存放於 Execution Plan 內，這包含了 Statistics 細節資訊。
若沒有啟用 DBCC TRACEON(8666) ，sys.dm_exec_query_plan 的 [Statistics] 顯示 NULL，沒有資料。

SQL Script 說明

啟用 DBCC TRACEON(8666)
執行 sys.dm_exec_query_plan，取得 Execution Plan 所使用的 Statistics
關閉 DBCC TRACEOFF(8666)

-- DBCC TRACEON(8666) -- Find out Statistics used to Execution Plan from Plan Cache.
DBCC TRACEON(8666)

SELECT TOP 100 st.text [TSQL], DB_NAME(st.dbid) [DB], 
 qs.last_elapsed_time/1000000.0 [LastElapsedTime(sec)],
 qs.last_worker_time/1000000.0 [LastCPUTime(sec)],
 qs.last_logical_reads LastLogicalReads,
 qs.last_physical_reads LastPhysicalRead,
 qs.last_logical_writes LastlogicalWrites,
 qs.last_rows LastRows,
 cp.usecounts [ExecutionCount], cp.size_in_bytes/1024.0 [PlanSize(KB)],
 cp.cacheobjtype [CacheObject], cp.objtype [ObjType], qs.plan_generation_num [PlanRecompile],
 qp.query_plan.query('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
 (/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:ParameterList/ns:ColumnReference[@Column])') [ParameterList],
 qp.query_plan.query('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
 (/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:InternalInfo/ns:EnvColl/ns:Recompile)') [Statistics], 
 qp.query_plan [QueryPlan], cp.plan_handle, qs.last_execution_time, qs.creation_time
FROM sys.dm_exec_cached_plans cp INNER JOIN sys.dm_exec_query_stats qs
 ON cp.plan_handle = qs.plan_handle
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.dbid = DB_ID('Northwind')
-- CHARINDEX ('Products', st.text) >0
-- st.text LIKE '%xxx%'
ORDER BY qs.last_execution_time DESC;

DBCC TRACEOFF(8666)
GO

-- figure 03_ DBCC TRACEON(8666) -- Find out Statistics used to Execution Plan from Plan Cache.

04. 檢視回傳的結果集。

QueryPlan: 存放 Execution Plan。
Statistics: 存放 XML 格式的 Statistics。

-- figure 04_Get_Statistics_XML

05. 檢視 QueryPlan 內的 Execution Plan

點選左邊的 SELECT operator，滑鼠右鍵，選擇 Properties。
在右邊 Properties 窗格，展開 Internal Debugging Information。
展開與觀察 Recompile 與 ModTrackingInfo。

-- figure 05_SELECT_Properties_Internal Debugging Information

06. 在 ModTractkingInfo，展開顯示更多資訊關於 Statistics 資訊。

-- figure 06_ModTrackingInfo

07. 檢視由 Plan Cache 取得的 Statistics 資訊

以 XML 格式呈現的 Statistics。

-- figure 07_Execution_Plan_Statistics

08. 檢視 ModTrackingInfo 元素

StatName: _WA_Sys_00000003_014935CB
ColName: ContactName
ModCtr: 91
RowCount: 91
Threshold: 500
Reason: small table

-- figure 08_Statistics_ModTrackingInfo

09. 以 XML 格式來檢視 Execution Plan

InternalInfo 元素

-- figure 09_XML

10. 以 XML 格式來檢視 Execution Plan

Recompile 元素

-- figure 10_XML

11. 以 XML 格式來檢視 Execution Plan

ModTrackingInfo 元素

-- figure 11_XML_ModTrackingInfo

(2) 由 Include Actual Execution Plan / Display Estimated Execution Plan ，取得 Execution Plan 所使用的 Statistics
-- 啟用 TRACEON(8666)

先前介紹的是由 Plan Cache (Memory) 取得 Execution Plan 所使用的 Statistics。

以下方式，則是應用於 Include Actual Execution Plan / Display Estimated Execution Plan ，取得在 Execution Plan 所使用的 Statistics。

DBCC TRACEON(8666) -- Find out Statistics used to Execution Plan

用法：若搭配指定的 SQL Query，使用 Include Actual Execution Plan / Display Estimated Execution Plan，可以取得 Query Optimizer 在 Execution Plan 所使用的 Statistics。
功能：將內部偵錯用訊息存放於 Execution Plan 內，這包含了 Statistics 細節資訊。

01. 執行以下 SQL Query

啟用 DBCC TRACEON(8666)
使用 Include Actual Execution Plan / Display Estimated Execution Plan
執行 SQL Query ，僅查詢單一資料表
關閉 DBCC TRACEOFF(8666)

-- figure 21_Actual_Execution_Plan_DBCC TRACEON(8666)

02. 檢視 Execution Plan

點選左邊的 SELECT operator，滑鼠右鍵，選擇 Properties。
在右邊 Properties 窗格，展開 Internal Debugging Information。
展開與觀察 Recompile 與 ModTrackingInfo。

可以看到 1 份 Statistics 資訊，檢視 ModTrackingInfo 元素

StatName: _WA_Sys_00000003_014935CB
ColName: ContactName
ModCtr: 91
RowCount: 91
Threshold: 500
Reason: small table

-- figure 22_Execution_Plan_SELECT_Properties_Internal Debugging Information

03. 執行複雜 SQL Query

此 SQL Query，使用 Inner Join，查詢 2 張資料表，並使用 Where 條件式。
觀察 Recompile 與 ModTrackingInfo。
可以看到當時 2 張資料表上的全部 Statistics 資訊，約：15 個。

在此案例 (Inner Join) 中，啟用 DBCC TRACEON(8666) 後，會取得當時參與資料表上的全部 Statistics 資訊。但仍可分析像是條件式 Where、On 等 SQL statement，得知當時所使用的 Statistics。

-- figure 31_Display all statistics

04. 以 XML 格式來檢視 Execution Plan

觀察 Orders 資料表的 Statistics。

-- figure 32_XML_Orders

05. 以 XML 格式來檢視 Execution Plan

觀察 Orders 資料表的 Statistics。

-- figure 33_XML

06. 以 XML 格式來檢視 Execution Plan

觀察 Customers 資料表的 Statistics。

-- figure 34_XML_Customers

Sample Code

20181029_Find out Statistics_TRACEON_8666
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

Reference

Use Autostat (AUTO_UPDATE_STATISTICS), 認識自動更新統計資料 (1)
http://sharedderrick.blogspot.com/2018/10/statistics-use-autostat.html

Use Autostat (AUTO_UPDATE_STATISTICS), 認識自動更新統計資料 (2)
http://sharedderrick.blogspot.com/2018/10/use-autostat-autoupdatestatistics-2.html

Find out Statistics used to Execution Plan in SQL Server 2017, 找出執行計畫所使用的統計值
http://sharedderrick.blogspot.com/2018/10/find-out-statistics-used-to-execution.html

Find out Statistics used to Execution Plan in SQL Server 2017, 找出執行計畫所使用的統計值

noreply@blogger.com (DerrickLaLa) — Fri, 19 Oct 2018 22:55:00 +0800

SQL Server 2017 強化 Execution Plan，增加 [OptimizerStatsUsage] 屬性，可提供 Query Optimizer 在此 Execution Plan 所使用的 Statistics。

無論是 Include Actual Execution Plan / Display Estimated Execution Plan，都可以使用 [OptimizerStatsUsage] 屬性。

經過測試: SQL Server 2017 版本，並且 COMPATIBILITY_LEVEL = 120 以上適用。

Find out Statistics used to Execution Plan in SQL Server 2017
找出 Execution Plan (執行計畫) 所使用的 Statistics (統計值)

SQL Server 2017 強化 Execution Plan，增加 [OptimizerStatsUsage] 屬性，可提供 Query Optimizer 在此 Execution Plan 所使用的 Statistics。

無論是 Include Actual Execution Plan / Display Estimated Execution Plan，都可以使用 [OptimizerStatsUsage] 屬性。

初步測試: SQL Server 2017 版本，並且 COMPATIBILITY_LEVEL = 120 以上適用。SQL Server 2014 (x.12)

01. 使用 dm_db_stats_properties: 傳回資料表的所有 Statistics 屬性

Table: Customers, Orders

-- figure 01_dm_db_stats_properties_Customers_Orders

02. 執行 SQL Query

僅查詢單一資料表。

無論是 Include Actual Execution Plan / Display Estimated Execution Plan，都可以使用 [OptimizerStatsUsage] 屬性。
點選左邊最後一個 SELECT Operator，滑鼠右鍵，選擇 Properties。

自動產生 Statistics。

依據預設值，有啟用資料庫選項：AUTO_CREATE_STATISTICS。
WHERE 條件式使用 Column: ContactName，Query Optimizer 將自動為其建立 Statistics。

-- figure 11_SQL_Server 2017_SELECT

03. 在 Properties 視窗，可以看到 [OptimizerStatsUsage] 區塊，使用到 1 個 Statistics，包含以下資訊：

Database: [Northwind]
LastUpdate: 2018-10-19T21:01:05.14
ModificationCount: 0
SamplingPercent: 100
Schema: dbo
Statistics: _WA_Sys_00000003_014935CB
Table: [Customers]

-- figure 12_SELECT_Properties_OptimizerStatsUsage

04. 檢視 Show Execution Plan XML

觀察 OptimizerStatsUsage

-- figure 13_Show_Execution_Plan_XML

05. 執行 SQL Query

Table Join 2 個

無論是 Include Actual Execution Plan / Display Estimated Execution Plan，都可以使用 [OptimizerStatsUsage] 屬性。
點選左邊最後一個 SELECT Operator，滑鼠右鍵，選擇 Properties。

-- figure 14_SQL_Server 2017_SELECT

06. 在 Properties 視窗，可以看到 [OptimizerStatsUsage] 區塊，使用到 3 個 Statistics，包含以下資訊：

[1]

Database: [Northwind]
LastUpdate: 2018-10-19T21:01:05.14
ModificationCount: 0
SamplingPercent: 100
Schema: dbo
Statistics: _WA_Sys_00000003_014935CB
Table: [Customers]

[2]

Database: [Northwind]
LastUpdate: 2008-09-13T13:06:45.8
ModificationCount: 0
SamplingPercent: 100
Schema: dbo
Statistics: [CustomerID]
Table: [Orders]

[3]

Database: [Northwind]
LastUpdate: 2008-09-13T13:06:45.77
ModificationCount: 0
SamplingPercent: 100
Schema: dbo
Statistics: [PK_Customers]
Table: [Customers]

-- figure 15_SELECT_Properties_OptimizerStatsUsage

07. 檢視 Show Execution Plan XML

觀察 OptimizerStatsUsage

-- figure 16_Show_Execution_Plan_XML

08. 使用 SSMS 檢視 Index 與 Statistics

-- figure 17_SSMS_Statistics

Sample Code

20181019_Find out Statistics used to Execution Plan
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

Use Autostat (AUTO_UPDATE_STATISTICS), 認識自動更新統計資料 (2)

noreply@blogger.com (DerrickLaLa) — Tue, 16 Oct 2018 07:21:00 +0800

從 SQL Server 2016 (13.x) 開始，並且 COMPATIBILITY_LEVEL = 130 (資料庫相容性層級 )，系統採用 Dynamic auto update statistics threshold (動態自動更新統計資料閾值)

使用降低、動態的統計資料更新臨界值。此臨界值會根據資料表中的資料列數目來做出調整。
這是以 1000 乘以目前資料表基數的平方根來計算。

例如

如資料表包含 2 百萬個資料列，則計算結果是 SQRT(1000 * 2000000) = 44721.359。

案例

資料表有 10 萬筆資料列，若要達到自動更新統計資訊的 threshold

需要有 10, 000 筆以上被異動。
計算： SELECT SQRT (1000 * 100000) = 10000

在具有 100,000 行的表中，觸發更新統計信息的閾值已降至 10 %。
在一個包含 1,000,000 行的表中，它只需要大約 3.2 % 的異動，即可觸發自動更新統計信息。

透過這項變更，大型資料表上的統計資料會經常更新。

不過，如果資料庫的相容性層級低於 130，便會套用 SQL Server 2014 (12.x) 臨界值。

追蹤旗標 2371

在資料庫相容性層級低於 130 之下，從 SQL Server 2008 R2 開始至 SQL Server 2014 (12.x)。或是，從 SQL Server 2016 (13.x) 至 SQL Server 2017，使用追蹤旗標 2371 時，
SQL Server 就會使用降低、動態的統計資料更新臨界值。此臨界值會根據資料表中的資料列數目來做出調整。

對於較小的表格，閾值仍將是大約 20% 的百分比。
當每個表超過 25,000 行時，動態規則才會啟動，並且隨著行數的增加，更改的記錄百分比變得越來越低。

更新 Statistics (統計值) 的 Threshold

	SQL Server 2014 (12.x)	SQL Server 2016 (13.x)
Threshold	資料表基數的 20% + 500	以 1000 乘以目前資料表基數的平方根
Example	(100000 * 0.2) + 500 = 20,500	SQRT (1000 * 100000) = 10,000

-- figure 53_Excel_Threshold

-- figure 54_Threshold_Chart

Statistics: Use Autostat (AUTO_UPDATE_STATISTICS ), 認識自動更新統計資料 (2)

00. 依據預設值，資料庫屬性：Auto Create Statistics 與 Auto Update Statistics 已經啟用。

-- figure 51_Autostat

01. 檢查 COMPATIBILITY_LEVEL

確認 COMPATIBILITY_LEVEL = 130
將固定自動更新統計資料閾值變更為動態自動更新統計資料閾值。
SQL Server 會使用降低、動態的統計資料更新臨界值。此臨界值會根據資料表中的資料列數目來做出調整。
這是以 1000 乘以目前資料表基數的平方根計算。

更新 Statistics 的 Threshold(臨界值)是：

如果您的資料表包含 2 百萬個資料列，則計算結果是 SQRT(1000 * 2000000) = 44721.359。

透過這項變更，大型資料表上的統計資料會經常更新。
不過，如果資料庫的相容性層級低於 130，便會套用 SQL Server 2014 (12.x) 臨界值。

追蹤旗標 2371

將固定自動更新統計資料閾值變更為動態自動更新統計資料閾值。
在資料庫相容性層級低於 130 之下，從 SQL Server 2008 R2 開始至 SQL Server 2014 (12.x)。
或是，從 SQL Server 2016 (13.x) 至 SQL Server 2017，使用追蹤旗標 2371 時，
SQL Server 就會使用降低、動態的統計資料更新臨界值。此臨界值會根據資料表中的資料列數目來做出調整。
當每個表超過 25,000 行時，動態規則才會啟動，並且隨著行數的增加，更改的記錄百分比變得越來越低。
例如

在具有 100,000 行的表中，觸發更新統計信息的閾值已降至 10%。
在一個包含 1,000,000 行的表中，它只需要大約 3.2 % 的異動，即可觸發自動更新統計信息。

注意事項

從 SQL Server 2016 (13.x) 開始及根據資料庫相容性層級 130，此行為由引擎控制，追蹤旗標 2371 沒有任何作用。

-- figure 01_COMPATIBILITY_LEVEL 130

02. 建立資料表 Statistics_100K

建立資料表: Statistics_100K。
建立 Primary Key 與 Clustered Index 在 column: rid。
新增 10 萬筆資料列。

-- figure 02_Create Table Statistics_100K

03. 使用 dm_db_stats_properties: 傳回資料表的所有 Statistics 屬性

dm_db_stats_properties: SQL Server 2008 版本提供，可用於傳回資料表的所有統計資料屬性。
建立 Clustered Index 在 column: rid，系統自動一併建立 Statistics: PK_Statistics_100K_rid。
但 Statistics 目前都是 NULL，尚未產生。

-- dm_db_stats_properties: Returns properties of statistics for the specified database object

SELECT sch.name [Schema], t.name [Table], c.name [Column], stat.stats_id, stat.name [Statistics], sp.last_updated, 
 sp.rows, sp.rows_sampled, sp.steps, sp.unfiltered_rows, stat.filter_definition, sp.modification_counter, 
 stat.auto_created, stat.user_created
FROM sys.stats stat CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) sp
 INNER JOIN sys.tables t ON t.object_id = stat.object_id
 INNER JOIN sys.stats_columns sc ON (sc.stats_id = sp.stats_id AND sp.object_id = sc.object_id)
 INNER JOIN sys.columns c ON (c.column_id = sc.column_id AND c.object_id = t.object_id)
 INNER JOIN sys.schemas sch ON t.schema_id = sch.schema_id
WHERE t.name = 'Statistics_100K';  
GO

-- figure 03_dm_db_stats_properties

04. 使用 SSMS 檢視 Index 與 Statistics。

-- figure 04_Primary_Key_Index_Statisitics

05. 自動產生 Statistics。

依據預設值，有啟用資料庫選項：AUTO_CREATE_STATISTICS。
WHERE 條件式使用 Column: Cdata，Query Optimizer 將自動為其建立 Statistics。

AUTO_CREATE_STATISTICS

查詢最佳化工具會視需要針對查詢述詞中的單一資料行建立統計資料，以便改善查詢計劃和查詢效能。
這些單一資料行統計資料是在查詢最佳化工具編譯查詢時所建立的。
它只會針對尚未成為現有統計資料物件之第一個資料行的資料行建立單一資料行統計資料。
預設值是 ON。

-- figure 05_Auto Create Statistics_ Cdata column

06. 檢查資料表上的 Statistics。

自動建立 Statistics: _WA_Sys_00000003_55DFB4D9

Column: Cdata
rows: 100000
rows_sampled: 89782
steps： 154
unfiltered_rows: 100000
modification_counter: 0
auto_created: 1

注意：
Statistics: PK_Statistics_100K_rid 內容值尚未產生，仍是 NULL

-- figure 06_dm_db_stats_properties

07. 使用 SSMS 檢視 Index 與 Statistics。

已經新增第 2 個 Statistics: _WA_Sys_00000003_55DFB4D9

-- figure 07_SSMS_Statistics

08. 檢視 Statistics 的 Details 頁籤。

Statistics: PK_Statistics_100K_rid 內容值尚未產生，仍是 NULL

-- figure 08_Statistics_Detail_NULL

09. 檢視 Statistics 的 Details 頁籤。

Statistics: _WA_Sys_00000003_55DFB4D9

-- figure 09_Auto_Create_Statistics_Detail_Data

10. 使用 DBCC SHOW_STATISTICS 與 WITH STAT_HEADER

DBCC SHOW_STATISTICS 可檢視指定 Index, Statistics, Column 的統計資訊。
WITH STAT_HEADER，僅檢視 STAT_HEADER。
與 dm_db_stats_properties 結果相同。

-- figure 10_DBCC SHOW_STATISTICS_WITH STAT_HEADER

11. 建立 Create NonClustered Index on [TransactionID]

-- figure 11_Create NonClustered Index on [TransactionID]

12. 檢視 Statistics：

產生第 3 個 statistics。
建立 Index，系統自動建立對應的 Statistics: IX_Statistics_100K_TransactionID。

Statistics: IX_Statistics_100K_TransactionID

Column: TransactionID
rows: 100000
rows_sampled:100000
steps：2
unfiltered_rows: 100000
modification_counter: 0
auto_created: 0

注意：

PK_Statistics_100K_rid 尚未產生 Statistics

-- figure 12_建立 Index_系統自動建立對應的 Statistics

13. 使用 SSMS 檢視 Index 與 Statistics。

已經新增第 3 個 Statistics: IX_Statistics_100K_TransactionID

-- figure 13_Index_Statistics

14. 使用 DBCC SHOW_STATISTICS 與 WITH STAT_HEADER

檢視指定 Statistics: IX_Statistics_100K_TransactionID 的統計資訊

-- figure 14_DBCC SHOW_STATISTICS_WITH STAT_HEADER

Dynamic auto update statistics threshold 動態自動更新統計資料閾值

認識 AUTO_UPDATE_STATISTICS 自動更新統計資料

從 SQL Server 2016 (13.x) 開始，並在資料庫相容性層級 130，
採用 Dynamic auto update statistics threshold(動態自動更新統計資料閾值)
SQL Server 會使用降低、動態的統計資料更新臨界值。此臨界值會根據資料表中的資料列數目來做出調整。
這是以 1000 乘以目前資料表基數的平方根來計算。

例如，如果您的資料表包含 2 百萬個資料列，則計算結果是 SQRT(1000 * 2000000) = 44721.359。

透過這項變更，大型資料表上的統計資料會經常更新。

不過，如果資料庫的相容性層級低於 130，便會套用 SQL Server 2014 (12.x) 臨界值。

追蹤旗標 2371

在資料庫相容性層級低於 130 之下，從 SQL Server 2008 R2 開始至 SQL Server 2014 (12.x)。
或是，從 SQL Server 2016 (13.x) 至 SQL Server 2017，使用追蹤旗標 2371 時，
SQL Server 就會使用降低、動態的統計資料更新臨界值。此臨界值會根據資料表中的資料列數目來做出調整。

對於較小的表格，閾值仍將是大約 20% 的百分比。

當每個表超過 25,000 行時，動態規則才會啟動，並且隨著行數的增加，更改的記錄百分比變得越來越低。

例如

在具有 100,000 行的表中，觸發更新統計信息的閾值已降至 10 %。
在一個包含 1,000,000 行的表中，它只需要大約 3.2 % 的異動，即可觸發自動更新統計信息。

以本案例來看：

資料表有 10 萬筆資料列，若要達到自動更新統計資訊的 threshold
需要有 10, 000 筆以上被異動。
計算： SELECT SQRT (1000 * 100000) = 10000

-- figure 52_Threshold

01. 本次案例，所使用的 SQL Query：

資料表有 10 萬筆，回傳 10,310 筆。
Clustered Index Scan, Return 10,310 rows
TransactionID < 110311
Execution Plan: Missing Index

-- figure 21_常用的 Query 與條件式

02. 有 Missing Index

-- figure 22_Missing_Index

檢視 Execution Plan

Cluster Index Scan
Reads: 1,289
Est Rows: 10,310
Actual Rows: 10,310

-- figure 23_Plan_Explorer

03. 刪除資料

刪除 10,300 筆。
已達到更新 Statistics 的 threshold(臨界值)

SQL Server 2016(13.x) 版本，改為以 1000 乘以目前資料表基數的平方根來計算

This is calculated as the square root of the product of 1000 and the current table cardinality.
SELECT SQRT (1000 * 100000) = 10000
TransactionID < 110301
Return 10,300 rows

-- figure 24_Modify Data to over threshold_SQRT

04. 檢視 Statistics：

超過臨界值，但 Statistics 仍未改變 !

Statistics: IX_Statistics_100K_TransactionID
Column: TransactionID
rows: 100000
rows_sampled:100000
steps：2
unfiltered_rows: 100000
modification_counter: 10300
auto_created: 0
Total: 89,700 rows: SELECT 100000 - 10300 -- 89700

-- figure 25_dm_db_stats_properties_超過臨界值，但 Statistics 仍未改變

使用使用 DBCC SHOW_STATISTICS 與 WITH STAT_HEADER

-- figure 26_DBCC SHOW_STATISTICS_WITH STAT_HEADER

05. 再度執行 SQL Query

何時自動更新 AUTO_UPDATE_STATISTICS ?

開啟自動更新統計資料選項 AUTO_UPDATE_STATISTICS 時，Query Optimizer 會判斷 statistics 何時過期，然後在查詢使用 statistics 時加以更新。
在編譯查詢及執行快取 query plan 之前，Query Optimizer 會檢查是否有過期的 statistics 。
在編譯查詢之前，Query Optimizer 會使用查詢述詞中的資料行、資料表和索引檢視表來判斷哪些 statistics 可能已過期。
在執行快取 query plan 之前， Database Engine 會確認 query plan 是否參考最新的 statistics。

換言之:

statistics 過期後，要等到下次執行 Query(SIUD)，查詢 statistics 時，一併更新 statistics。
如果 statistics 已過期，Query Optimizer 就會先等候並執行更新 statistics，然後再 compile 並執行查詢。

-- figure 31_何時更新 statistics_下次執行 Query(SIUD)

改為使用 Index Seek

-- figure 32_何時更新 statistics_下次執行 Query(SIUD)

檢視 Execution Plan

Index Seek
Reads: 33
Est Rows: 10
Actual Rows: 10

-- figure 33_Plan_Explorer

06. 檢視 Statistics

Statistics 已經更新

modification_counter: 0

(1) Statistics: IX_Statistics_100K_TransactionID

Column: TransactionID
rows: 89700
rows_sampled:88296
steps：80
unfiltered_rows: 89700
modification_counter: 0
auto_created: 0

(2) Statistics: PK_Statistics_100K_rid 尚未產生 Statistics

(3) _WA_Sys_00000003_55DFB4D9 仍是未更新

-- figure 34_dm_db_stats_properties

07. 使用 DBCC SHOW_STATISTICS 與 WITH STAT_HEADER

檢視指定 Statistics: IX_Statistics_100K_TransactionID 的統計資訊

-- figure 35_DBCC SHOW_STATISTICS_WITH STAT_HEADER

Statistics 更新前後的比較

	Before	After
Index	Clustered Index Scan	Index Seek
Read	1,289	33
Est Rows	10,310	10

更新過的 Statistics，能夠反映實際的資料特性，幫助 Query Optimizer 選擇合適的 Index。

若以 Read IO 來看，更新 Statistics 後，幫助 Query Optimizer 選擇合適的 Index
效能提升：97.44 %。

SELECT ABS(33-1289)/1289.0 * 100 -- 97.439800

Sample Code

20181011_Use Autostat (AUTO_UPDATE_STATISTICS)
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

Reference

Use Autostat (AUTO_UPDATE_STATISTICS), 認識自動更新統計資料 (1)
http://sharedderrick.blogspot.com/2018/10/statistics-use-autostat.html

Use Autostat (AUTO_UPDATE_STATISTICS), 認識自動更新統計資料 (1)

noreply@blogger.com (DerrickLaLa) — Thu, 11 Oct 2018 00:42:00 +0800

(1) 更新 Statistics

更新過的 Statistics，能夠反映實際的資料特性，幫助 Query Optimizer 選擇合適的 Index。
若 Statistics 尚未更新，導致無法選擇更有效率的 Index。

(2) 啟用 AUTO_UPDATE_STATISTICS

Query Optimizer 會判斷 statistics 何時過期。
statistics 過期後，要等到下次執行 Query(SIUD)，查詢 statistics 時，一併更新 statistics。
如果 statistics 已過期，Query Optimizer 就會先等候並執行更新 statistics，然後再 compile 並執行查詢。

(3) Statistics 更新的 Threshold(臨界值)

包含 SQL Server 2014 (12.x)，以及先前版本，Statistics 更新的 Threshold是：

若 Table Cardinality(資料表基數) 在評估 Statistics 時為 500 或更小的數值，將會在每 500 次修改之後更新。
若資料表基數在評估 Statistics 時為超過 500 的數值，將會在每 20% + 500 的修改次數之後更新。
在 tempdb 資料庫中，如果資料表基數是小於 6，資料表的資料表每 6 修改，就會自動更新。
Table Variables為基數的變更，不會觸發自動更新統計資料。

例如：

資料表有 10 萬筆資料列，若要達到自動更新統計資訊的臨界值
需要有 20,500 筆以上被異動。
計算：(100000 * 0.2) + 500 = 20500

(4) 建立 Index，系統自動建立對應的 Statistics。

Statistics: Use Autostat (AUTO_UPDATE_STATISTICS ), 認識自動更新統計資料

01. 檢查 COMPATIBILITY_LEVEL

確認 COMPATIBILITY_LEVEL = 120
包含 SQL Server 2014 (12.x)，以及先前版本，會使用變更資料列的百分比作為臨界值。

Statistics 更新的 Threshold(臨界值)是：

若資料表基數在評估統計資料時為 500 或更小的數值，將會在每 500 次修改之後更新。
若資料表基數在評估統計資料時為超過 500 的數值，將會在每 20% + 500 的修改次數之後更新。

-- figure 01_COMPATIBILITY_LEVEL = 120

依據預設值，資料庫屬性：Auto Create Statistics 與 Auto Update Statistics 已經啟用。

-- figure 檢視 Autostat

02. 建立資料表 Statistics_100K

建立資料表: Statistics_100K。
建立 Primary Key 與 Clustered Index 在 column: rid。
新增 10 萬筆資料列。

-- figure 02_CREATE TABLE Statistics_100K - Insert

03. 檢視資料特性：

-- figure 03_View_Table_Data

04. 使用 dm_db_stats_properties: 傳回資料表的所有 Statistics 屬性

dm_db_stats_properties: SQL Server 2008 版本提供，可用於傳回資料表的所有統計資料屬性。
建立 Clustered Index 在 column: rid，系統自動一併建立 Statistics: PK_Statistics_100K_rid。

但 Statistics 目前都是 NULL，尚未產生。

-- dm_db_stats_properties: Returns properties of statistics for the specified database object

SELECT sch.name [Schema], t.name [Table], c.name [Column], stat.stats_id, stat.name [Statistics], sp.last_updated, 
 sp.rows, sp.rows_sampled, sp.steps, sp.unfiltered_rows, stat.filter_definition, sp.modification_counter, 
 stat.auto_created, stat.user_created
FROM sys.stats stat CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) sp
 INNER JOIN sys.tables t ON t.object_id = stat.object_id
 INNER JOIN sys.stats_columns sc ON (sc.stats_id = sp.stats_id AND sp.object_id = sc.object_id)
 INNER JOIN sys.columns c ON (c.column_id = sc.column_id AND c.object_id = t.object_id)
 INNER JOIN sys.schemas sch ON t.schema_id = sch.schema_id
WHERE t.name = 'Statistics_100K';  
GO

-- figure 04_dm_db_stats_properties_PK

05. 使用 SSMS 檢視 Index 與 Statistics。

-- figure 05_SSMS_Statistics

06. 自動產生 Statistics。

依據預設值，有啟用資料庫選項：AUTO_CREATE_STATISTICS。
WHERE 條件式使用 Column: Cdata，Query Optimizer 將自動為其建立 Statistics。

AUTO_CREATE_STATISTICS

查詢最佳化工具會視需要針對查詢述詞中的單一資料行建立統計資料，以便改善查詢計劃和查詢效能。
這些單一資料行統計資料是在查詢最佳化工具編譯查詢時所建立的。
它只會針對尚未成為現有統計資料物件之第一個資料行的資料行建立單一資料行統計資料。
預設值是 ON。

-- figure 06_Auto Create Statistics Cdata column

07. 檢查資料表上的 Statistics。

自動建立 Statistics: _WA_Sys_00000003_226010D3

Column: Cdata
rows: 100000
rows_sampled: 88612
steps： 131
unfiltered_rows: 100000
modification_counter: 0
auto_created: 1

注意：

Statistics: PK_Statistics_100K_rid 內容值尚未產生，仍是 NULL

-- figure 07_自動建立 Statistics_但PK 還沒有產生 Statistics

08. 使用 SSMS 檢視 Index 與 Statistics。

已經新增第 2 個 Statistics: _WA_Sys_00000003_226010D3

-- figure 08_SSMS_Statistics

09. 檢視 Statistics 的 Details 頁籤。

-- figure 09_SSMS_Statistics_Details

10. 使用 DBCC SHOW_STATISTICS 與 WITH STAT_HEADER

DBCC SHOW_STATISTICS 可檢視指定 Index, Statistics, Column 的統計資訊。
WITH STAT_HEADER，僅檢視 STAT_HEADER。
與 dm_db_stats_properties 結果相同。

-- figure 10_DBCC SHOW_STATISTICS_WITH STAT_HEADER

11. 建立 Create NonClustered Index on [TransactionID]

-- figure 11_Create NonClustered Index on TransactionID

12. 檢視 Statistics：

產生第 3 個 statistics。
建立 Index，系統自動建立對應的 Statistics: IX_Statistics_100K_TransactionID。

Statistics: IX_Statistics_100K_TransactionID

Column: TransactionID
rows: 100000
rows_sampled:100000
steps：2
unfiltered_rows: 100000
modification_counter: 0
auto_created: 0

注意：

PK_Statistics_100K_rid 尚未產生 Statistics

-- figure 12_建立 Index_系統自動建立對應的 Statistics

13. 使用 SSMS 檢視 Index 與 Statistics。

已經新增第 3 個 Statistics: IX_Statistics_100K_TransactionID

-- figure 13_SSMS_Index_Statistics

14. 使用 SSMS 檢視 Statistics 的 Details 資訊。

-- figure 14_SSMS_Index_Statistics_Details

15. 使用 DBCC SHOW_STATISTICS 與 WITH STAT_HEADER

檢視指定 Statistics: IX_Statistics_100K_TransactionID 的統計資訊

-- figure 15_DBCC SHOW_STATISTICS_Index_Statistics

Statistics 尚未更新，導致 Query Optimizer 無法選擇有效率的 Index

認識 AUTO_UPDATE_STATISTICS 自動更新統計資料

包含 SQL Server 2014 (12.x)，以及先前版本，會使用變更資料列的百分比作為臨界值。
與資料表中的資料列數無關。

臨界值是：

若資料表基數在評估統計資料時為 500 或更小的數值，將會在每 500 次修改之後更新。
若資料表基數在評估統計資料時為超過 500 的數值，將會在每 20% + 500 的修改次數之後更新。

以本案例來看：

Threshold is 20% + 500

Count: (100000 * 0.2) + 500 = 20500

資料表有 10 萬筆資料列，若要達到自動更新統計資訊的臨界值

需要有 20,500 筆以上被異動。

01. 本次案例，所使用的 SQL Query：

資料表有 10 萬筆，回傳 20,520 筆。
Clustered Index Scan, Return 20,520 rows
TransactionID < 120521
Execution Plan: Missing Index

-- figure 21_Clustered Index Scan_20520 rows

02. 有 Missing Index

-- figure 22_Clustered Index Scan_20520 rows_Missing_Index

檢視 Execution Plan

Cluster Index Scan
Reads: 1,289
Est Rows: 20,520
Actual Rows: 20,520

-- figure 41_Execution_Plan

03. 刪除資料

刪除 20,300 筆。
但仍未達到更新 Statistics 的 threshold(臨界值)

Threshold is 20% + 500，需要超過 20,500 筆。

-- figure 23_Delete 20300 rows

04. 執行 SQL Query，僅回傳 1 row。

-- figure 24_Query_1_row

05. 檢視 Statistics：

Under Threshold (未達臨界值)

(1) Statistics:IX_Statistics_100K_TransactionID 保持不變

累計： modification_counter: 20300
Total: 79,700 rows, (SELECT 100000 - 20300 -- 79700)

(2) Statistics:PK_Statistics_100K_rid 已經自動產生

Column: rid
rows: 100000
rows_sampled:87284
steps：37
unfiltered_rows: 100000
modification_counter: 20300
auto_created: 0

-- figure 25_Statistics PK_Statistics_100K_rid 已經自動產生_未達臨界值_保持不變

06. 再刪除 10 rows。

再刪除 10 rows。
累計刪除: 20300 + 10 = 20,310 筆。

但 Threshold is 20% + 500，需要超過 20,500 筆。

-- figure 26_再刪除 10 rows_Under Threshold_未達臨界值

07. 檢視 Statistics：

仍是 Under Threshold (未達臨界值)，沒有更新 Statistics
累計刪除: 20300 + 10 = 20,310 筆。
Total: 79,690 rows, (SELECT 100000 - 20300 - 10 -- 79690)
modification_counter: 20310

但 Threshold is 20% + 500，需要超過 20,500 筆。

-- figure 27_modification_counter_未達臨界值

08. 檢視資料表

剩下 79,690 rows。

-- figure 28_Table_剩下_79690

09. 執行先前範例 SQL Query:

資料量已大幅刪除，回傳資料量減少，推論可以改用 Index Seek
但卻仍是使用 Clustered Index Scan, 僅 Return 210 rows
Missing Index
回傳筆數，由 20,520 減少至 210 rows

因為 Statistics 尚未更新，Query Optimizer 仍選擇原先的 Index。

-- figure 29_仍是使用 Clustered Index Scan

檢視 Execution Plan

Cluster Index Scan
Reads: 1,030
Est Rows: 20,520
Actual Rows: 210

預估有 20,520 筆，但實際卻僅有 210 筆。

-- figure 42_Execution_Plan_Delete_尚未更新_Statistics

異動資料，超過更新 Statistics 的 threshold

01. 再刪除 200 rows

Over Threshold (超過臨界值)

再刪除 200 rows，Over Threshold (超過臨界值)
TransactionID < 120511
累計：已經刪除 20,510 rows

(20,300 + 10 + 200)

Threshold is 20% + 500，需要超過 20,500 筆。

-- figure 31_再刪除 200 rows_Over Threshold (超過臨界值)

02. 檢視 Statistics：

超過臨界值，但 Statistics 仍未改變 !

Statistics: IX_Statistics_100K_TransactionID

Column: TransactionID
rows: 100000
rows_sampled:100000
steps：2
unfiltered_rows: 100000
modification_counter: 20510
auto_created: 0

-- figure 32_超過臨界值，但 Statistics 仍未改變

03. 再度執行 SQL Query

AUTO_UPDATE_STATISTICS

開啟自動更新統計資料選項 AUTO_UPDATE_STATISTICS 時，Query Optimizer 會判斷 statistics 何時過期，然後在查詢使用 statistics 時加以更新。
在編譯查詢及執行快取 query plan 之前，Query Optimizer 會檢查是否有過期的 statistics 。
在編譯查詢之前，Query Optimizer 會使用查詢述詞中的資料行、資料表和索引檢視表來判斷哪些 statistics 可能已過期。
在執行快取 query plan 之前， Database Engine 會確認 query plan 是否參考最新的 statistics。

換言之:

statistics 過期後，要等到下次執行 Query(SIUD)，查詢 statistics 時，一併更新 statistics。
如果 statistics 已過期，Query Optimizer 就會先等候並執行更新 statistics，然後再 compile 並執行查詢。

-- figure 33_何時更新 statistics_ 下次執行 Query(SIUD)

檢視 Execution Plan

Index Seek
Reads: 33
Est Rows: 10
Actual Rows: 10

-- figure 43_Execution_Plan_已更新_Statistics

04. 檢視 Statistics

Statistics 已經更新
modification_counter: 0

(1)　Statistics: IX_Statistics_100K_TransactionID

Column: TransactionID
rows: 79490
rows_sampled:79490
steps：3
unfiltered_rows: 79490
modification_counter: 0
auto_created: 0

(2)　Statistics: PK_Statistics_100K_rid

Column: rid
rows: 79490
rows_sampled:79490
steps：3
unfiltered_rows: 79490
modification_counter: 0
auto_created: 0

(3) _WA_Sys_00000003_226010D3 仍是未更新

-- figure 34_Statistics_已經更新

05. 使用 DBCC SHOW_STATISTICS 與 WITH STAT_HEADER

檢視指定 Statistics: IX_Statistics_100K_TransactionID 的統計資訊

-- figure 35_DBCC SHOW_STATISTICS_Statistics_已經更新

Statistics 更新前後的比較

	Before	After
Index	Clustered Index Scan	Index Seek
Read	1,030	33
Est Rows	20,520	10

更新過的 Statistics，能夠反映實際的資料特性，幫助 Query Optimizer 選擇合適的 Index。

若以 Read IO 來看，更新 Statistics 後，幫助 Query Optimizer 選擇合適的 Index
效能提升：96.8 %。

SELECT ABS(33-1030)/1030.0 * 100 -- 96.796100

Sample Code

20181011_Use Autostat (AUTO_UPDATE_STATISTICS)
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

Reference

AUTO_UPDATE_STATISTICS Option
https://docs.microsoft.com/zh-tw/sql/relational-databases/statistics/statistics?view=sql-server-2017

Controlling Autostat (AUTO_UPDATE_STATISTICS) behavior in SQL Server
https://support.microsoft.com/en-us/help/2754171/controlling-autostat-auto-update-statistics-behavior-in-sql-server

Changes to automatic update statistics in SQL Server – traceflag 2371
https://blogs.msdn.microsoft.com/saponsqlserver/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371/

Default auto statistics update threshold change for SQL Server 2016
https://blogs.msdn.microsoft.com/psssql/2016/10/04/default-auto-statistics-update-threshold-change-for-sql-server-2016/

Statistics Used by the Query Optimizer in Microsoft SQL Server 2008
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd535534(v=sql.100)

Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
https://technet.microsoft.com/en-us/library/cc966419.aspx

How Statistics in SQL Server determines performance
https://blogs.msdn.microsoft.com/ggaurav/2015/04/10/how-statistics-in-sql-server-determines-performance/

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
Recompilation threshold (RT)
https://technet.microsoft.com/en-us/library/cc966425.aspx

Statistical maintenance functionality (AutoStats) in SQL Server
"change threshold"
https://support.microsoft.com/en-us/help/195565/statistical-maintenance-functionality-autostats-in-sql-server

Dynamic SQL: Pass dynamic TABLE name variable, 動態傳遞資料表名稱變數

noreply@blogger.com (DerrickLaLa) — Mon, 17 Sep 2018 20:15:00 +0800

消失的 Execution Plan

當使用 Dynamic SQL 動態傳遞 TABLE name(資料表名稱) 變數

無論是使用 sp_executesql 或 EXEC，都要等到執行階段，動態傳入資料表名稱後，Query Optimizer 方能依據傳入的資料表資訊，分析 SQL 陳述式，判斷出取得所需資料的最有效方式。

影響

Display Estimated Execution Plan 無法正確顯示。
Plan Cache 無法直接觀察。

看不到實際執行的 Execution Plan!

Dynamic SQL: sp_executesql, Pass dynamic TABLE name variable,
動態傳遞資料表名稱變數

01. 建立使用 Dynamic SQL: sp_executesql 動態傳遞資料表變數的 Stored Procedure。

@tsql

需要執行的 SQL 陳述式或批次，必須使用 Unicode 字串。

@Parms

N'@TransactionDate datetime'，此為參數定義的 Unicode 字串。，由參數名稱和資料類型組成。

使用 QUOTENAME()

附加傳回 [] 分隔符號，以便讓輸入字串成為有效的 [SQL Server] 分隔識別碼。
限制為 128 個 Unicode 字元。
用於處理 SQL Injection(資料隱碼) 問題，驗證 Dynamic SQL 使用的所有變數都已正確處理。

-- figure 01_Proc_pr_DynamicTable

02. 點選 Display Estimated Execution Plan，嘗試檢視 Execution Plan。

檢視使用 Dynamic SQL: sp_executesql 動態傳遞資料表名稱變數的 Stored Procedure。

-- figure 02_Display Estimated Execution Plan

03. 消失的 Execution Plan!

無可分析!

因為要等到執行階段，動態傳入資料表名稱後，Query Optimizer 方能依據傳入的資料表資訊，分析 SQL 陳述式，判斷出取得所需資料的最有效方式。

-- figure 03_Display Estimated Execution Plan

04. 改用 Include Actual Execution Plan。

點選 Include Actual Execution Plan，執行 2 段 SQL 陳述式。

-- figure 04_Exec_Proc_Include Actual Execution Plan

05. 檢視 Stored Procedure 執行結果。

資料表變數: TransactionHistory

回傳 0 筆資料列。

資料表變數: TransactionHistoryArchive

回傳 215 筆資料列。

-- figure 05_Exec_Proc_Include Actual Execution Plan

06. 檢視 Execution Plan。

資料表變數: TransactionHistory

回傳 0 筆資料列。
Clustered Index Scan。
有 Missing Index。

資料表變數: TransactionHistoryArchive

回傳 215 筆資料列。
Clustered Index Scan。
有 Missing Index。

-- figure 06_View_Actual_Execution_Plan

07. 檢視 Plan Cache

1st: CREATE PROC pr_DynamicTable @tbname sysname, ...

LastElapsedTime, last_logical_reads 等，都是 NULL。
ExecutionCount 累積到 2。
ObjType: Proc。

2nd 與 3rd: SET STATISTICS XML OFF 與 SET STATISTICS XML ON

這是因為啟用了 Include Actual Execution Plan 功能，SQL Server 使用 XML 文件格式來產生 SQL 陳述式執行狀況的詳細資料。
LastElapsedTime, last_logical_reads 等，都是 NULL。
ObjType: Prepared。

4th: (@TransactionDate datetime) SELECT [TransactionID],..

LastElapsedTime, last_logical_reads 等，都有顯示出資料。
這是實際執行 SQL 陳述式後產生的效能資料。
點選 [QueryPlan]，可以看到實際執行的 Execution Plan。
ObjType: Prepared。

5th: (@TransactionDate datetime) SELECT [TransactionID],..

LastElapsedTime, last_logical_reads 等，都有顯示出資料。
這是實際執行 SQL 陳述式後產生的效能資料。
點選 [QueryPlan]，可以看到實際執行的 Execution Plan。
ObjType: Prepared。

-- figure 07_Plan_Cache

08. 可以看到 [ParameterList] 與 [QueryPlan] 都有實際資料。

-- figure 08_Plan_Cache

消失的 Execution Plan

看不到實際執行的 Execution Plan!

-- figure 09_1 Missing Execution Plan

Execution Plan 藏在這裡 ...

-- figure 09_2

09. 重複執行多次 Stored Procedure。

資料表變數: TransactionHistory，再執行 10 次。
資料表變數: TransactionHistoryArchive，再執行 20 次。

-- figure 11_Run many times

10. 觀察 Plan Cache。

1st: ExecutionCount 累積到 32。

ObjType: Proc。

2nd: 資料表變數: TransactionHistoryArchive，ExecutionCount 累積到 21。

ObjType: Prepared。

3rd: 資料表變數: TransactionHistory，ExecutionCount 累積到 11。

ObjType: Prepared。

-- figure 12_Plan_Cache

11. 輸入更多不同的 Parameter value

資料表變數: TransactionHistory

但日期更換為: 2013-06-10, 2013-06-11, 2013-06-12

資料表變數: TransactionHistoryArchive

但日期更換為: 2013-06-10, 2013-06-11, 2013-06-12

-- figure 13_ Different parameter value with the same table

12. 觀察 Plan Cache。

1st: ExecutionCount 累積到 38。

ObjType: Proc。

2nd: 資料表變數: TransactionHistoryArchive，ExecutionCount 累積到 24。

使用相同資料表，雖然 Parameter value不同，但可以 Reuse Execution Plan。
ObjType: Prepared。

3rd: 資料表變數: TransactionHistory，ExecutionCount 累積到 14。

使用相同資料表，雖然 Parameter value不同，但可以 Reuse Execution Plan。
ObjType: Prepared。

Dynamic SQL: sp_executesql

ObjType: Prepared
使用 Parameter Sniffing 機制來 Re-Use Execution Plan。
必須面對 Parameter Sniffing 所帶來的 Side Effects。

-- figure 14_Plan Cache

Dynamic SQL: EXEC, Pass dynamic TABLE name variable
動態傳遞資料表名稱變數

改用 EXEC。

01. 建立使用 Dynamic SQL: EXEC 動態傳遞資料表變數的 Stored Procedure。

@tsql: 需要執行的 SQL 陳述式或批次。

-- figure 71_Proc_Pass dynamic TABLE name to EXEC

02. 點選 Display Estimated Execution Plan，消失的 Execution Plan，無可分析!

因為要等到執行階段，動態傳入資料表名稱後，Query Optimizer 方能依據傳入的資料表資訊，分析 SQL 陳述式，判斷出取得所需資料的最有效方式。

03. 改用 Include Actual Execution Plan。

點選 Include Actual Execution Plan，執行以下多段 SQL 陳述式。

-- figure 72_Exec_Proc_pr_DynamicTable_EXEC

04. 觀察 Execution Plan。

1st: CREATE PROC pr_DynamicTable @tbname sysname, ...

LastElapsedTime, last_logical_reads 等，都是 NULL。
ExecutionCount 累積到 2。
ObjType: Proc。

2nd: 是重複執行 'TransactionHistoryArchive', '2013-07-30';

ExecutionCount 累積到 21。
ObjType: Adhoc。

3rd: 是重複執行 'TransactionHistory', '2013-07-30';

ExecutionCount 累積到 11。
ObjType: Adhoc。

4th~9th: 資料表變數: TransactionHistory 或是 TransactionHistoryArchive

但日期更換為: 2013-06-10, 2013-06-11, 2013-06-12
ExecutionCount 都是 1

相同資料表，但 Parameter value不同，產生的 hash value 也不相同。
導致無法 Reuse Execution Plan。

ObjType: Adhoc。

-- figure 73_Plan_Cache

05. 觀察 Execution Plan，檢視 SQL statement 的 Parameter value。

Parameter value不同，產生的 hash value 也不相同，導致無法 Reuse Execution Plan。

Dynamic SQL: EXEC

採取比對 hash 值

相同，就能重複使用 Execution Plan。
不同，產生新的 Execution Plan。
ObjType: Adhoc。

-- figure 74_Plan_Cache

QUOTENAME()

功能：傳回 Unicode 字串，且附加了分隔符號，以便使輸入字串成為有效的 [SQL Server] 分隔識別碼。
限制為 128 個字元。大於 128 個字元的輸入會傳回 NULL。

語法
QUOTENAME ( 'character_string' [ , 'quote_character' ] )

'quote_character'
這是用來當做分隔符號的單字元字串。
它可以是單引號 ( ' )、左或右方括弧 ( [] )、雙引號 ( " )、左或右括弧 ( () )、
大於或小於符號 ( >< )、左或右大括弧 ( {} ) 或反勾號 ( ` )。

如果提供了無法接受的字元，則會傳回 NULL。
如果未指定 quote_character，就會使用方括號。

用於面對 SQL Injection(資料隱碼)，建議搭配 QUOTENAME() 或 REPLACE()，來驗證 Dynamic SQL 使用的所有變數都已正確處理。

Sample Code

20180917_Dynamic SQL Pass dynamic TABLE name variable
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (5)

noreply@blogger.com (DerrickLaLa) — Fri, 7 Sep 2018 07:16:00 +0800

延續前一篇文章：[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (4)

一談起 Dynamic SQL，許多人以為要使用 sp_executesql 才是對的、效能好的，但其實這是囫圇吞棗後的謬誤。

sp_executesql，仍使用 Parameter Sniffing 機制來 Reuse Execution Plan

有機會發生重複使用了效能不佳 Execution Plan，陷入效能更糟的窘境。

Dynamic SQL: EXEC，採取 Ad hoc Query 機制

採取比對 hash 值來重複使用 Execution Plan，為各個輸入 parameter 值，建立效能佳的 Execution Plan。

方法總比困難多

在本案例中：

與 Dev Team 攜手合作，理解 Business 商務面的需求，改寫為兩隻符合各自需求的 Stored Procedure，會是執行效能最佳的解決方案!

繼續討論 Parameter Sniffing 的因應之道：

使用 EXEC ... WITH RECOMPILE
使用 sp_recompile
修改 Stored Procedure，使用 Local Variables
修改 Stored Procedure，使用 WITH RECOMPILE
修改 Stored Procedure，使用 OPTION RECOMPILE
改寫 Stored Procedure，使用 UNION ALL
改寫 Stored Procedure，使用 IF ELSE
改寫 Stored Procedure，使用 Dynamic SQL: EXEC
改寫 Stored Procedure，使用 Dynamic SQL: sp_executesql
因應需求，分別建立 Stored Procedure

Parameter Sniffing with OR Operator issue,
當 Parameter Sniffing 遇上 OR 運算子(5)

使用 UNION ALL 或 IF ELSE 改寫 Stored Procedure，確實可以讓 Query Optimizer 優化使用到更佳的索引。

但改寫、分解條件式邏輯，這是項大工程，需要與 SQL Developer 進一步討論、驗證與測試。

其實還有其他解決方案，試試 Dynamic SQL: EXEC。

8. 改寫 Stored Procedure，使用 Dynamic SQL: EXEC

說明：

Dynamic SQL: EXEC

採取 Ad hoc Query 機制來重複使用 Execution Plan

採取比對 hash 值

相同，就能重複使用 Execution Plan。
不同，產生新的 Execution Plan。

為各個輸入 parameter 值，建立效能佳的 Execution Plan。

但若每次輸入 parameter 值完全不會重複，則每次執行都會編譯，耗用系統資源。

減少因 Parameter Sniffing 機制，重複使用了效能不佳 Execution Plan，導致效能更糟的問題。

注意事項：

修改 Stored Procedure 程式碼，牽扯版本問題。

需要與 SQL Developer 人員溝通討論。

01. 建立使用 Dynamic SQL: EXEC 的 Stored Procedure。

必須修改原來的程式碼。
宣告字串變數 @sql 存放 SQL statement。
傳入 parameter

可能需要轉型為 Character strings 類型。
以及對 NULL 值的處理。

基本上

採用組合 SQL statement 方式。這是與原始程式雷同。
沒有像 UNION ALL 或 IF ELSE 般分拆改寫。

由於採取組 SQL statement 方式，程式碼可讀性略差，是會增加維護成本。

-- figure 91_ALTER Proc Dynamic SQL EXEC

02. 執行使用 Dynamic SQL: EXEC 的 Stored Procedure。

分別輸入 NULL 與 43659。

當輸入 NULL:

取回全部資料，回傳 118,744 rows，使用 Clustered Index Scan。

當輸入 43659:

取回指定 SalesOrderID 的資料，回傳 12 rows。
經 Query Optimizer 執行優化後，使用 Clustered Index Seek。

沒有使用到效率差的 Clustered Index Scan。

這是有提升執行效能的 Execution Plan。

進一步來檢視 Execution Plan。

-- figure 92_EXEC Proc Dynamic SQL EXEC

03. 觀察 Execution Plan:

當輸入參數是 NULL

使用 Clustered Index Scan。
Actual Rows: 118,744。
Est Rows: 116,323，這是 Estimated Number of Rows。
Reads: 2,249，這是 Logical Reads。

-- figure 93_Plan_Explorer

04. 觀察 Execution Plan:

當輸入參數是 43659

使用 Clustered Index Seek。
Actual Rows: 12。
Est Rows: 12，這是 Estimated Number of Rows。
Reads: 12，這是 Logical Reads。
對比使用 UNION ALL，Reads 是 372，UNION ALL 使用更多的 Logical Reads。

-- figure 114_GetSalesList_DynamicSQL_EXEC 43659

-- figure 94_Plan_Explorer

05. 使用不同的輸入 parameter，分別執行。

刻意設計，有 2 次使用相同的輸入 parameter。

-- figure 95_Execute many time with different parameter

06. 由 Cache ，觀察 Performance data

確認 Reuse Execution Plan。

ExecutionCount: 2，就是先前刻意設計有 2 次使用相同的輸入 parameter。

ObjType: Adhoc

Dynamic SQL: EXEC

採取比對 hash 值

相同，重複使用 Execution Plan。
不同，產生新的 Execution Plan。

-- figure 96_Adhoc_Execution_Plan

Dynamic SQL: EXEC 的特色：

為各個輸入 parameter 值，建立合適的 Execution Plan。
採取 Ad hoc Query 機制來重複使用 Execution Plan

比對 hash 值，若不同時，就產生新的 Execution Plan。

若每次輸入 parameter 值完全不會重複，則每次執行都會編譯，耗用系統資源。

減少因 Parameter Sniffing 機制，重複使用效能不佳 Execution Plan，導致效能更糟的問題。

9. 改寫 Stored Procedure，使用 Dynamic SQL: sp_executesql

一談起 Dynamic SQL，許多人都以為要使用 sp_executesql 才是對的、效能好的，但其實這是囫圇吞棗後的謬誤。

若能正確理解 sp_executesql，方能避免因故重複使用效率差的 Execution Plan，導致執行效能更糟的窘境。

說明

Dynamic SQL: sp_executesql

使用 Parameter Sniffing 機制來 Reuse Execution Plan。
必須面對 Parameter Sniffing 所帶來的 Side Effects。

注意事項

修改 Stored Procedure 程式碼，牽扯版本問題。

需要與 SQL Developer 人員溝通討論。

01. 建立使用 Dynamic SQL: sp_executesql 的 Stored Procedure。

必須修改原來的程式碼。
宣告字串變數 @sql 存放 SQL statement，資料類型需要是 Unicode。
傳入 parameter

可能需要轉型為 Character strings 類型。
以及對 NULL 值的處理。

基本上

採用組合 SQL statement 方式。這是與原始程式雷同。
沒有像 UNION ALL 或 IF ELSE 般分拆改寫。

由於採取組 SQL statement 方式，程式碼可讀性略差，是會增加維護成本。

-- figure 101_ALTER Proc Dynamic SQL sp_executesql

02. 執行使用 Dynamic SQL: sp_executesql 的 Stored Procedure。

分別輸入 NULL 與 43659。

當輸入 NULL:

取回全部資料，回傳 118,744 rows，使用 Clustered Index Scan。

當輸入 43659:

取回指定 SalesOrderID 的資料，回傳 12 rows，但卻仍是使用 Clustered Index Scan。
Dynamic SQL: sp_executesql 使用 Parameter Sniffing 機制來 Re-Use Execution Plan。
雖然節省了編譯 Execution Plan 的 CPU 資源，但卻因故誤用效能不佳的 Bad Execution Plan，導致耗用更多的系統資源！

Dynamic SQL: sp_executesql
必須面對誤用效能不佳的 Bad Execution Plan，所帶來的 Side Effects。

-- figure 102_EXEC Proc Dynamic SQL sp_executesql

03. 觀察 Execution Plan

輸入 NULL

在 Proc 內使用 Dynamic SQL: sp_executesql

Parameter Sniffing: Reuse Execution Plan
僅第 1 次有編譯產生 Execution Plan，之後都是共用同一份 Execution Plan。
執行 10 次，即便是輸入不同的參數值，都是共用同一份 Execution Plan。
節省編譯 Execution Plan 的資源。

-- figure 103_Plan_Explorer

04. 觀察 Execution Plan

輸入 43659

-- figure 104_Plan_Explorer

05. 執行 GetSalesList_DynamicSQL_sp_executesql

執行 6 次。
但第 2 與 3 次的輸入 parameter 都是相同。

-- figure 105_Parameter Sniffing reuse execution plan

06. 由 Cache，觀察 Performance Data

ExecutionCount: 6。
ObjType: Prepared。
重複使用同一份 Execution Plan。

Dynamic SQL: sp_executesql

採取 Parameter Sniffing 機制來 Reuse Execution Plan。
必須面對因故重複使用效率差的 Execution Plan，所帶來的 Side Effects。

-- figure 106_Prepared_Execution_Plan

10. 因應需求，分別建立 Stored Procedure

Use that big brain of yours to think your way out! Look for a new angle.

還有什麼方法，可以有效解決因故重複使用效率差的 Execution Plan 呢？
記得這句話：方法總比困難多。

回顧一下

條件式: WHERE (@SalesOrderID IS NULL OR sd.SalesOrderID = @SalesOrderID)

@SalesOrderID IS NULL:

當 @SalesOrderID 是 NULL 時，取回全部的資料，回傳 118,744 rows。

sd.SalesOrderID = @SalesOrderID:

取回指定的 SalesOrderID 的資料，回傳 12 rows。

以 Index 使用策略來看：

這兩個條件式，以執行效能角度來看，應該是使用不同的 Index 存取方式，例如：Index Scan、Index Seek 等。

與 Dev Team 討論，理解 Business 商務面的需求：

輸入參數是 NULL

用於找回全部資料，並且額外顯示資料表: Sales.SalesOrderDetail 的相關資訊。
此為管理階層常用的報表。

輸入參數是特定值

是客戶於登入系統時要使用，不需要資料表: Sales.SalesOrderDetail 的資料。

兩個不同的需求，使用 OR 運算子整合在同一個 Stored Procedure 內。
經確認與討論後，決定改寫為兩隻符合各自需求的 Stored Procedure。

01. 建立 2 隻符合各自需求的 Stored Procedure

GetSalesList_Only

輸入參數是 NULL
WHERE (@SalesOrderID IS NULL)
額外顯示資料表: Sales.SalesOrderDetail 的相關資訊

GetSalesOrderID

輸入參數是特定值
WHERE sd.SalesOrderID = @SalesOrderID
不需要資料表: Sales.SalesOrderDetail 的資料: sd.*

-- figure 81_Create 2 Procs for different purposes

02. 執行這兩 2 隻符合各自需求的 Stored Procedure

分別輸入 GetSalesList_Only NULL 與 GetSalesOrderID 43659。

GetSalesList_Only NULL:

取回全部資料，回傳 118,744 rows，使用 Clustered Index Scan。

GetSalesOrderID 43659

取回指定 SalesOrderID 的資料，回傳 12 rows。
經 Query Optimizer 執行優化後，使用效率佳 Clustered Index Seek。
這是有提升執行效能的 Execution Plan。

-- figure 82_EXEC_2 Procs

03. 觀察 Execution Plan

當執行 GetSalesList_Only NULL:

使用 Clustered Index Scan。
Actual Rows: 118,744。
Est Rows: 116,323，這是 Estimated Number of Rows。
Reads: 2,249，這是 Logical Reads。

相比先前的解決方案，與使用輸入參數 NULL 的 Execution Plan 都雷同。

-- figure 83_Plan_Explorer

-- figure 84_Plan_Explorer

-- figure 85_Compute Scalar 1

-- figure 86_Compute Scalar 2

-- figure 87_LineTotal_Data

04. 觀察 Execution Plan

當執行 GetSalesOrderID 43659:

使用 Clustered Index Seek。
Actual Rows: 12。
Est Rows: 12，這是 Estimated Number of Rows。精準符合 Actual Rows。
Reads: 12，這是 Logical Reads。
省去無需使用的資料行

只需 3 個資料行：c.PersonID, sh.Freight, cc.CardType
省去使用 Compute Scalar 運算子。

相較先前的解決方案，這是執行效能最佳的 Execution Plan !

-- figure 88_Plan_Explorer

Sample Code

20180821_Parameter_Sniffing_with_OR_Operator
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

Reference

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (1)
http://sharedderrick.blogspot.com/2018/08/case-study-parameter-sniffing-with-or.html

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (2)
http://sharedderrick.blogspot.com/2018/08/case-study-parameter-sniffing-with-or_23.html

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (3)
http://sharedderrick.blogspot.com/2018/08/case-study-parameter-sniffing-with-or_24.html

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (4)
http://sharedderrick.blogspot.com/2018/08/case-study-parameter-sniffing-with-or_31.html

[Parameter Sniffing] Reuse bad execution plan and Recompile stored procedure, 重複使用效能差的執行計劃與重新編譯預存程序
http://sharedderrick.blogspot.com/2018/08/parameter-sniffing-reuse-bad-execution.html

Parameter Sniffing: Dynamic SQL, sp_executesql and EXEC (Parameter Sniffing 與動態 SQL)
http://sharedderrick.blogspot.com/2018/08/parameter-sniffing-dynamic-sql.html

Compare "WITH RECOMPILE / sp_recompile" with "OPTION RECOMPILE"
http://sharedderrick.blogspot.com/2018/02/sql-server-compare-with-recompile-with.html

Parameter Sniffing: Local Variable
http://sharedderrick.blogspot.com/2018/01/sql-server-parameter-sniffing-local.html

Parameter Sniffing: OPTION OPTIMIZE FOR UNKNOWN
http://sharedderrick.blogspot.com/2018/01/sql-server-parameter-sniffing-option_7.html

Parameter Sniffing: OPTION OPTIMIZE FOR UNKNOWN 2

http://sharedderrick.blogspot.com/2018/01/sql-server-parameter-sniffing-option_14.html

Ad hoc Query and Reuse - 重複使用
http://sharedderrick.blogspot.com/2017/11/sql-server-ad-hoc-query-and-reuse.html

Dynamic SQL, sp_executesql and EXEC (Parameter Sniffing 與動態 SQL)
http://sharedderrick.blogspot.com/2018/08/parameter-sniffing-dynamic-sql.html

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (4)

noreply@blogger.com (DerrickLaLa) — Fri, 31 Aug 2018 22:48:00 +0800

延續前一篇文章：[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (3)

使用 UNION ALL 或 IF ELSE 改寫 Stored Procedure

仍使用 Parameter Sniffing 機制，Reuse Execution Plan。
讓 Query Optimizer 優化使用到更佳的索引，例如： Clustered Index Seek。

使用 IF ELSE，經判斷後，僅執行滿足條件式的 SQL Statement。
但 UNION ALL 卻是執行全部的 SQL Statement，再組合起來回傳。

繼續討論 Parameter Sniffing 的因應之道：

使用 EXEC ... WITH RECOMPILE
使用 sp_recompile
修改 Stored Procedure，使用 Local Variables
修改 Stored Procedure，使用 WITH RECOMPILE
修改 Stored Procedure，使用 OPTION RECOMPILE
改寫 Stored Procedure，使用 UNION ALL
改寫 Stored Procedure，使用 IF ELSE
改寫 Stored Procedure，使用 Dynamic SQL: EXEC
修改 Stored Procedure，使用 Dynamic SQL: sp_executesql
因應需求，分別建立 Stored Procedure

Parameter Sniffing with OR Operator issue,
當 Parameter Sniffing 遇上 OR 運算子(4)

回顧

條件式: WHERE (@SalesOrderID IS NULL OR sd.SalesOrderID = @SalesOrderID)

以 Index 使用策略來看，已知

@SalesOrderID IS NULL，使用 Index Scan。
sd.SalesOrderID = @SalesOrderID，使用 Index Seek。

今藉由 OR 運算子，整合在同一個 Stored Procedure。

若硬是要找出一個 Index 可以符合這兩者的效能需求，這該怎麼辦呢?

燃起程式魂

跳脫框架，改寫條件式邏輯!

6. 改寫 Stored Procedure，使用 UNION ALL

此處使用改寫，是因為要對條件式進行改寫，而非先前僅是修改編譯參數。

說明：

使用 Parameter Sniffing 機制，Reuse Execution Plan。
UNION ALL

每次執行 Stored Procedure，都必須要分別執行全部 SQL Statement 的 WHERE 條件式之判斷。
再利用 UNION ALL 將結果組合起來回傳。這勢必多耗用系統資源。
Execution Plan 相對複雜，Subtree Cost 已失真。

對比 Local Variables，改寫為 UNION ALL：

讓 Query Optimizer 優化使用到更佳的索引，例如： Clustered Index Seek。
沒有使用到效率差的 Clustered Index Scan。

對比 WITH RECOMPILE 或 OPTION RECOMPILE，改寫為 UNION ALL：

使用 Parameter Sniffing 機制，Reuse Execution Plan，沒有額外重新編譯。
相形之下，會比 WITH RECOMPILE 或 OPTION RECOMPILE 更適合於每秒都要大量執行或同時間會大量執行的程式等情境。
但若有大量 UNION ALL 卻會耗用過多資源，需要進一步的測試與評估。

注意事項：

修改 Stored Procedure 程式碼，牽扯版本問題。

需要與 SQL Developer 人員溝通討論。

01. 建立使用 UNION ALL 的 Stored Procedure。

必須修改原來的程式碼。
將條件式 OR 運算子，改寫為多段 SELECT statement，再額外使用 UNION ALL 結合起來。
換句話說

輸入任何參數值，都必須要分別執行全部 SQL Statement 的 WHERE 條件式之判斷。
再利用 UNION ALL 將結果組合起來回傳。
增加執行成本。

若有多段 SELECT statement，會增加維護的複雜度。

-- figure 61_ALTER Proc UNION ALL

02. 執行使用 UNION ALL 的 Stored Procedure。

分別輸入 NULL 與 43659。

當輸入參數是 NULL:

取回全部資料，回傳 118,744 rows，使用 Clustered Index Scan。

當輸入參數是 43659:

取回指定 SalesOrderID 的資料，回傳 12 rows。
經 Query Optimizer 執行優化後，使用 Clustered Index Seek。

沒有使用到效率差的 Clustered Index Scan。

這是有提升執行效能的 Execution Plan。

似乎美好，進一步來檢視 Execution Plan。

-- figure 62_EXEC_Proc UNION ALL

03. 觀察 Execution Plan:

複雜的 Execution Plan

使用 Concatenation 運算子，結合多段 SQL Statement 的 Execution Plan。

將輸入參數為 NULL 與輸入參數 43659 的兩份 Execution Plan，結合成一大份 Execution Plan。

換句話說，輸入任何參數值，都必須要分別執行全部 SQL Statement 的 WHERE 條件式之判斷。
再利用 UNION ALL 將結果組合起來回傳。

採用 Parameter Sniffing 機制

由於是各自獨立的 SQL statement，再使用 UNION ALL 結合再一起，因此，Query Optimizer 可以為各個 SQL statement 選擇更加合適的 Index。

但仍然採取 Parameter Sniffing 機制，重複使用 Execution Plan。
潛藏著誤用 Execution Plan，導致效能更糟的問題。

-- figure 63_Complex Execution Plan

04. 觀察 Execution Plan:

整體的 Estimated Subtree Cost是：5.03934。
已失真的 Subtree Cost

結合多份 SQL statement，所產生的 Execution Plan。

-- figure 64_Complex Execution Plan

05. 綜合觀察 1

當輸入參數是 NULL

使用 Clustered Index Scan。
Actual Rows: 118,744。
Est Rows: 116,323，這是 Estimated Number of Rows。
Reads: 2,249，這是 Logical Reads。

-- figure 65_Plan_Explorer

05. 綜合觀察 2

當輸入參數是 43659

使用 Clustered Index Seek。
Actual Rows: 12。
Est Rows: 116,323，這是 Estimated Number of Rows。

評估失真。

Reads: 372，這是 Logical Reads。

-- figure 66_Plan_Explorer

7. 改寫 Stored Procedure，使用 IF ELSE

此處使用改寫，是因為要對條件式進行改寫，而非先前僅是修改編譯參數。

說明：

使用 Parameter Sniffing 機制，Reuse Execution Plan。
使用 IF ELSE

經判斷後，僅執行滿足條件式的 Transact-SQL 陳述式。

對比 Local Variables，改寫為 IF ELSE ：

讓 Query Optimizer 優化使用到更佳的索引，例如： Clustered Index Seek。
沒有使用到效率差的 Clustered Index Scan。

對比 WITH RECOMPILE 或 OPTION RECOMPILE，改寫為 IF ELSE：

使用 Parameter Sniffing 機制，Reuse Execution Plan，沒有額外重新編譯。
相形之下，會比 WITH RECOMPILE 或 OPTION RECOMPILE 更適合於每秒都要大量執行或同時間會大量執行的程式等情境。
但若 IF ELSE 是複雜邏輯運算，則應評估與測試。

對比 UNION ALL，改寫為 IF ELSE：

使用 IF ELSE 流程控制語言，經判斷後，僅執行滿足條件式的 Transact-SQL 陳述式；
但 UNION ALL 卻是執行全部的 Transact-SQL 陳述式，再組合起來回傳。
若有多段 SELECT statement 需要使用到 IF ELSE 結合，則會增加維護的複雜度。

說明：

修改 Stored Procedure 程式碼，牽扯版本問題。

需要與 SQL Developer 人員溝通討論。

01. 建立使用 IF ELSE 的 Stored Procedure。

必須修改原來的程式碼。

使用 IF ELSE 流程控制語言，改寫為：

如果 IF 關鍵字的條件獲得滿足，就會執行在 IF 關鍵字及其條件之後的 Transact-SQL 陳述式：布林運算式會傳回 TRUE。
選擇性的 ELSE 關鍵字導入了另一個在 IF 條件未獲滿足時所執行的 Transact-SQL 陳述式：布林運算式會傳回 FALSE。

與 UNION ALL 不同在於

使用 IF ELSE 流程控制語言，經判斷後，僅執行滿足條件式的 Transact-SQL 陳述式；
但 UNION ALL 卻是執行全部的 Transact-SQL 陳述式，再組合起來回傳。
若有多段 SELECT statement 需要使用到 UNION ALL 結合，則會增加維護的複雜度。

-- figure 71_ALTER Proc IF ELSE

02. 執行使用 IF ELSE 的 Stored Procedure。

分別輸入 NULL 與 43659。

當輸入參數是 NULL:

取回全部資料，回傳 118,744 rows，使用 Clustered Index Scan。

當輸入參數是 43659:

取回指定 SalesOrderID 的資料，回傳 12 rows。
經 Query Optimizer 執行優化後，使用 Clustered Index Seek。
沒有使用到效率差的 Clustered Index Scan。
這是有提升執行效能的 Execution Plan。

似乎美好，進一步來檢視 Execution Plan。

-- figure 72_EXEC_Proc IF ELSE

03. 觀察 Execution Plan:

以 EXEC GetSalesList_IF 43659 為例

經判斷後，僅執行該 SQL Query 的 Execution Plan

與 UNION ALL 比較起來，使用 IF ELSE 的 Execution Plan 是簡潔的。

使用 IF ELSE 流程控制語言，經判斷後，僅執行滿足條件式的 Transact-SQL 陳述式。

採用 Parameter Sniffing 機制

使用 IF ELSE 流程控制語言僅執行滿足條件式的 Transact-SQL 陳述式，因此，Query Optimizer 可以為各個 SQL statement 選擇更加合適的 Index。

但仍然採取 Parameter Sniffing 機制，重複使用 Execution Plan。
潛藏著誤用 Execution Plan，導致效能更糟的問題。

當輸入參數是 43659

使用 Clustered Index Seek。
Actual Rows: 12。
Est Rows: 1，這是 Estimated Number of Rows。
Reads: 10，這是 Logical Reads。

對比使用 UNION ALL，Reads 是 372，UNION ALL 使用更多的 Logical Reads。

-- figure 73_Plan_Explorer

使用到 Compute Scalar 運算子，這是因為有使用 Computed Column(計算資料行): LineTotal。

[[AdventureWorks2014].[Sales].[SalesOrderDetail].LineTotal] = Scalar Operator(isnull(CONVERT_IMPLICIT(numeric(19,4),[AdventureWorks2014].[Sales].[SalesOrderDetail].[UnitPrice] as [sd].[UnitPrice],0)*((1.0)-CONVERT_IMPLICIT(numeric(19,4),[AdventureWorks2014].[Sales].[SalesOrderDetail].[UnitPriceDiscount] as [sd].[UnitPriceDiscount],0))*CONVERT_IMPLICIT(numeric(5,0),[AdventureWorks2014].[Sales].[SalesOrderDetail].[OrderQty] as [sd].[OrderQty],0),(0.000000)))

-- figure 111_Computed Column_LineTotal

-- figure 112_Computed Column_LineTotal

-- figure 113_Computed Column_LineTotal

04. 觀察 Execution Plan:

Actual Rows: 12
Estimated Rows: 1

些微的落差。

-- figure 74_Plan_Explorer

Sample Code

20180821_Parameter_Sniffing_with_OR_Operator
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

[Parameter Sniffing] Reuse bad execution plan and Recompile stored procedure, 重複使用效能差的執行計劃與重新編譯預存程序

noreply@blogger.com (DerrickLaLa) — Sun, 26 Aug 2018 16:55:00 +0800

曾經，遇見過 Parameter Sniffing 副作用的處理方式：

只能採用 "Local Variables / OPTIMIZE FOR UNKNOWN"。

強調重新編譯 Stored Procedure 對於資源耗用與影響。

這是不合理的作法，缺少了對於資料特性、存取資料量、執行頻率、Index、Statistics等角度考量，也忽視改寫 T-SQL 優化效能的可行性。

一昧使用 "Local Variables / OPTIMIZE FOR UNKNOWN" 機制，或許，節省了開發時程或管控方便，但卻落得 Stored Procedure 執行效能不佳的問題!

本案例，將以 "重複使用效能差的 Execution Plan" 與 "重新編譯 Stored Procedure" 的整體執行效能差異來對照。

[Parameter Sniffing] Reuse bad execution plan and Recompile stored procedure.
重複使用效能差的執行計劃與重新編譯預存程序

01. 使用範例 Parameter Sniffing 與 OR 運算子，請參考以下文章：

Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (1)

條件式: WHERE (@SalesOrderID IS NULL OR sd.SalesOrderID = @SalesOrderID)

@SalesOrderID IS NULL: 當 @SalesOrderID 是 NULL 時，取回全部的資料，回傳 118,744 rows。
sd.SalesOrderID = @SalesOrderID: 取回指定的 SalesOrderID 的資料，回傳 12 rows。

以 Index 使用策略來看：

這兩個條件式，應該是使用不同的 Index 存取方式，例如：Index Scan、Index Seek 等。

-- figure 00_Parameter Sniffing with OR issue

02. 執行此 Stored Procedure: dbo.GetSalesList。

使用 sp_recompile，重新編譯。
輸入參數 NULL。

因 Parameter Sniffing 發揮功效，重複使用 Execution Plan。
誤用到效能差的 Execution Plan: Clustered Index Scan。
輸入參數 43659。

再度使用 sp_recompile，重新編譯。
使用效能佳的 Execution Plan: Clustered Index Seek。
輸入 NULL 與 43659。

-- figure 11_Parameter Sniffing_sp_recompile_Exec_Proc

03. 使用 DMV 查詢記憶體 Cache 區，檢視 Performance Data.

-- figure 22_Compare_ComileTime_CPU

	SubTreeCost	CompileTime(ms)	LastElapsedTime(sec)	LastCPUTime(sec)	LastLogicalReads	LastRows
1st	5.0630900	17	1.345748000	0.541942000	2,249	118,744
2nd	5.0630900	17	0.181764000	0.066875000	1564	12
3rd	0.5626740	6	0.123931000	0.010333000	398	12

-- 1st: Parameter Sniffing with OR issue
-- Clustered Index Scan, return 118,744 rows

StatementSubTreeCost 5.0630900
CompileTime(ms) 17
CompileCPU(ms) 17
CompileMemory(KB) 1216
LastElapsedTime(sec) 1.345748000
LastCPUTime(sec) 0.541942000
LastLogicalReads 2,249
LastRows 118,744

-- 2nd: Bad: Clustered Index Scan, return 12 rows
-- Reuse bad execution plan

StatementSubTreeCost 5.0630900
CompileTime(ms) 17
CompileCPU(ms) 17
CompileMemory(KB) 1216
LastElapsedTime(sec) 0.181764000
LastCPUTime(sec) 0.066875000
LastLogicalReads 1564
LastRows 12

-- 3rd: "sp_recompile"
-- Good: Index Scan + Clustered Index Seek, return 12 rows

StatementSubTreeCost 0.5626740
CompileTime(ms) 6
CompileCPU(ms) 6
CompileMemory(KB) 1000
LastElapsedTime(sec) 0.123931000
LastCPUTime(sec) 0.010333000
LastLogicalReads 398
LastRows 12

比較

編譯 Compile 時間的最大值： 0.017 sec。
每次執行時間的差距(0.181764 - 0.12393) ： 0.057834 sec。
每次 Logical Read 的差距(1564 - 398)：1166。
加入編譯 Compile 時間的耗損(0.057834 - 0.017)：0.040834 sec。

若執行 100 次，Reuse bad execution plan 與 Recompile stored procedure 來比較：

執行時間差距 ( (0.181764)*100 - (0.12393 + 0.017)*100 ) ，多耗用： 4.0834 sec。
Logical Read 差距(1166 *100) ，多耗用：116,600。

由上，隨著執行次數越頻繁，因故重複使用效能差的 Execution Plan，卻是耗費更多的資源。

可以得知：

非每秒都要大量執行或同時間會大量執行的程式，改用重新編譯 Stored Procedure，獲取更佳的執行效能。這筆支出，是值得的。

-- Get execution plan for cached query: [CompileTime(ms)], [StatementSubTreeCost], [LastElapsedTime(sec)], LastLogicalReads, LastRows etc.
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as ns)

SELECT TOP 10 st.text [TSQL], DB_NAME(st.dbid) [DB], 
 TRY_CAST(qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/@StatementSubTreeCost)[1]', 'float') 
 AS decimal(38,7)) [StatementSubTreeCost],
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileTime)[1]', 'int') [CompileTime(ms)], 
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileCPU)[1]', 'int') [CompileCPU(ms)],
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileMemory)[1]', 'int') [CompileMemory(KB)],
 qs.last_elapsed_time/1000000.0 [LastElapsedTime(sec)],
 qs.last_worker_time/1000000.0 [LastCPUTime(sec)],
 qs.last_logical_reads LastLogicalReads,
 qs.last_physical_reads LastPhysicalRead,
 qs.last_logical_writes LastlogicalWrites,
 qs.last_rows LastRows,
 cp.usecounts [ExecutionCount], cp.size_in_bytes/1024.0 [PlanSize(KB)],
 cp.cacheobjtype [CacheObject], cp.objtype [ObjType], qs.plan_generation_num [PlanRecompile],
 qp.query_plan.query('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:ParameterList/ns:ColumnReference[@Column])') [ParameterList],
 qp.query_plan [QueryPlan], cp.plan_handle, qs.last_execution_time, qs.creation_time
FROM sys.dm_exec_cached_plans cp INNER JOIN sys.dm_exec_query_stats qs
         ON cp.plan_handle = qs.plan_handle
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.dbid = DB_ID('AdventureWorks2014') 
-- AND cp.objtype = 'Proc' CHARINDEX ('Products', st.text) >0
-- st.text LIKE '%xxx%'
ORDER BY StatementSubTreeCost DESC;
GO

-- figure 23_Get execution plan for cached query

Sample Code

20180825_Get_parse_compile_execute_time_SQL
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

http://sharedderrick.blogspot.com/2017/12/sql-server-get-subtreecost-compiletime.html

Get parse, compile, and execute time for SQL statement. 取得剖析、編譯和執行 SQL 查詢所耗用的時間

noreply@blogger.com (DerrickLaLa) — Sun, 26 Aug 2018 16:26:00 +0800

若取得剖析、編譯和執行 SQL 查詢所耗用的時間，可以使用以下方式：

使用 SET STATISTICS IO, TIME ON 取得。
檢視 Execution Plan 取得。
使用 DMV 直接上記憶體 Cache 區取得。

Get parse, compile, and execute time for SQL statement.
取得剖析、編譯和執行 SQL 查詢所耗用的時間

01. 依序執行以下 SQL 陳述式：

SET STATISTICS IO, TIME ON: 是用於取得剖析、編譯和執行的時間與 Disk IO 資訊。
sp_recompile: 是用於要求 SQL Server 重新編譯 Stored Procedure。

以下是各個的功用說明。

SET STATISTICS IO, TIME ON:

TIME:

顯示剖析、編譯和執行每個陳述式所需要的毫秒數。
Displays the number of milliseconds required to parse, compile, and execute each statement.

顯示 Transact-SQL 陳述式所產生之磁碟活動量的相關資訊。
Causes SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements.

sp_recompile:

在下次執行預存程序、觸發程序以及使用者定義函數時，重新編譯它們。
其運作方式是從程序快取中卸除現有的計畫，以便強制在下次執行程序或觸發程序時建立新的計畫。

-- figure 01_SET STATISTICS IO TIME ON

02. 執行 Stored Procedure。

EXEC dbo.GetSalesList 43659

-- figure 02_Execute_Stored_Procedure

03. 檢視輸出，可以看到 parse and compile time:

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 15 ms, elapsed time = 43 ms.

-- figure 03_parse and compile time

04. 也可以由 Execution Plan 取得相關的資訊:

CompileCPU: 12
CompileMemory: 1008
CompileTime: 35

-- figure 04_Actual_Execution_Plan_Complie_Time_CPU

05. 想要知道已經執行過 SQL Statement 的編譯相關資源嗎？

使用 DMV 直接上記憶體 Cache 區，自行取用 :)

CompileTime(ms): 35
CompileCPU(ms): 12
CompileMemory(KB): 1008

-- figure 05_Get execution plan for cached query

-- Get execution plan for cached query: Execution Count, Plan Recompile, ParameterList, Query Plan, CompileTime, CompileMemory, SubTreeCost, etc.
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as ns)

SELECT TOP 10 st.text [TSQL], DB_NAME(st.dbid) [DB], 
 TRY_CAST(qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/@StatementSubTreeCost)[1]', 'float') 
 AS decimal(38,7)) [StatementSubTreeCost],
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileTime)[1]', 'int') [CompileTime(ms)], 
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileCPU)[1]', 'int') [CompileCPU(ms)],
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileMemory)[1]', 'int') [CompileMemory(KB)],
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/@RetrievedFromCache)[1]', 'nvarchar(50)') [RetrievedFromCache],
 cp.usecounts [ExecutionCount], cp.size_in_bytes/1024.0 [PlanSize(KB)],
 cp.cacheobjtype [CacheObject], cp.objtype [ObjType], qs.plan_generation_num [PlanRecompile],
 qp.query_plan.query('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:ParameterList/ns:ColumnReference[@Column])') [ParameterList],
 qp.query_plan [QueryPlan], cp.plan_handle, qs.last_execution_time, qs.creation_time
FROM sys.dm_exec_cached_plans cp INNER JOIN sys.dm_exec_query_stats qs
         ON cp.plan_handle = qs.plan_handle
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.dbid = DB_ID('AdventureWorks2014') 
-- AND cp.objtype = 'Proc' CHARINDEX ('Products', st.text) >0
-- st.text LIKE '%xxx%'
ORDER BY StatementSubTreeCost DESC;
GO

Sample Code

20180825_Get_parse_compile_execute_time_SQL
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

Reference

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (1)
http://sharedderrick.blogspot.com/2018/08/case-study-parameter-sniffing-with-or.html

[SQL Server] Get SubTreeCost, CompileTime, CompileMemory, Execution Count, Plan Recompile from Plan Cache - 子樹成本
http://sharedderrick.blogspot.com/2017/12/sql-server-get-subtreecost-compiletime.html

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (3)

noreply@blogger.com (DerrickLaLa) — Fri, 24 Aug 2018 06:57:00 +0800

延續前一篇文章：[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (2)

繼續討論 Parameter Sniffing 的因應之道：

使用 EXEC ... WITH RECOMPILE
使用 sp_recompile
修改 Stored Procedure，使用 Local Variables
修改 Stored Procedure，使用 WITH RECOMPILE
修改 Stored Procedure，使用 OPTION RECOMPILE
改寫 Stored Procedure，使用 UNION ALL
改寫 Stored Procedure，使用 IF ELSE
改寫 Stored Procedure，使用 Dynamic SQL: EXEC
改寫 Stored Procedure，使用 Dynamic SQL: sp_executesql
因應需求，分別建立 Stored Procedure

Parameter Sniffing with OR Operator issue,
當 Parameter Sniffing 遇上 OR 運算子(3)

4. 修改 Stored Procedure，使用 WITH RECOMPILE

說明

WITH RECOMPILE

停用 Reuse Execution Plan 功能

每次執行 Stored Procedure，都要 Recompile SQL 陳述式，並執行 Parameter Sniffing，這勢必多耗用系統資源。

注意事項

修改 Stored Procedure 程式碼，牽扯版本問題。

需要與 SQL Developer 人員溝通討論。

停用 Reuse Execution Plan 功能
每次執行 Stored Procedure，都要 Recompile SQL 陳述式，這勢必多耗用系統資源。
以 Execute Frequency(執行頻率) 來考量

不適用於 every second(每秒) 都要執行或同時間會大量執行等情境。

每次都重新編譯，將過度耗用系統資源。

適用於: 年報、季報、月報、周報、日報、小時報或數分鐘報等

如能因重新編譯 Stored Procedure，獲取更佳的執行效能。這筆支出，值得。

01. 建立使用 WITH RECOMPILE 的 Stored Procedure。

必須修改原來的程式碼。
在 Stored Procedure 內，額外再宣告 WITH RECOMPILE 來使用。

-- figure 41_ALTER Proc WITH RECOMPILE

02. 執行使用 WITH RECOMPILE 的 Stored Procedure。

分別輸入 NULL 與 43659。

當輸入 NULL:

取回全部資料，回傳 118,744 rows，使用 Clustered Index Scan。

當輸入 43659:

取回指定 SalesOrderID 的資料，回傳 12 rows。
經 Query Optimizer 執行優化後，使用 Index Scan + Clustered Index Seek。

沒有使用到效率差的 Clustered Index Scan。

這是有提升執行效能的 Execution Plan。

若非每秒都要執行或同時間會大量執行的程式，能因 WITH RECOMPILE 重新編譯 Stored Procedure，獲取更佳的執行效能。這筆支出，是值得的。

-- figure 42_EXEC_Proc WITH RECOMPILE

03. 觀察 Execution Plan:

EXEC GetSalesList_WITH_RECOMPILE NULL

使用 Clustered Index Scan。
Actual Rows: 118,744。
Est Rows: 116,322，這是 Estimated Number of Rows。
Reads: 2,315，這是 Logical Reads。

EXEC GetSalesList_WITH_RECOMPILE 43659

重新編譯，獲取執行效能更佳的 Execution Plan。
使用 Index Scan + Clustered Index Seek。
Actual Rows: 12。
Est Rows: 12，這是 Estimated Number of Rows。
Reads: 464，這是 Logical Reads。

-- figure 43_Plan_Explorer

04. 進一步觀察 Execution Plan

Operation caused residual IO.
The actual number of rows read was 121,317, but the number of rows returned was 12.

雖然已經不用 Clustered Index Scan，Query Optimizer 判斷改用 Index Scan，但實際僅取回 12 rows，卻仍須讀取 121,317 rows，仍是耗用過多的系統資源。

讓我們繼續設計出更有效率的執行方式與 Execution Plan!

-- figure 44_Plan_Explorer

5. 修改 Stored Procedure，使用 OPTION RECOMPILE

說明

使用 OPTION (RECOMPILE)

針對特定的 SQL Statement，停用其 Reuse Execution Plan 功能。
僅 Recompile 指定的 SQL Statement，並且可使用到 Local Variable 的內容值。
每次執行 Stored Procedure，僅需 Recompile 指定的 SQL 陳述式，但仍有耗用過多資源的顧慮。

注意事項

修改 Stored Procedure 程式碼，牽扯版本問題。

需要與 SQL Developer 人員溝通討論。

每次執行 Stored Procedure，僅需 Recompile 指定的 SQL 陳述式，仍有耗用過多資源的顧慮。
以 Execute Frequency(執行頻率) 來考量

不適用於 every second(每秒) 都要執行或同時間會大量執行等情境。

每次都重新編譯，將過度耗用系統資源。

適用於: 年報、季報、月報、周報、日報、小時報或數分鐘報等

如能因重新編譯 Stored Procedure，獲取更佳的執行效能。這筆支出，值得。

而且，OPTION (RECOMPILE) 可以解除 "Local Variables / OPTIMIZE FOR UNKNOWN" 所帶來的副作用，但 sp_recompile 與 WITH RECOMPILE 是無此功效。

01. 建立使用 OPTION RECOMPILE 的 Stored Procedure。

必須修改原來的程式碼。
在指定的 SQL Statement，額外再宣告 OPTION RECOMPILE 來使用。

-- figure 51_ALTER Proc OPTION RECOMPILE

02. 執行使用 OPTION RECOMPILE 的 Stored Procedure。

分別輸入 NULL 與 43659。

當輸入 NULL:

取回全部資料，回傳 118,744 rows，使用 Clustered Index Scan。

當輸入 43659:

取回指定 SalesOrderID 的資料，回傳 12 rows。
經 Query Optimizer 執行優化後，使用 Index seek + Clustered Index Seek。

沒有使用到效率差的 Clustered Index Scan、Index Scan。

這是有提升執行效能的 Execution Plan。
與 WITH RECOMPILE 比較起來，OPTION RECOMPILE 有機會產更有效率的 Execution Plan。

若非每秒都要大量執行或同時間會大量執行的程式，能因 OPTION RECOMPILE 重新編譯 Stored Procedure，獲取更佳的執行效能。這筆支出，是值得的。

-- figure 52_EXEC_Proc OPTION RECOMPILE

03. 觀察 Execution Plan:

EXEC GetSalesList_OPTION_RECOMPILE NULL

使用 Clustered Index Scan。
Actual Rows: 118,744。
Est Rows: 116,322，這是 Estimated Number of Rows。
Reads: 2,313，這是 Logical Reads。

EXEC GetSalesList_OPTION_RECOMPILE 43659

僅 Recompile 指定的 SQL Statement，就可以獲得執行效能更佳的 Execution Plan。
使用 Index Seek + Clustered Index Seek。
Actual Rows: 12。
Est Rows: 12，這是 Estimated Number of Rows。
Reads: 76，這是 Logical Reads。

-- figure 53_Plan_Explorer

Sample Code

20180821_Parameter_Sniffing_with_OR_Operator
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (2)

noreply@blogger.com (DerrickLaLa) — Thu, 23 Aug 2018 07:27:00 +0800

延續前一篇文章：[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (1)

繼續討論 Parameter Sniffing 的因應之道：

使用 EXEC ... WITH RECOMPILE
使用 sp_recompile
修改 Stored Procedure，使用 Local Variables
修改 Stored Procedure，使用 WITH RECOMPILE
修改 Stored Procedure，使用 OPTION RECOMPILE
改寫 Stored Procedure，使用 UNION ALL
改寫 Stored Procedure，使用 IF ELSE
改寫 Stored Procedure，使用 Dynamic SQL: EXEC
改寫 Stored Procedure，使用 Dynamic SQL: sp_executesql
因應需求，分別建立 Stored Procedure

Parameter Sniffing with OR Operator issue,
當 Parameter Sniffing 遇上 OR 運算子(2)

1. 使用 EXEC ... WITH RECOMPILE

說明：

無須修改 Stored Procedure 程式碼
執行 stored procedure，搭配 WITH RECOMPILE

不方便。還需要帶入 User Parameter。

執行當下 Recompile (重新編譯) 此 stored procedure。

注意事項：

單次性的處理。

處理此次因 Parameter Sniffing 機制，導致重複使用到效能不佳的 Execution Plan。

但仍繼續使用 Parameter Sniffing 機制。

若 Input Parameter 仍差異性過大，仍會遭遇重複使用到效能不佳的 Execution Plan 。

01. 執行當下 Recompile (重新編譯) 此 stored procedure。

-- figure 11_EXEC_WITH RECOMPILE

02. 觀察 Execution Plan

-- Good: Index Scan + Clustered Index Seek, return 12 rows
EXEC dbo.GetSalesList 43659 WITH RECOMPILE

改用 Index Scan + Clustered Index Seek，Good Execution Plan。

-- figure 12_Good_Index Scan_Clustered Index Seek

03. 使用 Plan Explorer 來觀察

EXEC dbo.GetSalesList 43659 WITH RECOMPILE

改用 Index Scan + Clustered Index Seek，Good Execution Plan。
Actual Rows: 12。
Est Rows: 12，這是 Estimated Number of Rows。
Reads: 464，這是 Logical Reads。

與 "dbo.GetSalesList NULL" 對比起來，節省了大量的系統資源，Est Cost 是整體的 10%。

-- figure 13_Plan_Explorer

2. 使用 sp_recompile

說明：

無須修改 Stored Procedure 程式碼。
等到下次執行 stored procedure，才將 Recompile(重新編譯) 此 stored procedure。
使用到此 Table 的各項 SQL 物件：stored procedure, trigger, or user-defined function，都將 Recompile 。
比較起來，不先移除該 stored procedure 的 Execution Plan。

注意事項：

單次性的處理。

處理此次因 Parameter Sniffing 機制，導致重複使用到效能不佳的 Execution Plan。

但仍繼續使用 Parameter Sniffing 機制。

若 Input Parameter 仍差異性過大，仍會遭遇重複使用到效能不佳的 Execution Plan 。

01. 使用 sp_recompile 對 Table 後，再執行 Stored Procedure。

-- figure 21_sp_recompile

02. 使用 Plan Explorer 來觀察

-- Good: Index Scan + Clustered Index Seek, return 12 rows
EXEC sp_recompile 'dbo.GetSalesList'
GO
EXEC dbo.GetSalesList 43659
GO

改用 Index Scan + Clustered Index Seek，Good Execution Plan。
Actual Rows: 12。
Est Rows: 12，這是 Estimated Number of Rows。
Reads: 464，這是 Logical Reads。

與 "dbo.GetSalesList NULL" 對比起來，節省了大量的系統資源，Est Cost 是整體的 10%。

-- figure 22_Plan_Explorer

3. 修改 Stored Procedure，使用 "Local Variables / OPTIMIZE FOR UNKNOWN"

說明：

修改 Stored Procedure 程式碼，牽扯版本問題。

需要與 SQL Developer 人員溝通討論。

設定 Query Optimizer 僅使用 Average Distribution (平均分配) 機制來執行 cardinality estimation。

至於輸入什麼 parameter 值，回傳多少筆數等等，設定讓 Query Optimizer 都不要列入考量。
停用 Parameter Sniffing。

評估方式，例如：

使用 equality (等號)，則 Query Optimizer 僅使用 density vector 來執行 cardinality estimation，也就是： Rows * All density。
使用 Inequality (不相等)，則 Query Optimizer 連 density vector 也不用了，就直接設定資料列總數的 30% 來執行 cardinality estimation，也就是： Rows * 30% 。

注意事項：

停用 Parameter Sniffing 機制。

僅重複使用 "Local Variables / OPTIMIZE FOR UNKNOWN" 機制產生的 Execution Plan。
堪用，非效能最佳化的 Execution Plan。

看似節省編譯 Execution Plan 的系統資源，卻陷入執行效能不佳的窘境，甚至耗損更多的系統資源!
必須修改 SQL 程式碼。
不適用於需求是高效能的系統。

01. 建立使用 Local Variable 的 Stored Procedure。

必須修改原來的程式碼。
在 Stored Procedure 內，額外再宣告 Local Variable 來使用。

-- figure 31_ALTER Proc Local Variables_issue

02. 執行使用 Local Variable 的 Stored Procedure。

分別輸入 NULL 與 43659。

當輸入 NULL:

取回全部資料，回傳 118,744 rows，使用 Clustered Index Scan。

當輸入 43659:

取回指定 SalesOrderID 的資料，回傳 12 rows，但卻使用 Clustered Index Scan。

應該使用 Index Seek。

啟用 "Local Variables / OPTIMIZE FOR UNKNOWN" 判斷機制，停用 Parameter Sniffing 機制。
看似節省了編譯 Execution Plan 的 CPU 資源，卻陷入執行效能不佳的窘境，導致耗用更多的系統資源！

-- figure 32_EXEC_Local Variables_issue

03. 觀察: 這 2 份 Execution Plan。

因啟用 "Local Variables / OPTIMIZE FOR UNKNOWN" 判斷機制

停用 Parameter Sniffing 機制。
兩份完全相同的 Execution Plan。

都是使用 Clustered Index Scan！

-- Clustered Index Scan, return 118,744 rows
EXEC GetSalesList_Local_Variable NULL

使用 Clustered Index Scan。
Actual Rows: 118,744。
Est Rows: 38,387，這是 Estimated Number of Rows，不精確。

因啟用 "Local Variables / OPTIMIZE FOR UNKNOWN" 機制所計算出來的回傳筆數，與事實不符。

Reads: 2,249，這是 Logical Reads。
Est Cost 卻都顯示 50%，這是 Estimated Subtree Cost，不精確，不具備參考價值。

-- Bad: Clustered Index Scan, return 12 rows
EXEC GetSalesList_Local_Variable 43659

卻使用 Clustered Index Scan。
Actual Rows: 12。
Est Rows: 38,387，這是 Estimated Number of Rows，與 Actual Rows 差異極大，不精確。

因啟用 "Local Variables / OPTIMIZE FOR UNKNOWN" 機制所計算出來的回傳筆數，與事實不符。

Reads: 1,562，這是 Logical Reads。
Est Cost 卻都顯示 50%，這是 Estimated Subtree Cost，不精確，不具備參考價值。

-- figure 33_Plan_Explorer

進一步觀察 Execution Plan

Operation caused residual IO.
The actual number of rows read was 121,317, but the number of rows returned was 12.

實際僅取回 12 rows，卻因啟用 "Local Variables / OPTIMIZE FOR UNKNOWN" 機制，額外讀取 121,317 rows，浪費了大量的 IO 系統資源!

看似節省編譯 Execution Plan 的系統資源，卻陷入執行效能不佳的窘境，甚至耗損更多的系統資源!

-- figure 34_Plan_Explorer

身為 SQL Developer 人員

需要去了解資料特性、回傳筆數、執行頻率、Index、Statistics 等，選擇適合的因應之道，方能開發出高效能的 Stored Procedure。
一昧使用 "Local Variables / OPTIMIZE FOR UNKNOWN" 機制，或許，節省了開發時程，但卻落得 Stored Procedure 執行效能不佳的窘境!

Sample Code

20180821_Parameter_Sniffing_with_OR_Operator
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

Parameter Sniffing: OPTION OPTIMIZE FOR UNKNOWN
http://sharedderrick.blogspot.com/2018/01/sql-server-parameter-sniffing-option_7.html

Parameter Sniffing: OPTION OPTIMIZE FOR UNKNOWN 2
http://sharedderrick.blogspot.com/2018/01/sql-server-parameter-sniffing-option_14.html

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (1)

noreply@blogger.com (DerrickLaLa) — Wed, 22 Aug 2018 06:59:00 +0800

在先前的文章裡，陸續介紹過 Parameter Sniffing 的基本概念。本文以 Case Study (案例分享) 方式來綜合討論。

Parameter Sniffing

優點：藉由重複使用 Execution Plan 的機制，來節省編譯 Execution Plan 資源的耗損。
缺點：卻存在著誤用 Execution Plan，導致效能更糟的問題。
看似節省編譯 Execution Plan 的 CPU 資源，卻掉入效能不佳的窘境。

OR 運算子

可篩選多值，延展條件式的功能，十分彈性，但卻潛藏效能不佳的問題。

當兩者結合，遭遇到效能不佳的困境時，該如何解決呢?

本文彙整 10 種 Parameter Sniffing 的因應之道：

使用 EXEC ... WITH RECOMPILE
使用 sp_recompile
修改 Stored Procedure，使用 "Local Variables / OPTIMIZE FOR UNKNOWN"
修改 Stored Procedure，使用 WITH RECOMPILE
修改 Stored Procedure，使用 OPTION RECOMPILE
改寫 Stored Procedure，使用 UNION ALL
改寫 Stored Procedure，使用 IF ELSE
改寫 Stored Procedure，使用 Dynamic SQL: EXEC
改寫 Stored Procedure，使用 Dynamic SQL: sp_executesql
因應需求，分別建立 individual Stored Procedure

想知道之間的區別、應用時機與優缺點嗎？

本系列文章

Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (1)

Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (2)

Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (3)

Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (4)

Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (5)

Parameter Sniffing with OR Operator issue,
當 Parameter Sniffing 遇上 OR 運算子(1)

01. 建立 Stored Procedure: dbo.GetSalesList

條件式: WHERE (@SalesOrderID IS NULL OR sd.SalesOrderID = @SalesOrderID)

@SalesOrderID IS NULL: 當 @SalesOrderID 是 NULL 時，取回全部的資料，回傳 118,744 rows。
sd.SalesOrderID = @SalesOrderID: 取回指定的 SalesOrderID 的資料，回傳 12 rows。

以 Index 使用策略來看：

這兩個條件式，應該是使用不同的 Index 存取方式，例如：Index Scan、Index Seek 等。
但透過 OR 運算子，整合在同一個 Stored Procedure，會遇到什麼效能上的難題呢？

-- figure 01_Parameter Sniffing with OR issue

02. 執行此 Stored Procedure: dbo.GetSalesList。

分別輸入 NULL 與 43659。

當輸入 NULL:

取回全部資料，回傳 118,744 rows，使用 Clustered Index Scan。

當輸入 43659:

取回指定 SalesOrderID 的資料，回傳 12 rows，但卻仍是使用 Clustered Index Scan。
由於 Parameter Sniffing 發揮功效，重複使用 Execution Plan。
雖然節省了編譯 Execution Plan 的 CPU 資源，但卻因故誤用效能不佳的 Bad Execution Plan，導致耗用更多的系統資源！

-- figure 02_EXEC_Parameter Sniffing with OR issue

03. 觀察: 這 2 隻 Stored Procedure 的 Execution Plan。

由於 Parameter Sniffing 發揮功效，重複使用 Execution Plan。
都是使用 Clustered Index Scan！

-- figure 03_Clustered Index Scan

04. 綜合觀察

EXEC dbo.GetSalesList NULL

使用 Clustered Index Scan。
Actual Rows: 118,744。
Est Rows: 116,322，這是 Estimated Number of Rows。
Reads: 2,335，這是 Logical Reads。

EXEC dbo.GetSalesList 43659

仍是使用 Clustered Index Scan。
Actual Rows: 12。
Est Rows: 116,322，這是 Estimated Number of Rows。
Reads: 1,564，這是 Logical Reads。

這就是錯誤的重複使用 Execution Plan 後，所導致的效能低落問題。
看似節省編譯 Execution Plan 的 CPU 資源，卻掉入效能不佳的窘境。

下圖的 Execution Plan 是屬於 EXEC dbo.GetSalesList NULL。

-- figure 04_Plan_Explorer

05. 下圖的 Execution Plan 是屬於 EXEC dbo.GetSalesList 43659。

-- figure 05_Plan_Explorer

Sample Code

20180821_Parameter_Sniffing_with_OR_Operator
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

Reference

Parameter Sniffing: Dynamic SQL, sp_executesql and EXEC (Parameter Sniffing 與動態 SQL)
http://sharedderrick.blogspot.com/2018/08/parameter-sniffing-dynamic-sql.html

Compare "WITH RECOMPILE / sp_recompile" with "OPTION RECOMPILE"
http://sharedderrick.blogspot.com/2018/02/sql-server-compare-with-recompile-with.html

[SQL Server] Disabled Index still update Statistics, 停用索引仍會更新統計值

noreply@blogger.com (DerrickLaLa) — Sat, 18 Aug 2018 14:52:00 +0800

在維護大型系統上，需要定期評估 Index 適用性。
若判斷無須使用該 Index，則執行 Disabled Index 與進一步觀察對系統的影響程度。

提醒

若經確認無需使用該 Index 後，應移除該 Index。

Disabled Index 會防止使用者存取 Index，Query Optimizer 不使用此 Index。但 SQL Server 仍會維護與更新該 Index 的統計值。

Disabled Index 仍會更新 Statistics

Disabled Index still update Statistics, 停用索引仍會更新統計值

01. 使用 sp_helpindex 觀察資料表 [Person].[Address] 的 Index 資訊

-- figure 01_Index

02. 使用 DMV 與 STATS_DATE 觀察全部資料表的 Index 與 Statistics 資訊

Get all indexes and Statistics last updated datetime

-- figure 02_Get all indexes and Statistics last updated datetime

03. 停用 Index

Disable Nonclustered Index
IX_Address_StateProvinceID

-- figure 11_Disable Nonclustered Index

04. 觀察資料表的 Index 與 Statistics 資訊

確認 IX_Address_StateProvinceID 已經被 Disabled

-- figure 12_Get all indexes and Statistics last updated datetime

05. 更新 Statistics

Update the Statistics of table

-- figure 21_Update the Statistics of table

06. 觀察 Statistics 資訊

確認 Statistics 已經被更新
即便該 Index 早已被 Disabled

-- figure 22_Get all indexes and Statistics last updated datetime

07. 使用 DBCC SHOW_STATISTICS 觀察 Statistics

確認已經被更新

-- figure 31_DBCC SHOW_STATISTICS

08. 使用 DMV: dm_db_stats_properties 觀察 Statistics

確認已經被更新

-- figure 32_dm_db_stats_properties

Disabled Index 會防止使用者存取 Index，Query Optimizer 不使用此 Index。
但 SQL Server 仍會維護與更新該 Index 的統計值。

Disabled Index

Disabled Index 會防止使用者存取 Index ，而 Disabled Clustered Index 則會防止存取基礎資料表資料。
索引定義會保留在中繼資料內，而 index statistics 資料會保留在 Nonclustered Indexes上。
停用 View 上的非叢集或叢集索引，實際上會刪除 Index Data。
Disabled Clustered Index，則會防止存取資料；這些資料仍留在資料表中，但無法用於資料操作語言 (DML) 作業，除非卸除或重建索引。

Sample Code

20180818_Disabled_Index_Update_Statistics
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

Reference

Disable Indexes and Constraints
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/disable-indexes-and-constraints?view=sql-server-2017

STATS_DATE (Transact-SQL)
https://docs.microsoft.com/zh-tw/sql/t-sql/functions/stats-date-transact-sql?view=sql-server-2017

sys.dm_db_stats_properties (Transact-SQL)

https://docs.microsoft.com/zh-tw/sql/relational-databases/system-dynamic-management-views/sys-dm-db-stats-properties-transact-sql?view=sql-server-2017

Parameter Sniffing: Dynamic SQL, sp_executesql and EXEC (Parameter Sniffing 與動態 SQL)

noreply@blogger.com (DerrickLaLa) — Tue, 14 Aug 2018 20:12:00 +0800

Dynamic SQL: sp_executesql 與 EXEC 有其彈性與應用的特殊場合。
但 Dynamic SQL 需面對 Parameter Sniffing 所帶來的優點與 Side Effects。

	Re-Use Execution Plan	ObjType
Dynamic SQL: EXEC	Ad hoc Query - 比對 hash 值	Adhoc
Dynamic SQL: sp_executesql	Parameter Sniffing	Prepared
Stored Procedure	Parameter Sniffing	Proc

Dynamic SQL: EXEC

採取比對 hash 值

相同，就能重複使用 Execution Plan。
不同，產生新的 Execution Plan。

Dynamic SQL: sp_executesql

使用 Parameter Sniffing 機制來 Re-Use Execution Plan。
必須面對 Parameter Sniffing 所帶來的 Side Effects。

思考

sp_executesql，受制於 Parameter Sniffing，導致重複使用效能不佳的 Execution Plan。

後續文章，將繼續討論例外情形。

sp_executesql

若 Transact-SQL 陳述式本身維持不變，只有 parameters 值改變。

因此，Query Optimizer 可能會重複使用它針對第 1 次執行所產生的Execution Plan。

也就是說，當陳述式 parameters 值的變更是唯一的變數時，您可以利用 sp_executesql 取代 Stored Procedure 來重複執行 Transact-SQL 陳述式。
若要提升效能，請在陳述式字串中使用完整物件名稱。

在 sp_executesql 中替換 parameters 的能力，會為利用 EXECUTE 陳述式來執行字串帶來下列好處：

由於在各次執行之間，sp_executesql 字串中 Transact-SQL 陳述式的實際文字不會改變。
因此，Query Optimizer 可能會符合第 2 次執行的 Transact-SQL 陳述式與針對第 1 次執行所產生的執行計畫。因此，不需要編譯第 2 個陳述式。
Transact-SQL 字串只建立一次。

Parameter Sniffing: Dynamic SQL, sp_executesql and EXEC
Parameter Sniffing 與動態 SQL

01. 使用 Dynamic SQL: EXEC

執行 10 次。
傳入參數都不同。

-- figure 11_Dynamic SQL_EXEC

02. 觀察 Performance Statistics：

使用 Dynamic SQL: EXEC

如同 Ad hoc Query 一般，Query Optimizer 為每一個 SQL 陳述式建立 hash value，以此 hash value 比對另一 SQL 陳述式的 hash value 是否相同。

若 hash 值相同，就能重複使用該 SQL 陳述式所建立 Execution Plan。
若 hash 值不同，就必須為該 SQL 陳述式編譯新的 Execution Plan。

也就是說，要能重複使用 Execution Plan，則執行的 Ad hoc Query 必須完全相同。

舉例來說，這包含了：空白、註解、大小寫、結尾符號、SET 選項等。

觀察

RetrievedFromCache 是 true
Execution Count ，若 hash 值相同，重複使用 Execution Plan，累加。
CacheObject 是 Compiled Plan。
ObjType 是 Adhoc。

Ad hoc Query and Reuse - 重複使用
http://sharedderrick.blogspot.com/2017/11/sql-server-ad-hoc-query-and-reuse.html

-- figure 12_Get last performance statistics

03. 再度執行 01. 使用 Dynamic SQL: EXEC 後，觀察 Performance Statistics

Execution Count ，若 hash 值相同，重複使用 Execution Plan。

本次範例，已累加到 2。

ObjType 是 Adhoc。

-- figure 13_2_Get last performance statistics

04. 使用 Dynamic SQL: sp_executesql

執行 10 次。
傳入參數都不同。

-- figure 21_Dynamic SQL_sp_executesql

05. 觀察 Performance Statistics：

使用 Dynamic SQL: sp_executesql

Parameter Sniffing: Re-Use Execution Plan。
僅第 1 次有編譯產生 Execution Plan，之後都是共用同一份 Execution Plan。

觀察

TSQL 是「Parameterization(參數化)」

(@StateProvinceID INT)SELECT City FROM Person.Address WHERE StateProvinceID = @StateProvinceID

RetrievedFromCache 是 true
由於 Parameter Sniffing 發揮功能，重複使用同一份 Execution Plan。

本次範例 Execution Count，已經累加到 10。

CacheObject 是 Compiled Plan。
ObjType 是 Prepared。

-- figure 22_Get last performance statistics

06. 再度執行使用 Dynamic SQL: sp_executesql 後，觀察 Performance Statistics

由於 Parameter Sniffing 發揮功能，重複使用同一份 Execution Plan。

本次範例 Execution Count，已經累加到 20。

ObjType 是 Prepared。

-- figure 23_2nd_Get last performance statistics

Proc and Dynamic SQL: sp_executesql, EXEC

01. 建立 Stored Procedure: Proc and Dynamic SQL: EXEC

在 Proc 內使用 Dynamic SQL: EXEC

-- figure 31_Proc and Dynamic SQL_EXEC

02. 執行 Proc_DynamicSQL_EXEC

執行 10 次。
傳入參數都不同。

-- figure 32_Exec_Proc_DynamicSQL_EXEC

03. 觀察 Performance Statistics：

在 Proc 內使用 Dynamic SQL: EXEC

如同 Ad hoc Query 一般，Query Optimizer 為每一個 SQL 陳述式建立 hash value，以此 hash value 比對另一 SQL 陳述式的 hash value 是否相同。

若 hash 值相同，就能重複使用該 SQL 陳述式所建立 Execution Plan。
若 hash 值不同，就必須為該 SQL 陳述式編譯新的 Execution Plan。

也就是說，要能重複使用 Execution Plan，則執行的 Ad hoc Query 必須完全相同。

舉例來說，這包含了：空白、註解、大小寫、結尾符號、SET 選項等。

觀察

RetrievedFromCache 是 true
Execution Count ，若 hash 值相同，重複使用 Execution Plan，累加。
CacheObject 是 Compiled Plan。
ObjType 是 Adhoc。

-- figure 33_Get last performance statistics

04. 再度執行 02. 執行 Proc_DynamicSQL_EXEC 後，觀察 Performance Statistics

Execution Count ，若 hash 值相同，重複使用 Execution Plan。

本次範例，已累加到 2。

ObjType 是 Adhoc。

-- figure 34_2nd_Get last performance statistics

05. 建立 Stored Procedure: Proc_DynamicSQL_sp_executesql

在 Proc 內使用 Dynamic SQL: sp_executesql

-- figure 41_Proc and Dynamic SQL_sp_executesql

06. 執行 Proc_DynamicSQL_sp_executesql

執行 10 次。
傳入參數都不同。

-- figure 42_Exec_Proc_DynamicSQL_sp_executesql

07. 觀察 Performance Statistics：

在 Proc 內使用 Dynamic SQL: sp_executesql

Parameter Sniffing: Re-Use Execution Plan
僅第 1 次有編譯產生 Execution Plan，之後都是共用同一份 Execution Plan。
執行 10 次，即便是輸入不同的參數值，都是共用同一份 Execution Plan。
節省編譯 Execution Plan 的資源。

觀察

TSQL 是「Parameterization(參數化)」

(@StateProvinceID INT)SELECT City FROM Person.Address WHERE StateProvinceID = @StateProvinceID

RetrievedFromCache 是 true
由於 Parameter Sniffing 發揮功能，重複使用同一份 Execution Plan。

本次範例 Execution Count，已經累加到 10。

CacheObject 是 Compiled Plan。
ObjType 是 Prepared。

-- figure 43_Get last performance statistics

08. 再度執行 06. 執行 Proc_DynamicSQL_sp_executesql 後，觀察 Performance Statistics

由於 Parameter Sniffing 發揮功能，重複使用同一份 Execution Plan。

本次範例 Execution Count，已經累加到 20。

ObjType 是 Prepared。

-- figure 44_2nd_Get last performance statistics

對照組

-- figure 51_Proc without Dynamic SQL

-- figure 52_Exec_Proc_NonDynamicSQL

-- figure 53_Get last performance statistics

-- figure 54_2nd_Get last performance statistics

很單純的 Proc，沒有使用 Dynamic SQL

Parameter Sniffing: Re-Use Execution Plan

僅第 1 次有編譯產生 Execution Plan，之後都是共用同一份 Execution Plan。
執行 10 次，即便是輸入不同的參數值，都是共用同一份 Execution Plan。
節省編譯 Execution Plan 的資源。

觀察

RetrievedFromCache 是 true
由於 Parameter Sniffing 發揮功能，重複使用同一份 Execution Plan。

Execution Count，已經累加。

CacheObject 是 Compiled Plan。
ObjType 是 Proc。

Sample Code

20180814_Parameter_Sniffing_Dynamic_SQL
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

Reference

Parameter Sniffing: Pros and Cons, 參數探測
http://sharedderrick.blogspot.com/2017/12/sql-server-parameter-sniffing-pros-and.html

Parameter Sniffing: sp_recompile, WITH RECOMPILE
http://sharedderrick.blogspot.com/2018/01/sql-server-parameter-sniffing.html

Compare "WITH RECOMPILE / sp_recompile" with "OPTION RECOMPILE"
http://sharedderrick.blogspot.com/2018/02/sql-server-compare-with-recompile-with.html

Enable optimize for ad hoc workloads , 針對特定工作負載最佳化
http://sharedderrick.blogspot.com/2017/11/sql-server-enable-optimize-for-ad-hoc.html

Ad hoc Query and Reuse - 重複使用
http://sharedderrick.blogspot.com/2017/11/sql-server-ad-hoc-query-and-reuse.html

Dynamic SQL - 動態 SQL
https://docs.microsoft.com/zh-tw/sql/odbc/reference/dynamic-sql?view=sql-server-2017

Static SQL - 靜態 SQL
https://docs.microsoft.com/zh-tw/sql/odbc/reference/static-sql?view=sql-server-2017

sp_executesql (Transact-SQL)
https://docs.microsoft.com/zh-tw/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017

EXECUTE (Transact-SQL)
https://docs.microsoft.com/zh-tw/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-2017

TSQL: Look at parameter from execution plan, 查看執行計畫所使用的參數

noreply@blogger.com (DerrickLaLa) — Mon, 30 Jul 2018 22:10:00 +0800

延續先前文章 SSMS: Look at parameter from execution plan, 查看執行計畫所使用的參數

在對 Stored Procedure 執行 Performance Optimization (效能優化)時，若能知道編譯時期的 Parameter Compiled Value，將能夠精準有效率的執行優化任務。

可使用以下方式查詢：Parameter Compiled Value

使用 SSMS
使用 DMV: sys.dm_exec_query_plan

使用 SSMS 觀察 Execution Plan，可以看到

Column
Parameter Compiled Value
Parameter Data Type
Parameter Runtime Value

使用 DMV: sys.dm_exec_query_plan 的資料行: query_plan，可以觀察 ParameterList Element(元素)：

Column: 傳入的 Parameter 名稱。
ParameterDataType: 使用的資料類型。
ParameterCompiledValue: 編譯 Execution Plan 的值。

TSQL: Look at parameter from execution plan, 查
看執行計畫所使用的參數

01. 使用以下範例，其中：

[dbo].[uspGetWhereUsedProductID] 使用的參數是：

@StartProductID=819
@CheckDate='2010-12-23'

-- figure 01_TSQL_Sample

02. 使用以下語法查詢取得 last performance statistics for cached query:

-- Get last performance statistics for cached query: Execution Count, Plan Recompile, ParameterList, Query Plan, etc.
SELECT TOP 10 st.text [TSQL], DB_NAME(st.dbid) [DB], 
 qs.last_elapsed_time/1000000.0 [LastElapsedTime(sec)],
 qs.last_worker_time/1000000.0 [LastCPUTime(sec)],
 qs.last_logical_reads LastLogicalReads,
 qs.last_physical_reads LastPhysicalRead,
 qs.last_logical_writes LastlogicalWrites,
 qs.last_rows LastRows,
 cp.usecounts [ExecutionCount], cp.size_in_bytes/1024.0 [PlanSize(KB)],
 cp.cacheobjtype [CacheObject], cp.objtype [ObjType], qs.plan_generation_num [PlanRecompile],
 qp.query_plan.query('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
 (/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:ParameterList/ns:ColumnReference[@Column])') [ParameterList],
 qp.query_plan [QueryPlan], cp.plan_handle, qs.last_execution_time, qs.creation_time
FROM sys.dm_exec_cached_plans cp INNER JOIN sys.dm_exec_query_stats qs
         ON cp.plan_handle = qs.plan_handle
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.dbid = DB_ID('AdventureWorks2014')
-- CHARINDEX ('Products', st.text) >0
-- st.text LIKE '%xxx%'
ORDER BY [ExecutionCount] DESC;
GO

-- figure 11_Get last performance statistics for cached query

03. 檢視查詢結果，可以看到：[ParameterList]。

考量會傳入多個參數值，採用 XML 格式呈現。

-- figure 12_last performance statistics_ParameterList

04. 檢查 ParameterList 的 XML 格式，可以觀察'：

Column: 傳入的 Parameter 名稱。
ParameterDataType: 使用的資料類型。
ParameterCompiledValue: 編譯 Execution Plan 的值。

Column="@CheckDate" ParameterDataType="datetime" ParameterCompiledValue="'2010-12-23 00:00:00.000'"
Column="@StartProductID" ParameterDataType="int" ParameterCompiledValue="(819)"

-- figure 13_Review_ParameterCompiledValue

05. Get performance statistics for cached query - Full

-- Get performance statistics for cached query - Full
/*
sys.dm_exec_query_stats - Returns aggregate performance statistics
An initial query of sys.dm_exec_query_stats might produce inaccurate results if there is a workload currently executing on the server. 
More accurate results may be determined by rerunning the query.

total_elapsed_time: Total elapsed time, reported in microseconds (but only accurate to milliseconds), 
for completed executions of this plan.
*/

SELECT TOP 10 st.text [TSQL], DB_NAME(st.dbid) [DB], cp.usecounts [ExecutionCount],
 qs.total_elapsed_time/cp.usecounts/1000000.0 [AvgElapsedTime(sec)], 
 qs.last_elapsed_time/1000000.0 [LastElapsedTime(sec)], qs.max_elapsed_time/1000000.0 [MaxElapsedTime(sec)], 
 qs.min_elapsed_time/1000000.0 [MinElapsedTime(sec)], qs.total_elapsed_time/1000000.0 [TotalElapsedTime(sec)],
 qs.total_worker_time/cp.usecounts/1000000.0 [AvgCPUTime(sec)], 
 qs.last_worker_time/1000000.0 [LastCPUTime(sec)], qs.max_worker_time/1000000.0 [MaxCPUTime(sec)], 
 qs.min_worker_time/1000000.0 [MinCPUTime(sec)], qs.total_worker_time/1000000.0 [TotalCPUTime(sec)], 
 qs.total_logical_reads/cp.usecounts [AvgLogicalRead], 
 qs.last_logical_reads, qs.max_logical_reads, qs.min_logical_reads, qs.total_logical_reads, 
 qs.last_physical_reads [LastPhysicalRead], qs.max_physical_reads, qs.min_physical_reads, qs.total_physical_reads,  
 qs.total_logical_writes/cp.usecounts [AvgLogicalWrite], qs.last_logical_writes,
 qs.max_logical_writes, qs.min_logical_writes, qs.total_logical_writes,
 qs.last_rows [LastRows], qs.max_rows, qs.min_rows, qs.total_rows,
 qp.query_plan.query('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
 (/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:ParameterList/ns:ColumnReference[@Column])') [ParameterList], 
 qp.query_plan [QueryPlan], cp.plan_handle, cp.size_in_bytes/1024.0 [PlanSize(KB)],
 cp.cacheobjtype [CacheObject], cp.objtype [ObjType], qs.plan_generation_num [PlanRecompile],
 st.objectid, qs.last_execution_time, qs.creation_time --qs.*
FROM sys.dm_exec_cached_plans cp INNER JOIN sys.dm_exec_query_stats qs
         ON cp.plan_handle = qs.plan_handle
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.dbid = DB_ID('AdventureWorks2014')
-- CHARINDEX ('Products', st.text) >0
-- st.text LIKE '%xxx%'
ORDER BY [ExecutionCount] DESC;
GO

/*
CPU bound - ORDER BY qs.total_worker_time , [AvgElapsedTime(sec)]
I/O bound - ORDER BY qs.total_logical_reads , [AvgLogicalRead]
*/

-- figure 21_Get performance statistics for cached query

06. 檢視回傳的結果：

-- figure 22_Get performance statistics for cached query

07. Get execution plan for cached query: Execution Count, Plan Recompile, ParameterList, Query Plan, etc.

-- Get execution plan for cached query: Execution Count, Plan Recompile, ParameterList, Query Plan, etc.
SELECT TOP 10 st.text [TSQL], DB_NAME(st.dbid) [DB], 
 cp.usecounts [ExecutionCount], cp.size_in_bytes/1024.0 [PlanSize(KB)],
 cp.cacheobjtype [CacheObject], cp.objtype [ObjType], qs.plan_generation_num [PlanRecompile],
 qp.query_plan.query('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
 (/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:ParameterList/ns:ColumnReference[@Column])') [ParameterList], 
 qp.query_plan [QueryPlan], cp.plan_handle, qs.last_execution_time, qs.creation_time
FROM sys.dm_exec_cached_plans cp INNER JOIN sys.dm_exec_query_stats qs
         ON cp.plan_handle = qs.plan_handle
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.dbid = DB_ID('AdventureWorks2014')
-- CHARINDEX ('Products', st.text) >0
-- st.text LIKE '%xxx%'
ORDER BY [ExecutionCount] DESC;
GO

-- figure 31_Get execution plan for cached query

08. 檢視回傳的結果：

-- figure 32_Get execution plan for cached query_ParameterList

09. -- Get execution plan for cached query: Execution Count, Plan Recompile, ParameterList, Query Plan, CompileTime, CompileMemory, SubTreeCost, etc.

-- Get execution plan for cached query: Execution Count, Plan Recompile, ParameterList, Query Plan, CompileTime, CompileMemory, SubTreeCost, etc.
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as ns)

SELECT TOP 10 st.text [TSQL], DB_NAME(st.dbid) [DB], 
 TRY_CAST(qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/@StatementSubTreeCost)[1]', 'float') 
 AS decimal(38,7)) [StatementSubTreeCost],
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileTime)[1]', 'int') [CompileTime(ms)], 
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileCPU)[1]', 'int') [CompileCPU(ms)],
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileMemory)[1]', 'int') [CompileMemory(KB)],
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/@RetrievedFromCache)[1]', 'nvarchar(50)') [RetrievedFromCache],
 cp.usecounts [ExecutionCount], cp.size_in_bytes/1024.0 [PlanSize(KB)],
 cp.cacheobjtype [CacheObject], cp.objtype [ObjType], qs.plan_generation_num [PlanRecompile],
 qp.query_plan.query('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:ParameterList/ns:ColumnReference[@Column])') [ParameterList],
 qp.query_plan [QueryPlan], cp.plan_handle, qs.last_execution_time, qs.creation_time
FROM sys.dm_exec_cached_plans cp INNER JOIN sys.dm_exec_query_stats qs
         ON cp.plan_handle = qs.plan_handle
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.dbid = DB_ID('AdventureWorks2014') 
-- AND cp.objtype = 'Proc' CHARINDEX ('Products', st.text) >0
-- st.text LIKE '%xxx%'
ORDER BY StatementSubTreeCost DESC;
GO

-- figure 41_Get execution plan for cached query

-- figure 42_Get execution plan for cached query_ParameterList

Sample Code

20180730_Look at parameter_TSQL
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

Reference

[SQL Server] SSMS: Look at parameter from execution plan, 查看執行計畫所使用的參數
http://sharedderrick.blogspot.com/2017/12/sql-server-look-at-parameter-from.html

[Performance Tuning] Parallel INSERT … SELECT with TABLOCK hint

noreply@blogger.com (DerrickLaLa) — Tue, 24 Jul 2018 07:50:00 +0800

SQL Server 2016 新增加 INSERT … SELECT with TABLOCK hint，支援 Parallel 平行處理來提升效能。

先前版本，雖然 TABLOCK，採取減少 Log 量與 Lock 方式來提升效能。但在 SQL Server 2016 再強化為支援 Parallel 平行處理來優化效能。

基本規則是

資料表沒有 Clustered Index，也就是 Heap(堆積)。
資料庫的 COMPATIBILITY_LEVEL (相容性) = 130 含以上。
INSERT ... SELECT 使用 TABLCOK。

Parallel INSERT … SELECT with TABLOCK hint

Table Without Clustered Index.
The database compatibility level must be 130 and above.
Must use the TABLOCK hint with the INSERT … SELECT statement.

使用 TABLOCK，效能提升：71.5% 。

(0.061514000-0.105527000)/0.061514000 = 71.5%

TSQL	LastElapsedTime(sec)
#th1：with TABLOCK	0.061514000
#th2：without TABLOCK	0.105527000
#th3_ClusteredIndex：with TABLOCK, and with Clustered Index	0.123723000

[Performance Tuning] Parallel INSERT … SELECT with TABLOCK hint

01. 建立 temporary table (暫存資料表)。

#th1 : 沒有 Clustered Index，也就是 Heap (堆積)。
#th2 : 沒有 Clustered Index，也就是 Heap (堆積)。

-- figure 01_Create Table_without_Clustered Index

02. 建立 temporary table (暫存資料表)。

#th3_ClusteredIndex : 有 Clustered Index。
TransactionID 資料行，設定為 Clustered Index，也是 Primary Key。

-- figure 02_Create Table With Clustered Index

03. 新增資料列，使用 INSERT ... SELECT

#th1 : INSERT ... SELECT ，並加上使用 TABLOCK 。

Parallel Insert，平行處理。

#th2 : 僅使用 INSERT ... SELECT 。

無法 Parallel Inert，無法平行處理。

-- figure 11_WITH (TABLOCK)

04. 觀察 Execution Plan，兩相比較下

有使用 TABLOCK hint，僅占 36% 成本資源。
觀察到有 Parallelism 平行處理的圖示。

-- figure 12_Execution_Plan

05. 使用 Plan Explorer 來做觀察：

有使用 TABLOCK，Cost 僅使用 36%，Parallel Operations 是 4 。
沒用 TABLOCK，Cost 多耗用至 63.8%，Parallel Operations 是 1 。

-- figure 13_Execution_Plan_Plan_Explorer

06. 進一步觀察 Execution Plan:

有使用 TABLOCK，Subtree Cost 是: 3.34141。
沒用 TABLOCK，Subtree Cost 高達: 5.89468。

-- figure 14

-- figure 15

07. 觀察 Table Insert operator:

有使用 TABLOCK，可以 Parallel 平行處理。

留意：Number of Executions 是：4。

沒有 TABLOCK，不支援 Parallel 平行處理。

留意：Number of Executions 是：1。

-- figure 16_Table_Insert

-- figure 17_Table_Insert

08. 觀察 Performance Data：

使用 TABLOCK，效能提升：71.5% 。
Improvement = (Original_Response_Time - Improved_Response_Time) / Original_Response_Time * 100%
(0.061514000-0.105527000)/0.061514000 = 71.5%

TSQL	LastElapsedTime(sec)
#th1：with TABLOCK	0.061514000
#th2：without TABLOCK	0.105527000
#th3_ClusteredIndex：with TABLOCK, and with Clustered Index	0.123723000

-- figure 18_Performance_Data

09. 比較以下 INSERT ... SELECT ：

#th1：with TABLOCK

可平行處理。Parallel Insert

#th2：without TABLOCK

無法平行處理。Not Parallel

#th3_ClusteredIndex：with TABLOCK, and with Clustered Index

無法平行處理。Not Parallel

-- figure 19_Different type of INSERT ... SELECT

10. 觀察 Execution Plan

-- figure 20_Compare_3

The database compatibility level must be 130 and above.

01. 調降 database compatibility level 為 120。

-- figure 21_database compatibility level

02. 確認該資料庫是降級使用 COMPATIBILITY_LEVEL = 120

-- figure 22_COMPATIBILITY_LEVEL = 120

03. 再度執行 INSERT ... SELECT

-- figure 23_INSERT_SELECT

04. 觀察 Execution Plan:

都不支援平行處理。
Non-Parallel execution in all script.

這是由於 COMPATIBILITY_LEVEL 必須要在 130 以上才支援平行處理。

-- figure 24_Execution_Plan

大量匯入資料的最佳做法

使用 INSERT INTO…SELECT 搭配最低限度記錄來大量匯入資料
您可以搭配使用 INSERT INTO SELECT FROM 最低限度記錄，

有效率地將大量資料列從某份資料表 (例如暫存表格) 傳送至另一份資料表。

最低限度記錄可以改善此陳述式的效能並且降低交易期間作業填滿可用交易記錄空間的可能性。

此陳述式的最低限度記錄具有下列需求：

資料庫的復原模式設為 simple (簡單) 或 bulk-logged(大量記錄)。
目標資料表是空白或非空白的 heap(堆積)。
目標資料表未用於複寫。
針對目標資料表指定了 TABLOCK 提示。

由於 MERGE 陳述式中的插入動作而插入堆積的資料列也可以採用最低限度記錄。

與 BULK INSERT 陳述式 (持有較不嚴格的大量更新鎖定) 不同之處在於，
具 TABLOCK 提示的 INSERT INTO…SELECT 對資料表持有獨佔 (X) 鎖定。
這代表您無法使用平行插入作業插入資料列。

Sample Code

20180724_INSERT_SELECT_TABLOCK
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

Reference

SQLSweet16!, Episode 3: Parallel INSERT … SELECT
https://blogs.msdn.microsoft.com/sqlcat/2016/07/06/sqlsweet16-episode-3-parallel-insert-select/

Real World Parallel INSERT…SELECT: What else you need to know!
https://blogs.msdn.microsoft.com/sqlcat/2016/07/21/real-world-parallel-insert-what-else-you-need-to-know/

Columnstore Index: Parallel load into clustered columnstore index from staging table
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/07/19/columnstore-index-parallel-load-into-clustered-columnstore-index-from-staging-table/

The Data Loading Performance Guide
https://docs.microsoft.com/zh-tw/previous-versions/sql/sql-server-2008/dd425070(v=sql.100)

Poor performance when you run INSERT.. SELECT operations in SQL Server 2016 or SQL Server 2017 on Windows
https://support.microsoft.com/en-us/help/3180087/poor-performance-when-you-run-insert-select-operations-in-sql-server-2

INSERT (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-2017

[SSMS] Dark Theme

noreply@blogger.com (DerrickLaLa) — Fri, 20 Jul 2018 22:10:00 +0800

目前，SSMS 17.x 提供 2 種 theme。

Blue
Light

若想要 Dark theme，則需要額外編輯 ssms.pkgundef。

雖然還未得到 Microsoft 正式支援，但確實可讓 SSMS 切換為 Dark theme 。

SSMS: Selecting the Dark Theme

版本: SSMS 17.x
示範作業系統： Windows 10

00. 請先關閉 SSMS。

01. 使用檔案總管，尋找與檢視文字格式檔案： ssms.pkgundef

SSMS 17.x 的存放路徑：

C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio

-- figure 11_ssms.pkgundef

02. 編輯此文字檔案: ssms.pkgundef

尋找到關鍵字：// Remove Dark Theme

-- figure 12_Remove Dark theme

03. 使用 // 註解，將 // Remove Dark Theme 下的第 1 行給註解掉。請參考下圖所示：

[$RootKey$\Themes\{1ded0138-47ce-435e-84ef-9ec1f439b749}]

-- figure 13_comment out first line

04. 儲存檔案 ssms.pkgundef

若不是使用 Administrator 管理者身分編輯時，將遭遇到以下錯誤訊息，請點選 Yes。

-- figure 41_Notepad_without_Administrator

05. 再點選 Yes，調整 UAC: User Account Control。

-- figure 42_Click_YES

06. 確認儲存檔案：ssms.pkgundef。

07. 開啟 SSMS

點選 Tools / Options。
在左邊窗格，點選 Environment / General。

在右邊窗格，在 Color theme，下拉，可以看到 3 種 theme：Blue, Dark, Light

-- figure 21_Select_Dark_theme

08. 確認選擇：Dark theme。

-- figure 22_Dark

若未正確設定，只能看到 2 種 theme：Blue, Light。

-- figure 29_Original

09. 已經切換為 Dark theme

-- figure 31_SSMS

10. 可惜，仍有部分視窗沒有黑化。

Results
Messages
Execution Plan

-- figure 31_SSMS_Results

11. 視窗 Object Explorer 也未黑化。

-- figure 32_SSMS_Object_Explorer

12. 視窗 Registered Servers 也尚未黑化。

-- figure 33_SSMS_Registered_Servers

13. 切換為 Light theme。

-- figure 51_Light_theme

14. 切換為預設 Blue theme。

-- figure 52_Blue_theme

15. 切換到 Dark theme。

-- figure 53_Dark_theme

16. 若要全部都黑化，可以改用 Visual Studio Code 或 SQL Ops Studio 等。

-- figure 61_Dark_default_dark

Reference

SQL Ops Studio: Selecting the Color Theme - Dark theme
http://sharedderrick.blogspot.com/2018/04/sql-ops-selecting-color-theme-dark-theme.html

[SQL Server] CREATE OR ALTER - New enhancements in SQL Server 2016 SP1

noreply@blogger.com (DerrickLaLa) — Sat, 14 Jul 2018 14:01:00 +0800

SQL Server 2016 (13.x) SP1 與 Azure SQL Database 開始支援 CREATE OR ALTER。

僅需 1 句 T-SQL，即可自動判斷是該 CREATE 或 ALTER 此 Stored Procedure。
無需額外撰寫判斷式去檢查是否存在於 sys.objects 中。

CREATE OR ALTER 支援以下物件：

STORED PROCEDURES
FUNCTIONS
TRIGGERS
VIEWS

不受限於 COMPATIBILITY_LEVEL。

CREATE OR ALTER - New enhancements in SQL Server 2016 SP1

01. 僅需 1 句 T-SQL，即可自動判斷是該 CREATE 或 ALTER 此 Stored Procedure。

CREATE OR ALTER PROC
無需額外撰寫判斷式去檢查是否存在於 sys.objects 中。
SQL Server 2016 (13.x) SP1 支援。

-- figure 01_CREATE OR ALTER PROC

02. 精簡的判斷式，但仍需額外再寫 CREATE PROC。

DROP PROCEDURE IF EXISTS 。
無需增加判斷式去檢查是否存在於 sys.objects 中。
SQL Server 2016 (13.x) 支援。

-- figure 02_DROP PROCEDURE IF EXISTS

03. 額外撰寫判斷式去檢查是否存在於 sys.objects 中。SQL Server 2016 之前的語法。

-- figure 03_EXISTS_sys_objects

04. 綜合來看：

SQL Server 2016 (13.x) SP1 支援的 CREATE OR ALTER ，最為簡潔。

-- figure 04_Compare_All

CREATE OR ALTER 支援的物件

CREATE OR ALTER 支援以下物件：

STORED PROCEDURES
FUNCTIONS
TRIGGERS
VIEWS

01. CREATE OR ALTER PROC

-- figure 11_CREATE OR ALTER PROC

02. CREATE OR ALTER FUNCTION

-- figure 12_CREATE OR ALTER FUNCTION

03. CREATE OR ALTER VIEW

-- figure 13_CREATE OR ALTER VIEW

04. CREATE OR ALTER TRIGGER

-- figure 14_CREATE OR ALTER TRIGGER

Sample Code

20180714_CREATE OR ALTER
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

Reference

CREATE OR ALTER – another great language enhancement in SQL Server 2016 SP1
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/17/create-or-alter-another-great-language-enhancement-in-sql-server-2016-sp1/

Update introduces CREATE OR ALTER Transact-SQL statement in SQL Server 2016
https://support.microsoft.com/en-us/help/3190548/update-introduces-create-or-alter-transact-sql-statement-in-sql-server

[SQL Server] 1,700 bytes and 32 columns for nonclustered index

noreply@blogger.com (DerrickLaLa) — Thu, 12 Jul 2018 22:11:00 +0800

SQL Server 2016 (13.x) 與 Azure SQL Database 增強 Nonclustered Index(非叢集索引)：

資料長度延展為 1,700 Bytes，由 32 個 Column 組成。

先前版本是：

資料長度為 900 Bytes，由 16 個 Column 組成。

Clustered Index(叢集索引) 仍是 900 Bytes。

若有需要超過，請改用 Included Columns (內含資料行的索引)

[SQL Server] 1,700 bytes and 32 columns for nonclustered index

01. 建立資料表: NonCL_Size

NCL VARCHAR(2048)

並建立 NonClustered Index

-- figure 11_Create NonClustered Index_Maximum_1700

Warning! The maximum key length for a nonclustered index is 1700 bytes.
The index 'ix_NCL' has maximum length of 2048 bytes.

For some combination of large values, the insert/update operation will fail.

02. 輸入資料:

第 1 筆，資料長度是 1700 bytes，沒有超過 Nonclustered Indexes 上限。
第 2 筆，資料長度是 1701 bytes，超過 Nonclustered Indexes 上限，導致新增失敗。

-- figure 12_Operation failed_over_1700

Msg 1946, Level 16, State 3, Line 32
Operation failed.

The index entry of length 1701 bytes for the index 'ix_NCL' exceeds the maximum length of 1700 bytes for nonclustered indexes.

03. 查詢資料表，確認僅第 1 筆輸入成功。

-- figure 13_Only 1 row

04. 建立資料表: CL_Size

CL VARCHAR(1024)

建立 Clustered Index

-- figure 21_Create Clustered Index_Maximum_900

Warning! The maximum key length for a clustered index is 900 bytes.
The index 'ix_CL' has maximum length of 1024 bytes.

For some combination of large values, the insert/update operation will fail.

05. 輸入資料:

第 1 筆，資料長度是 900 bytes，沒有超過 Clustered Indexes 上限。
第 2 筆，資料長度是 901 bytes，超過 Clustered Indexes 上限，導致新增失敗。

-- figure 22_Operation failed_over_900

Msg 1946, Level 16, State 3, Line 72
Operation failed.

The index entry of length 901 bytes for the index 'ix_CL' exceeds the maximum length of 900 bytes for clustered indexes.

06. 查詢資料表，確認僅第 1 筆輸入成功。

-- figure 23_Only 1 row

SQL Server 2016 與 Azure SQL Database 增強 nonclustered index(非叢集索引)，將資料長度延展為 1,700 Bytes，先前版本是 900 Bytes。

Clustered Index(叢集索引)為 900 個位元組數。 Nonclustered Index(非叢集索引) 為 1,700 Bytes。

您可以使用大小上限增加超過限制的可變長度資料行，來定義索引鍵。
不過，這些資料行中的資料大小總和不得超過限制。

在非叢集索引中，您可以包含額外的非索引鍵資料行，這些資料行不會計入索引鍵的大小限制。
非索引鍵資料行可能有助於提升某些查詢的執行效能。

Sample Code

20180712_1700byte_32column_Nonclustered
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

Reference

Maximum Capacity Specifications for SQL Server
https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-2017

Increased nonclustered index key size with SQL Server 2016
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/04/26/increased-nonclustered-index-key-size-with-sql-server-2016/

Create Indexes with Included Columns
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns?view=sql-server-2017

德瑞克：SQL Server 學習筆記

[Hyper-V] 安裝 Windows 11 Pro 虛擬機器（使用本機帳戶 Local Account 登入）

虛擬機設定重點

1. 虛擬機世代選擇：第 2 代

2. 網路設定：未連線

3. 啟用信賴平台模組（TPM）以符合 Windows 11 安裝需求

4. 跳過 Microsoft 帳號登入，使用本機帳戶的操作方式

Install SQL Server 2019 on Windows Server 2019: 安裝抓圖

Sysprep 變更 SID(Security Identifier)，以 Windows Server 2019 為例

Windows server 2019 安裝

Enabled SET XACT_ABORT ON in SSMS, 在 SSMS 啟用 XACT_ABORT

BEGIN TRAN with XACT_ABORT

Find out Statistics used to Execution Plan from Plan Cache - DBCC TRACEON(8666), 由 Plan Cache 取得 執行計畫 所使用的 統計值

Find out Statistics used to Execution Plan in SQL Server 2017, 找出 執行計畫 所使用的 統計值

Use Autostat (AUTO_UPDATE_STATISTICS), 認識 自動更新統計資料 (2)

Use Autostat (AUTO_UPDATE_STATISTICS), 認識 自動更新統計資料 (1)

Dynamic SQL: Pass dynamic TABLE name variable, 動態傳遞 資料表名稱 變數

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (5)

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (4)

[Parameter Sniffing] Reuse bad execution plan and Recompile stored procedure, 重複使用效能差的 執行計劃 與 重新編譯 預存程序

Get parse, compile, and execute time for SQL statement. 取得 剖析、編譯 和 執行 SQL 查詢 所耗用的時間

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (3)

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (2)

[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (1)

[SQL Server] Disabled Index still update Statistics, 停用索引 仍會更新 統計值

Parameter Sniffing: Dynamic SQL, sp_executesql and EXEC (Parameter Sniffing 與 動態 SQL)

TSQL: Look at parameter from execution plan, 查看 執行計畫 所使用的參數

[Performance Tuning] Parallel INSERT … SELECT with TABLOCK hint

[SSMS] Dark Theme

[SQL Server] CREATE OR ALTER - New enhancements in SQL Server 2016 SP1

[SQL Server] 1,700 bytes and 32 columns for nonclustered index

Find out Statistics used to Execution Plan from Plan Cache - DBCC TRACEON(8666), 由 Plan Cache 取得執行計畫所使用的統計值

Find out Statistics used to Execution Plan in SQL Server 2017, 找出執行計畫所使用的統計值

Use Autostat (AUTO_UPDATE_STATISTICS), 認識自動更新統計資料 (2)

Use Autostat (AUTO_UPDATE_STATISTICS), 認識自動更新統計資料 (1)

Dynamic SQL: Pass dynamic TABLE name variable, 動態傳遞資料表名稱變數

[Parameter Sniffing] Reuse bad execution plan and Recompile stored procedure, 重複使用效能差的執行計劃與重新編譯預存程序

Get parse, compile, and execute time for SQL statement. 取得剖析、編譯和執行 SQL 查詢所耗用的時間

[SQL Server] Disabled Index still update Statistics, 停用索引仍會更新統計值

Parameter Sniffing: Dynamic SQL, sp_executesql and EXEC (Parameter Sniffing 與動態 SQL)

TSQL: Look at parameter from execution plan, 查看執行計畫所使用的參數