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