搜尋本站文章

2018-08-18

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


在維護大型系統上,需要定期評估 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


2018-08-14

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



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