Customize SQL Operations Studio Dashboards

By: Douglas Correa   |   Comments   |   Related: > SQL Operations Studio

Problem

SQL Operations Studio is a tool that manages and monitors SQL Server by writing T-SQL scripts, developing procedures, managing users, doing backups and visualizing execution plans. What is a suitable way of creating a dashboard to monitor your database and server information using this tool?

Solution

SQL Operations Studio provides dashboards using widgets and the ability to create them using your own scripts. The dashboards are created by using JSON in the dashboard.database.widgets section in your user settings.

In this tip I'm going to show how to improve your dashboards with the widgets that SQL Operations Studio has and also how to create your own.

SQL Operations Studio Dashboards

To see your default dashboard items, right-click on a database and click manage. To see the server dashboard, right-click on the server name.

SQL Operations Studio Database Dashboard

SQL Operations Studio Server Dashboard

Customize SQL Operations Studio Dashboard

For our example, let's create a dashboard for Query Store. 

To use the Query Store, it is necessary to enable it on the database using the T-SQL code below.

CREATE DATABASE demo
GO

ALTER DATABASE demo SET QUERY_STORE = ON;
GO

To customize a dashboard follow these steps below:

Go to File > Preferences > Settings to open the User Settings editor. Type “dashboard.database.widgets" and let the auto-complete fill in the default values.  The window below will show up.

Click Edit for dashboard.database.widgets to edit the JSON. Copy and paste the Tasks section to create a new item in the dashboard and change the name to "slow queries widget" as shown below.

After editing, the ne JSON will look like the script below.

"dashboard.database.widgets": [
    { "name": "slow queries widget",
         "gridItemConfig": {
              "sizex": 2,
              "sizey": 1
         },
         "widget": {
            "query-data-store-db-insight": null
         }
    }
]

• name: the title of the widget
• gridItemConfig: sizex and sizey specify the dimensions of the widget on the dashboard. Increase x for wider and y for taller.
• widget: the actual widget configuration

You can add more items in the dashboard. For example, add an item to show how much space your database tables are using as follows.

"dashboard.database.widgets": [   
     { "name": "table spaces",
         "gridItemConfig": {
              "sizex": 2,
              "sizey": 1
         },
         "widget": {
            "table-space-db-insight": null
         }
    }
    { "name": "slow queries widget",
         "gridItemConfig": {
              "sizex": 3,
              "sizey": 1
         },
         "widget": {
            "query-data-store-db-insight": null
         }
    }
]

Save your User Settings and refresh your dashboard. To do this, right-click on the database and click manage.

Create Your Own SQL Operations Studio Dashboard Widget

Let's use T-SQL to get server information to create an item on the dashboard. Type the script below and save in as serverInfo.sql.

SELECT 
   SERVERPROPERTY('Edition') AS [Edition], 
   SERVERPROPERTY('ProductLevel') AS [ProductLevel],
   SERVERPROPERTY('ProductVersion') AS [ProductVersion],
   SERVERPROPERTY('BuildClrVersion') AS [Build CLR Version],
   SERVERPROPERTY('ProductBuild') AS [ProductBuild], 
   SERVERPROPERTY('ProductUpdateReference') AS [ProductUpdateReference],
   SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled], 
   SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly],
   SERVERPROPERTY('FilestreamConfiguredLevel') AS [FilestreamConfiguredLevel],
   SERVERPROPERTY('IsXTPSupported') AS [IsXTPSupported],
   SERVERPROPERTY('IsPolybaseInstalled') AS [IsPolybaseInstalled],
   SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS [IsRServicesInstalled];

Now, we are going to add one more widget in the JSON script.

"dashboard.database.widgets": [   
     { "name": "table spaces",
         "gridItemConfig": {
              "sizex": 2,
              "sizey": 1
         },
         "widget": {
            "table-space-db-insight": null
         }
    }
    { "name": "slow queries widget",
         "gridItemConfig": {
              "sizex": 3,
              "sizey": 1
         },
         "widget": {
            "query-data-store-db-insight": null
         }
    }
 ],
 "dashboard.server.widgets": [  
    { "name": "Server Info",
         "gridItemConfig": {
            "sizex": 2,
            "sizey": 2
        },
        "widget": {
             "insights-widget": {
                  "queryFile": "/home/corread/sql/widgets/serverInfo.sql",
                  "type": {
                       "count": null
                  }
             }
        }
    },
    { 
        "widget": {
            "backup-history-server-insight": null
        }
     },
     {
           "widget": {
               "all-database-size-server-insight": null
           }
      }
        
]

The informational and diagnostic queries that are normally run as scripts can be turned into widgets that are easily seen when SQL Operations Studio is opened as shown below.

Conclusion

Using SQL Operations Studio can improve your ability to efficiently and effectively monitor your databases and servers. This can be done by utilizing the default dashboards or by customizing the dashboard to your specific needs as shown above.

Next Steps

• Check out SQL Operations Studio Installation on Linux.
• Read more about Query Editor and Source Control on SQL Operations Studio.

About the author

Douglas Correa is a database professional, focused on tuning, high-availability and infrastructure.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips