Full Changelog at https://sqlwatch.io/docs/changelog/
Table of Contents
New PowerBI 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)
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:
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.
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
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
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)
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:
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.
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())
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.
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.
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