搜尋本站文章

2018-06-23

[Performance Tuning] Multi-section of Group By clause, 使用 多段式 Group By 來提升效能


使用 Group By,已是耗費資源的動作。若再搭配 函數等運算,勢必耗損更多系統資源。

Rewrite and optimize 優化改寫 為 多段式 Group By 來提升效能

藉由 多段式 Group By:


  • 第 1 段 Group By,並 未 搭配 函數 等運算,相對輕量地執行 Group By 後,縮減 資料列筆數。
  • 第 2 段 Group By,雖然搭配 函數 等耗用資源的運算,但要處理資料量卻是 遠小 於原本的資料表。

多段式的 Group By 雖然稍為增加 T-SQL 陳述式的複雜性,但卻確實有 提升效能。

項目單一 Group By多段式 Group By比較
T-SQL 陳述式簡單稍嫌複雜
Elapsed Time(sec)0.1966900000.127646000節省 35%
CPU Time(sec)0.0184900000.013201000節省 29%
Subtree Cost0.4056890.182843節省 55%



-- Rewrite and optimize



本文包含討論 2 個 系統內部的暫存資料表: 'Worktable' 與 'Workfile'

  • Workfile
    • 用來儲存 hash joins (雜湊聯結) 與 hash aggregates (雜湊彙總) 的暫存結果。
  • Worktable
    • 用來儲存 Query spool (查詢多工緩衝處理)、lob 變數、XML 變數 與 Cursor (資料指標) 的暫存結果。
    • 例如: GROUP BY、 ORDER BY或 UNION 查詢等。





[Performance Tuning] Multi-section of Group By clause



01. 檢視資料表內的資料
  • Table: Sales.SalesOrderHeader
  • Column: SalesPersonID, OrderDate, SubTotal 
  • rows: 31465


-- figure  01_Look_at_ data





02. 檢視 資料表上的 Index 資訊


  • composite index with Included Columns: IX_SalesPersonID_OrderDate_Temp
    • [SalesPersonID] , [OrderDate] 
    • 內含資料行的索引(Indexes with Included Columns): ([SubTotal])


-- figure  02_Index_Information






03. 彙總: 各個月份的小計(Sub Total)

  • GROUP BY  這 3 個:
    • SalesPersonID, YEAR(OrderDate), MONTH(OrderDate)
    • 使用到 2 個 資料行: SalesPersonID, OrderDate
    • 其中,OrderDate 都有搭配使用 日期 函數。以此 分組 來取得 年、月 的匯總資料。


-- figure  11_Group_By_Year_Month_OrderDate







04. 檢視 Execution Plan


  • Subtree Cost: 0.405689
  • rows: 461
  • 主要耗用資源的 Operator(運算子)是: Index Scan(31%) + Hash Match(63%)
請留意:
  • Operator: Hash Match(Aggregate),占用 Execution Plan 整體的 63% 資源。

若要優化效能,該如何下手?

沒有 Missing Index 的輔助建議,還能做什麼?

-- figure  12_Hash_Match_Aggregate_ExecutionPlan






05. 觀察 Performance Data



  • LastElapsedTime(sec): 0.196690000
  • LastCPUTime(sec): 0.018490000
  • LastLogicalReads: 119
  • LastPhysicalRead: 125
  • LastRows: 461



-- figure  13_Performance_Data






06. 檢視 STATISTICS IO, TIME 輸出的資訊:


  • 有 2 個 系統內部的暫存資料表: Table 'Worktable' 與 Table 'Workfile'


認識 Workfiles


  • 用來儲存 hash joins(雜湊聯結) 與 hash aggregates(雜湊彙總) 的暫存結果
  • work files could be used to store temporary results for hash joins and hash aggregates.

Hash joins(雜湊聯結) 用於處理 大型、未排序、無索引 的輸入。

在 複雜查詢 下,Hash joins 有利於 intermediate results(中繼結果) 的使用:

  • 一般而言,中繼結果 是沒有索引 (除非有明確地儲存到磁碟,然後建立索引)。
    • 通常產生時也不會做 適當的 排序 供 Execution Plan 的下一個作業使用。
  • Query optimizer 只估計 中繼結果 的大小。
    • 因為 複雜查詢 的 估計值 可能非常不準確,所以處理 中繼結果 的演算法必須要有效率,
    • 而且萬一 中繼結果  顯著大於預期時,它的效能還不得 惡化 得太明顯。



若有 Index 可用,資料已經 排序,將可避免使用 Hash joins,避免產生 Workfiles 。


認識 Worktables (中繼工作資料表)


  • 用來儲存 query spool (查詢多工緩衝處理)、lob 變數、XML 變數 與 Cursor (資料指標) 的暫存結果。
  • work tables could be used to store temporary results for query spool, lob variables, XML variables, and cursors.

  • 關聯式引擎在執行 SQL 陳述式中所指定的邏輯作業前,可能需要先建立一個 Worktables。 
  • Worktables 屬於 內部資料表,可用來保存 中繼結果。 
  • Worktables 會針對特定的 GROUP BY、 ORDER BY或 UNION 查詢而產生。 
    • 例如,如果 ORDER BY 子句會參考不在任何索引範圍內的資料行,則關聯式引擎可能需要產生 Worktables,根據所要求的順序來排序結果集。 

  • Worktables 有時候也當作 多工緩衝 處理使用,可 暫時 保存 執行部份 查詢計畫的結果。 
  • Worktables 建置於 tempdb 中,並且會在不再需要時 自動卸除


-- figure  14_STATISTICS IO_TIME







Multi-section of Group By clause 多段式 Group By


01. 改寫為 多段式的 Group By 



  • 第 1 段 Group By
    • 使用 2 個 資料行: SalesPersonID, OrderDate。
    • 但 OrderDate 沒有 函數 運算。
  • 第 2 段 Group By
    • 使用 3 個 資料行: SalesPersonID, YEAR(OrderDate), MONTH(OrderDate)
    • OrderDate 資料行,皆使用 函數 運算。依據 年、月 來分組。


-- figure  21_Multi-section of Group By clause






02. 觀察 Execution Plan,與先前已有所不同:



  • Subtree Cost 降低 為: 0.182843
  • rows: 461
  • 主要耗用資源的 Operator(運算子)是: Index Scan(68%) + Sort (21%)

請留意:
  • 不復見 Operator: Hash Match(Aggregate)

-- figure  22_ExecutionPlan





03. 觀察 Performance Data


  • LastElapsedTime(sec): 0.127646000
  • LastCPUTime(sec): 0.013201000
  • LastLogicalReads: 119
  • LastPhysicalRead: 125
  • LastRows: 461

比較先前的 GROUP BY 陳述式,LastElapsedTime(sec) 與 LastCPUTime(sec) 都 降低 了。

-- figure  25_Performance_Data





04. 檢視 STATISTICS IO, TIME 輸出的資訊:



  • 僅使用到 1 個系統內部暫存資料表: 'Worktable'


已省去使用 Workfiles
  • Workfiles 用於存放 hash joins 與 hash aggregates 的暫存結果。
  • 改寫為 多段式 Group By,節省系統資源,省去使用 Workfiles。

-- figure  26_STATISTICS IO_TIME






05. 檢視第 1 段 Group By

  • 使用到 2 個 資料行: SalesPersonID, OrderDate
  • 但 OrderDate 沒有 函數 運算。
  • 原本資料表有 31,465 rows,經過 GROUP BY 分組後,已 縮減 輸出為 1,592 rows。
    • 資料列筆數 減量 95% ,只剩下原來的 5%

-- figure  23_First_section




06. 檢視第 1 段 Group By 的 Execution Plan。


-- figure  24_First_section_Execution Plan





07. 比較兩者


-- figure  41_Compare_TSQL






-- figure  42_Plan_Explorer





-- figure  43_Single_Group_By_Top_Operations





-- figure  




-- figure  31_Compare_Execution Plan







結論


多段式的 Group By 雖然稍為增加 T-SQL 陳述式的複雜性,但卻確實有 提升效能。

項目單一 Group By多段式 Group By比較
T-SQL 陳述式簡單稍嫌複雜
Elapsed Time(sec)0.1966900000.127646000節省 35%
CPU Time(sec)0.0184900000.013201000節省 29%
Subtree Cost0.4056890.182843節省 55%


多段式 Group By 的說明如下:

第 1 段 Group By
  • 使用到 2 個 資料行: SalesPersonID, OrderDate
  • 但 OrderDate 沒有 函數 運算。
  • 原本資料表有 31,465 rows,經過 GROUP BY 分組後,已 縮減 輸出為 1,592 rows。
    • 資料列筆數 減量 95% ,只剩下原來的 5%

第 2 段 Group By

  • 對 少量 筆數的資料,執行 Group By

對資料表執行 Group By,已是耗費資源的動作。若再搭配 函數等運算,勢必耗損更多系統資源。


藉由 多段式 Group By:
  • 第 1 段 Group By,並 未 搭配 函數 等運算,相對輕量地執行 Group By 後,縮減 資料列筆數。
  • 第 2 段 Group By,雖然搭配 函數 等耗用資源的運算,但要處理資料量卻是 遠小 於原本的資料表。




Sample Code


20180623_Multi-section of Group By
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing





Reference

Understanding Hash Joins
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/ms189313(v=sql.100)

Advanced Query Tuning Concepts
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/ms191426(v=sql.100)

SQL Server, Access Methods Object
https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-access-methods-object?view=sql-server-2017

Query Processing Architecture Guide -- Worktables 
https://docs.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide?view=sql-server-2017

Memory Management Architecture Guide -- Workfile
https://docs.microsoft.com/en-us/sql/relational-databases/memory-management-architecture-guide?view=sql-server-2017

Sort Warnings Event Class
https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/sort-warnings-event-class?view=sql-server-2017

Hash Warning Event Class
https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/hash-warning-event-class?view=sql-server-2017

Understanding Hash joins
https://docs.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-2017

SQL Server 2012:DATEFROMPARTS 從各自部分取得日期和時間值的函數
http://sharedderrick.blogspot.com/2013/01/sql-server-2012datefromparts.html

2018-05-30

[SQL Ops Studio] Build a custom insight widget: 自製 insight widget


延續前一篇:Enable built-in insight widgets - table space usage insight

在資料庫系統上,有哪些物件是有機會 生後不理 呢?
例如:資料表的 IDENTITY (屬性)。

過往經驗,多半是遇到 IDENTITY (屬性) 用爆了 或 快爆了,才緊急安排 擴容,例如: INT 擴到 BIGINT 等。

如何 事先、定期 檢測 呢?

可以自製 SQL Ops Studio 的 Insight widgets,在 Dashboard (儀表板) 上探查 各個資料表 IDENTITY 使用量 等相關資訊。




本文提供

  • 檢測 各個資料表 IDENTITY 屬性的相關資訊: T-SQL Script。
  • 客製化 SQL Ops Studio 的 Insight widgets,在 Dashboard (儀表板) 上,以 圖表方式呈現 使用量資訊。




Build a custom insight widget: 自製 insight widget



客製化 SQL Ops Studio 的 Insight widgets:

  • 以 圖表、百分比 方式來呈現,已經使用的 IDENTITY 值。
  • 進一步探查 各個資料表 IDENTITY 屬性的相關資訊。



本次練習包含以下工作:


  1. Run your own query and view the result in a chart view: 執行 自訂的 T-SQL,並且使用圖表檢視來呈現結果
  2. Add the custom insight to the database dashboard: 將 自訂的 insight widgets 增加到 資料庫 儀表板
  3. Add details to custom insight widgets: 新增詳細資料 到 自訂的 insight widgets



Run your own query and view the result in a chart view
執行 自訂的 T-SQL,並且使用圖表檢視來呈現結果


01. 使用以下 T-SQL 可以探查 各個資料表 IDENTITY 屬性的資料行之資訊:

  • Percent:計算 IDENTITY 已使用的百分比。
  • Last_Value:最新的 IDENTITY 值。
  • Data_Type:IDENTITY 使用的資料類型。


-- Identity Column Usage: Detail
SELECT TOP 5 Table_Column, CAST([Percent] AS Numeric(5,2)) [Percent], Last_Value, Data_Type
FROM ( 
    SELECT '[' + sc.name + '].[' + ta.name + ']' + '].[' + a.name + ']' Table_Column,
        CONVERT(Numeric(17,9), ( ((CONVERT(Float, last_value)) / CONVERT(Float, Case
    When b.name = 'tinyint'   Then 255 - cast(a.seed_value as float)
    When b.name = 'smallint'  Then 32767 - cast(a.seed_value as float)
    When b.name = 'int'       Then 2147483647 - cast(a.seed_value as float)
    When b.name = 'bigint'    Then 9223372036854775807 - cast(a.seed_value as float)
     End)) * 100)) [Percent]
        ,CONVERT(bigint, ISNULL(a.last_value,0)) Last_Value
        , b.name Data_Type          
    FROM sys.identity_columns a INNER JOIN sys.types As b ON a.system_type_id = b.system_type_id 
        LEFT JOIN sys.tables ta on ta.object_id = a.object_id 
        LEFT JOIN sys.schemas sc on sc.schema_id = ta.schema_id
) as t
WHERE [Percent] is not null
ORDER BY [Percent] DESC;



-- figure  31_Identity Column Usage Detail




02. 但若要用於 insight widget 的圖表上,應該使用 數值 類型 來呈現。調整 T-SQL 如下:

  • 百分比 方式來呈現,已經使用的 IDENTITY 值。

-- Identity Column Usage: [Percent] 
SELECT TOP 5 Table_Column, CAST([Percent] AS Numeric(5,2)) [Percent] 
FROM ( 
    SELECT '[' + sc.name + '].[' + ta.name + ']' + '].[' + a.name + ']' Table_Column,
        CONVERT(Numeric(17,9), ( ((CONVERT(Float, last_value)) / CONVERT(Float, Case
    When b.name = 'tinyint'   Then 255 - cast(a.seed_value as float)
    When b.name = 'smallint'  Then 32767 - cast(a.seed_value as float)
    When b.name = 'int'       Then 2147483647 - cast(a.seed_value as float)
    When b.name = 'bigint'    Then 9223372036854775807 - cast(a.seed_value as float)
     End)) * 100)) [Percent]
    FROM sys.identity_columns a INNER JOIN sys.types As b ON a.system_type_id = b.system_type_id 
        LEFT JOIN sys.tables ta on ta.object_id = a.object_id 
        LEFT JOIN sys.schemas sc on sc.schema_id = ta.schema_id
) as t
WHERE [Percent] is not null
ORDER BY [Percent] DESC;


-- figure  11_Identity Column Usage [Percent]




03. 點選 右邊的 View as Chart。

-- figure 12_Chart Viewer




04. 在 insight widget 組態,在 CHART VIEWER 頁籤,設定 Chart 的圖表格式:


  • 在 右邊的 Chart Type,選擇:horizontalBar
  • 在 Data Direction,選擇:Vertical


-- figure 61_Configure_Chart Type



05. 對 Chart 圖表的 初步設定,以橫條圖方式來呈現。


-- figure 13_Chart_Type_horizontalBar



06. 讓我們再增加一點資訊到 Chart 上吧。以下加上了 資料行名稱,並設定其呈現的位置。
  • 勾選 Use Column names as labes?
  • 在 Legend Position,選擇:bottom

-- figure 14_Use_Column




07. 觀賞一下,建立好的 Chart 圖表。

-- figure 15_Label_Column






Add the custom insight to the database dashboard
將 自訂的 insight 增加到 database dashboard



01. 在 insight widget 組態,在 CHART VIEWER 頁籤,點選 左邊的 Create Insight

-- figure 16_Create Insight




02. 點選 Create Insight 後,將產生新的頁籤: {} Insight

  • 這是將先前的 insight Chart,轉為 JSON 資料格式。

-- figure 17_insight configuration (the JSON data)




03. 複製此 insight 的 JSON資料。

04. 按下鍵盤的組合鍵:Ctrl+Shift+P,在 Command Palette(命令選擇區),開啟 User Settings (使用者設定)。

05. 在 搜尋方塊 中,輸入 Settings,再選擇 Preferences: Open User Settings



06. 在 邊 的 USER SETTINGS,檢視 dashboard.database.widgets

可以觀察到已經有 2 個 Insight widgets(小工具),依序是:

  • task-widget
  • explorer-widget


-- figure 18_USER_SETTINGS




07. 資料庫上的 檢視 Dashboard (儀表板),就可以看到這 2 個 Insight widgets。

-- figure 20_two_widget_configureDashboard_explorer-widget



讓我們來增加 第 3 Insight widgets。


08. 回到 dashboard.database.widgets,複製 先前所產生的 JSON 格式資料 (Insight widgets),貼到以下位置,請參考下圖所示。


JSON 格式的特性:

  • 任何物件,是以 { 開始,並以 } 結束,來寫入資料。
  • name 為字串,必須以 "" 括起來。
  • name 與 value 之間使用 : 分隔。
  • 多個 name:value 之間使用 , 分隔。 

"queryFile"

  • 這是用來指示 存放  T-SQL Script 的檔案路徑。
  • 當需要使用此 Insight widgets 時,就會自動去執行此 T-SQL Script,將結果呈現於 Dashboard (儀表板) 上。
  • 預設的檔案路徑是:C:\Program Files\SQL Operations Studio。

-- figure 21_Paste the insight configuration JSON





09. 將先前的 T-SQL Script: 以 百分比 方式來呈現,已經使用的 IDENTITY 值,存放到以下路徑:

  • 建立資料夾:C:\SQLOps
  • 存放路徑與檔名:C:\SQLOps\Identity_Column_Usage_Percent.sql


-- figure 22_Save_File_SQLOps_Identity_Column_Usage_Percent





10. 修改 "queryFile" "name"

  • "name" 設定其值為:"Identity Column Usage" 。
  • "queryFile" 設定其值為:/SQLOps/Identity_Column_Usage_Percent.sql。

-- figure 23_Update_queryFile




11.  儲存 User Settings 檔案。


12. 在 左邊 的 SERVERS Sidebar,在 伺服器與資料庫管理的 Dashboard (儀表板)。

  • 選擇資料庫 AdventureWorks2014,滑鼠右鍵,選擇 Manage


-- figure 24_SERVERS_DB_database dashboard_Manage





13. 就可以看到我們所建立的 Insight widgets: Identity Column Usage


-- figure 25_SERVERS_DB_database dashboard_Manage



來個放大的特寫:

-- figure 26_widget_Identity Column Usage



14. 在此 Insight widgets 的 右邊,先點選 省略符號(...),點選  Run Query。可以看到,此 Insight widgets 所執行的 T-SQL Script。

-- figure 27_Toggle_More




15. 可以看到,此 Insight widgets 所執行的 T-SQL Script。

-- figure 28_Run_Query








Add details to custom insight widgets
新增詳細資料 到 自訂的 insight widgets



利用先前建立的 Insight widgets: Identity Column Usage ,可以得知 以 百分比 方式來呈現 資料表的資料行,已經使用的 IDENTITY 值。

若要想更進一步知道細節資訊,例如:該  IDENTITY 值 最新的資料值,使用的 資料類型等。
除了,還要再自行執行 T-SQL Script 外,其實,這是可以整合到先前所建立的 Insight widgets 裡。



01. 檢視 T-SQL Script: 探查 各個資料表 IDENTITY 屬性的資料行之資訊:

  • Percent:計算 IDENTITY 已使用的百分比。
  • Last_Value:最新的 IDENTITY 值。
  • Data_Type:IDENTITY 使用的資料類型。


-- figure 31_Identity Column Usage Detail




02. 將查詢 T-SQL Script: 探查 各個資料表 IDENTITY 屬性的資料行之資訊,存檔為:

  • 存放路徑與檔名:C:\SQLOps\Identity_Column_Usage_Detail.sql


-- figure 32_Save_File_SQLOps_Identity_Column_Usage_Detail




03. 繼續編輯 Insight widgets: Identity Column Usage。在  "queryFile" 之後,增加以下 script,請參考下圖所示:


        {
            "name": "Identity Column Usage",
            "gridItemConfig": {
                "sizex": 2,
                "sizey": 1
            },
            "widget": {
                "insights-widget": {
                    "type": {
                        "horizontalBar": {
                            "dataDirection": "vertical",
                            "dataType": "number",
                            "legendPosition": "bottom",
                            "labelFirstColumn": false,
                            "columnsAsLabels": true
                        }
                    },
                    "queryFile": "/SQLOps/Identity_Column_Usage_Percent.sql",
                    "details": {
                        "queryFile": "/SQLOps/Identity_Column_Usage_Detail.sql",
                        "label": "Table_Column",
                        "value": "Percent"
                    }
                }
            }
        }


-- figure 33_Add_details_queryFile




04. 儲存 User Settings 檔案。



05. 在 左邊 的 SERVERS Sidebar,在 伺服器與資料庫管理的 Dashboard (儀表板)。

  • 選擇資料庫 AdventureWorks2014,滑鼠右鍵,選擇 Manage
  • 在此 Insight widgets 的 右邊,先點選 省略符號(...)。
  • 再點選 Show Details


-- figure 34_Show_Details




06. 點選 Show Details 後,在 右邊的 Insights 視窗,可以看到 詳細資料:


  • 在 上方的 ITEMS  表格,上下移動,選擇要觀察的 Column。
  • 在 下方的 ITEM DETAILS 表格,將自動呈現該 Column 的詳細資料。


-- figure 35_Insights_Detail




07. 檢視整個 Dashboard (儀表板)。


-- figure 41_Fully_Identity Column Usage




08. 檢視 Dashboard ,Insight widgets: Identity Column Usage,加上 詳細資料。

-- figure 42_Show_Details







檢視 settings.json



01. 先前所建立的 Insight widgets 等 User Settings 組態資訊,是存放於 settings.json
可以到以下檔案路徑,檢視此檔案。


  • 檔案路徑:"C:\Users\Administrator\AppData\Roaming\sqlops\User\settings.json"


-- figure 43_settings_json_file








Insight widgets 要執行 T-SQL Script 路徑不正確


若 Insight widgets 要執行 T-SQL Script 路徑不正確,錯誤訊息如下:


-- figure 51_Error_widgets



-- figure 52_Error_widgets




檢視 "queryFile"

-- figure 53_Error_queryFile




預設的檔案路徑是:C:\Program Files\SQL Operations Studio。

-- figure 54_Default_Path_SQL_Ops





Sample Code


20180530_Build a custom insight widget
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing





Reference


[SQL Ops Studio] Enable built-in insight widgets - table space usage insight
http://sharedderrick.blogspot.tw/2018/04/sql-ops-studio-enable-built-in-insight.html

SQL Ops Studio: SQL Operations Studio is a free tool
http://sharedderrick.blogspot.tw/2018/04/sqlops-sql-operations-studio.html

SQL Ops Studio: Connect and query SQL Server using SQL Ops Studio
http://sharedderrick.blogspot.tw/2018/04/sql-ops-connect-and-query-sql-server.html

SQL OPS: Server Group
http://sharedderrick.blogspot.tw/2018/04/sql-ops-server-group.html

SQL Ops Studio: Selecting the Color Theme - Dark theme
http://sharedderrick.blogspot.tw/2018/04/sql-ops-selecting-color-theme-dark-theme.html

SQL Ops Studio: Sidebar (資訊看板)
http://sharedderrick.blogspot.tw/2018/04/sql-ops-studio-sidebar.html