德瑞克：SQL Server 學習筆記: 2018/05

延續前一篇：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