Development progress w/e 16/09/2018 (Version 1.1)

Download (v1.1.6833.36316)

Full Changelog at https://sqlwatch.io/docs/changelog/

New PowerBI disk utilisation dashboard

Database utilisation

SQLWTCH disk utilisation dashboard

Key metrics presented on the dashboard:

  • Database growth per day
  • Transaction Log utilisation
  • Data distribution within database files (space allocated to data, indexes, space unallocated and unused)

The dashboard shows space allocation over time which will help to identify expensive file grow operations

Disk utilisation (Win32_Volume)

SQLWTCH disk utilisation dashboard

Some of the metrics include:

  • Total disk allocation across all drives
  • Utilisation trend to visualise variations
  • Daily Growth
  • Block Size with warnings for non-optimal setting
  • Days left until disk fills up (at the current growth)
  • A simple RAG status to flag up disks running low on space

Why new PowerBI dashboard

Initially, I wanted just one dashboard. I much prefer to have all the information available on different tabs of the same report rather than having to open (and maintain) multiple PowerBI dashboards. However, disk utilisation data is tied to a different snapshot_id (2), is point-in-time, collected at a different interval and kept for a longer time. PowerBI always downloads all data sets when refreshed which would mean that if we only wanted to see last 60 minutes of SQL Performance Activity, PowerBI would also (very kindly) download storage data which would impact the performance of the SQL Server and the PowerBI dashboard. This could be addressed if PowerBI handled composite primary keys without workarounds. It can only use single columns to build relationships between tables which would cause duplicate values in the snapshot_header if we didn’t also make the snapshot_type_id part of the relation.

Looks like PowerBI will introduce some limitations that I will have to accept.

Here are the Performance Dashboard relations:

SQLWATCH PowerBI performance schema relations

Performance tweaks

I have also made some performance improvements to reduce interval generation time from ~20 seconds to a few milliseconds.

My performance test kit is a very weak, 2 core Celeron HP Microserver. There is a chance that you did not notice it being slow on your SQL Server (unless you too run it on the Microserver) but if I make it run fast on my kit, it will likely run fast on your production box.

Original

The current view [dbo].[vw_sql_perf_mon_time_intervals]  takes about 20 seconds to run on my test kit:

SELECT * FROM [dbo].[vw_sql_perf_mon_time_intervals]
WHERE report_time_interval_minutes = 15

vw_sql_perf_mon_time_intervals Execution Plan

Things get much better when we limit resultset to a given snapshot_type_id. This takes approx 1 second:

SELECT * FROM [dbo].[vw_sql_perf_mon_time_intervals]
WHERE report_time_interval_minutes = 15
AND snapshot_type_id = 1

vw_sql_perf_mon_time_intervals Execution Plan

 

Improved

After changing to an inline table-valued function a.k.a parametrised view the same result is returned in 289ms:

SELECT * FROM [dbo].[ufn_time_intervals](1,15)

vw_sql_perf_mon_time_intervals Execution Plan

However, we still need to apply WHERE clause to limit our report to the chosen report window. This adds around 25ms and the end to end process and takes around 315ms:

SELECT * FROM [dbo].[ufn_time_intervals](1,15)
WHERE dateadd(mi, [report_time_interval_minutes], [spapshot_interval_start]) > DATEADD(HOUR, -4, GETDATE())
AND dateadd(mi, [report_time_interval_minutes], [spapshot_interval_start]) <= GETDATE()

which produces the below plan:

vw_sql_perf_mon_time_intervals Execution Plan

Not a big impact on the overall performance but the real benefit is achieved when we restrict the number of IO operations. There is no point returning data just to later remove it. Let’s remove it directly at the source and move the WHERE criteria into the function.

Final

This brings the total execution down to 1ms. This reduces the data read from disk down from original 99MB to 1MB after improvements and to 5KB in the final version:

SELECT * FROM [dbo].[ufn_time_intervals](1,15,4,getdate())

vw_sql_perf_mon_time_intervals Execution Plan

Considering the time_interval view (now function) must be called for every query, this is a big performance improvement which translates to a much faster rendering of the PowerBI dashboard. I will be updating the PowerBI dashboard to use this new function soon.

Before you start jumping and rewriting all views to functions – please don’t. This isn’t an answer to all performance problems. There have been reasons why the original view was slow to start with, driven by some functional requirements – such as having to return all intervals in order to filter in PowerBI.

Interval Periods

Currently, there are two interval periods available to select from in the PowerBI dashboard. 5 and 15 minutes. With the introduction of the new function above, it is no longer a requirement to have it hard-coded and therefore in the future, this will be a free text field rather than a drop-down.

SQLWATCH PowerBI new time interval parameter

GitHub releases

I have also started creating GitHub releases to make it easier to download historical versions – for example, previous PowerBI dashboards for people who are not always able to keep databases in sync with our release cycle.

Releases are available here: https://github.com/marcingminski/sqlwatch/releases

This site, like most websites, uses cookies. By continuing to use this website, you agree to their use.