Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Customize SQL Operations Studio Dashboards


By:   |   Read Comments   |   Related Tips: More > SQL Server Operations Studio

Attend this free live MSSQLTips webcast

Leveraging Storage Spaces Direct for SQL Server High Availability

Thursday, July 19, 2018 - click here to learn more


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 Dashboard

SQL Operations Studio Server Dashboard

SQL Operations Studio 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.

SQL Operations Studio Dashboard json widget code

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.

SQL Operations Studio Dashboard json widget code

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.

SQL Operations Studio Dashboard

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.

SQL Operations Studio Dashboard

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


Last Update:


next webcast button


next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools