Esat Erkec
Customized database widgets

How to build custom widgets on Azure Data Studio

July 7, 2022 by

In this article, we will learn how to build a customized widget in Azure Data Studio that helps to monitor the performance metrics.

What is Azure Data Studio?

Azure Data Studio (ADS) is a new generation database administration, development, and maintenance environment that can be used for on-premises and cloud data platforms. Azure Data Studio’s target user group is not just limited to database administrators, it also aims at the software developers or other non-DBA users who deal with the databases as part of their jobs. Such as, one of the standout features of ADS is that it is easily customizable and can also gain new features via extensions. If we give an example, after installing the PostgreSQL extension, we can connect any PostgreSQL (also known as Postgres) database and execute the PL/pgSQL queries.

PostreSQL extension for Azure Studio

Another capability of the ADS is that it can work on multiple operating systems:

  • Windows
  • macOS
  • Linux

SQL Server Management Studio’s (SSMS) initial release was published in 2005 and until the announcement of the ADS, it was the alone SQL database management and development tool of Microsoft. Nowadays, some are already starting to think that Azure Studio will run rings around SSMS. However, these two tools have advantages and disadvantages over each other but the following little touches of Azure Studio seem to have made it more attractive:

  • Export Results to CSV, JSON, XLSX
  • Generating customized dashboards easily
  • Dark theme configuration ( The dark theme can be set in SSMS to use the config files)

Despite this, SQL Server Management Studio still offers unique features:

  • Provides to configure and manage Always On through user-friendly interfaces
  • Error log viewer
  • Advanced Extended Events management
  • Can connect to:
    • Analysis Services
    • Integration Services
    • Reporting Services

How to create a custom widget in Azure Data Studio

One of the most advantageous features of ADS is that it allows the creation of customized widgets. With the help of the widgets, we can easily visualize the result of the queries using different graph types. In this context, building the performance monitoring widgets can be a reasonable approach so that we can track the performance metrics readily. Now, let’s learn how to build a custom widget with a very straightforward example.

The sys.dm_exec_requests dynamic management view returns the info about each request that is executing in SQL Server. The wait_type column is one of the important columns of this view that define which source the currently executing query is waiting for.

Tip: SQL Server wait types are one of the significant performance metrics and they are used to monitor the resource bottlenecks and diagnose the performance problems of the SQL Server.

The following query aggregates the wait times of the wait stats so that we can figure out which resource causes a bottleneck at present.

How to use sys.dm_exec_requests view

In the result panel, we click the chart button and select the following properties to customize the chart.

Chart Type: Doughnut

Data Direction: Vertical

Use column names as labels: true

Legend Position: Top

Display of data in graphs

A widget infrastructure uses JSON format to visualize query resultsets. To obtain the JSON of this chart, we will click the Create Insight button, and then the JSON of the chart appears on another screen.

Create Insight functionality

We need to store the query in a specific folder so that the chart can use this query every time the chart is viewed or refreshed. We delete the SQLQuery_2 value of the queryFile key and then place the query file path instead of it. After these steps, the JSON will be as follows:

As the second step, we need to inject the chart JSON that we have prepared into the Azure Data Studio user settings file. We will click the View menu and then click the Command Palette… menu item.

How to use Command Palette

As an alternative way, we can click the Manage button and then click the Command Palette.

Setting of the Azure Studio

After enabling the Command Pallete, we will type the “open user settings” keywords and then will click the Preferences: Open User Settings.

Preferences: Open User Settings.

In the settings tab, we search the widget setting and then click over the Edit in the settings.json hyperlink.

settings.json

On the JSON editor, we right-click over the settings.json tab and select the Reveal in File Explorer menu item and reach the file path of the user setting file of the Azure Data Studio. We take a copy of the settings.json file into any safe path, we may want to restore the previous settings.

the Reveal in File Explorer

In the setting.json file, we inject the following code at the end of this setting file.

Database widgets

The widget will be displayed any database management tab.

Customized database widgets

We can add a drill-down capability to customized widgets in order to access chart details. To do this, we need a query that fetches the data that makes up the details of the chart data. The following query returns each of the active session query text and query plan details.

Result of the dm_exec_sessions

Firstly, we will store this query with a file path and this path can be the same as the chart query file path. As a next step, we need to add the details key to the JSON code of the custom widget therefore we re-open the settings.

  • To open the Command Palette press Ctrl+Shift+P
  • Search Preferences: Open User Settings and click it
  • Search Database widgets and click the Edit in settings.json

JSON codes of a customized chart

We will click the ellipses () in the upper right of the widget and open the details report.

Insight detail report

How to enable built-in performance monitoring widget in Azure Data Studio

As we have learned the Azure Data Studio allows us to create customized widgets and it also offers some built-in widgets. One of these built-in widgets helps to monitor the five slowest queries. In order to enable this widget, we need to make some customizations on the user setting of the ADS. We re-open the setting.json file, we will add the following code at the end of this setting file.

Built-in performance monitoring widget

After saving the setting file, right-click one of the database lists and choose the Manage menu item. The dashboard views the slowest five queries in the database.

Slowest queries chart

To obtain more details about the slowest quires, we click the ellipses () in the upper right and select Show Details. When we click any query on the items report, the subreport shows all details about the query.

Slowest queries chart detail report

Tip: To display more than five queries in the insight widget, we can edit the query that provides the data. This query is placed under the “C:\Users\user_name\AppData\Local\Programs\Azure Data Studio\resources\app\extensions\insights-default\sql” and the query file name is qds.sql and qds_detail.sql. We can edit the top statement in these query files to show more slow queries on the widget.

Enlarged slowest queries chart

Enabling autoRefreshInterval option for the widgets

Azure Data Studio allows adding some basic options to the widgets and one of these options is autoRefreshInterval. With help of this option, the widget can automatically refresh the shown data within specified periods. To enable this feature, we need to add the autoRefreshInterval attribute into the setting of the chart. The autoRefreshInterval is placed after the queryFile key and then specifies the interval value as an integer and in minutes.

autoRefreshInterval option for the widgets

For our sample widget, we determine this value as 1 minute so the widget refreshes all data every one minute.

Summary

Azure Data Studio is a new database management and development platform of Microsoft. There is no doubt that ADS includes some excellent features and one of them is the ability to create a customized chart. Through this feature, we can transform the query result for ease of understanding.

Esat Erkec
377 Views