德瑞克：SQL Server 學習筆記

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 屬性的相關資訊。

本次練習包含以下工作：

Run your own query and view the result in a chart view: 執行自訂的 T-SQL，並且使用圖表檢視來呈現結果
Add the custom insight to the database dashboard: 將自訂的 insight widgets 增加到資料庫儀表板
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

2018-04-27

[SQL Ops Studio] Enable built-in insight widgets - table space usage insight

SQL Ops Studio 內建了多個 Insight widgets(小工具)。

Insight widgets 是

可自訂圖表和圖形，可以加入到伺服器和資料庫監視 Dashboard (儀表板)上來使用。
可以由 T-SQL 所組成。
能以摘要資訊方式呈現，而且能再展開並呈現深入的詳細資訊。

[SQL Ops Studio] Enable built-in insight widgets - table space usage insight

使用內建的 Insight widgets(小工具)
啟用資料表空間使用量 Insight

安裝與啟用內建的資料表空間使用量 Insight widgets(小工具)

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

02. 在搜尋方塊中，輸入 Settings，再選擇 Preferences: Open User Settings。

-- figure 11_Command Palette

03. 檢視已經開啟的 User and Workspace Settings (使用者和工作區設定)，這是用於使用者設定和開啟工作區設定的喜好設定。

User settings file(使用者設定檔) - settings.json，預設的檔案位置：

Windows： %APPDATA%\sqlops\User\settings.json
Mac： $HOME/Library/Application Support/sqlops/User/settings.json
Linux： $HOME/.config/sqlops/User/settings.json

Workspace setting file(工作區設定檔案)

位於: .[!INCLUDE[name-sos](../includes/name-sos-short.md)]專案資料夾中的。

-- figure 12_Preferences Open User Settings

04. 在左邊的 User Settings 的搜尋輸入方塊，輸入 dashboard。

在其下方 json 格式的使用者設定檔，在左邊的 DEFAULT SETTINGS 資料行區域，找到 dashboard.database.widgets 區段(section)。

-- figure 13_dashboard.database.widgets

05. 若是第一次設定，在 json 格式的使用者設定檔內。

在右邊的 USER SETTINGS 資料行區域，預設是沒有 "dashboard.database.widgets" 區段(section)。

在 User settings file 中(使用者設定檔)，可以分成以下區域來設定：

左邊是 DEFAULT SETTINGS 資料行區域。
右邊是 USER SETTINGS 資料行區域。

-- figure 14_no dashboard.database.widgets

06. 編輯設定 "dashboard.database.widget" 區段。

在左邊的 DEFAULT SETTINGS，使用滑鼠移動找到 dashboard.database.widgets 區段(section)。
在左邊會出現 pencil icon(鉛筆圖示)，可以看到 Edit。

-- figure 15_pencil icon

07. 在快顯示窗中，若出現 Copy to Settings，滑鼠點選此。

若出現 Replace in Settings，請取消點選。應該可以在右邊的 USER SETTINGS 資料行區域找到 "dashboard.database.widgets" 區段(section)。

-- figure 16_Copy to Settings

08. 可以觀察到 dashboard.database.widgets 區段(section)，已經複製到右邊的 USER SETTINGS 資料行區域。

-- figure 17_Copy to Settings

若出現 Replace in Settings，請取消點選。
應該可以在右邊的 USER SETTINGS 資料行區域找到 "dashboard.database.widgets" 區段(section)。

-- figure 18_Dont_Click_Replace in Settings

09. 檢視 "dashboard.database.widgets" 區段(section)。

-- figure 21_dashboard.database.widgets

10. 在 Dashboard.database.widgets 區段，加入以下資訊：

此為：啟用內建的資料表空間使用量 Insight widgets(小工具)

{
         "name": "Space Used by Tables",
         "gridItemConfig": {
             "sizex": 2,
             "sizey": 1
         },
         "widget": {
             "table-space-db-insight": null
         }
     },

-- figure 22_JSON_Space Used by Tables