Data Relay is a free community conference in the UK for the Microsoft data platform. I submitted a session for DBA Dash which was accepted and I delivered my session on the 4th October 2022 in Leeds.
This was the first time I’ve done something like this and it was outside of my comfort zone. I was excited to show off DBA Dash though. My plan was to demo how to use the tool to solve performance issues.
I created demos in my Lab environment and restored the repository database to my laptop to perform the demos.
To help prepare for speaking at data relay I recorded myself. This allowed my to practice my presentation and get an idea how long it would take to deliver. The actual session at data relay wasn’t recorded but I’ve uploaded my practice session to YouTube. It’s a bit rough around the edges, but hopefully you will find the video useful. 😊
Thank you to @MrKeithAtherton for providing the photo from the event!
I was eager to test DBA Dash with SQL 2022 and started testing with the first public release (CTP 2.0). I added a SQL 2022 instance to my lab environment, created using AutomatedLab.
Unlike my other lab machines, I ran through the installer for 2022 manually to get up and running quickly and test the new installer.
DBA Dash only needed some minor modifications to work with SQL 2022. The first and most important step was to add version 16 to the hardcoded list of supported versions and also update some static data for SQL Server 2022. I hardcode the list of supported versions to give me the opportunity to test new versions first and fix any issues.
This initial support for SQL 2022 was added in May. I’ve been running DBA Dash in my lab environment since then and it works well.
DBA Dash – support for new features in SQL 2022
QAT_DEFLATE Backup Compression
Version 2.22.0 adds support for capturing the compression algorithm used on SQL 2022.
Glenn Berry has some good articles on this new backup compression algorithm here and here. I’m getting similar results to Glenn with my own testing using QAT_DEFLATE (software mode) – faster backups, higher compression and lower resource utilization.
sys.databases
SQL 2022 adds some new columns to sys.databases:
is_data_retention_on
is_ledger_on
is_change_feed_enabled
I’m planning to add these to the DBA Dash collection. Only is_ledger_on is currently documented.
Other
On the to-do list is to test DBA Dash with contained availability groups. DBA Dash has monitoring for agent jobs and availability groups so it’s likely some changes will be required in this area.
What I’m excited about in SQL 2022
The intelligent query processing features in SQL 2022 and tempdb concurrency enhancements are very interesting. There is also some really useful language enhancements like GENERATE_SERIES, DATE_BUCKET, GREATEST and LEAST that I can see myself using. Null handling also gets easier with IS [NOT] DISTINCT FROM. I can’t use any of this for DBA Dash as I want to keep the repository compatible with SQL 2016 for now. The language enhancements will be useful for some of the SaaS databases I manage though once we upgrade.
The ability to failover back and forth between Azure managed instance and SQL 2022 is also a game changer. It certainly takes some of the risk out of the process if you are considering using managed instances.
The feature I’m most excited about? Backup/Restore to S3. If you host SQL instances on AWS you have to figure out how to get your backups to S3. There isn’t a standard way to do this. Many people backup to an EBS volume first then push the files to S3 which isn’t ideal. EBS storage is expensive and the two step process to get the backups to S3 adds time and complexity to the process. The restore process is also slower and more complex as you need to pull the backups from S3 first before you can restore them.
The ability to backup directly to S3 is a huge win. It also has value outside of AWS as there are a number of other S3 compatible storage providers.
If your table runs out of identity values, you might see an error message similar to the one below:
Msg 8115, Level 16, State 1, Line 7 Arithmetic overflow error converting IDENTITY to data type int.
DBA Dash now has monitoring for identity values. You can see when you are about to run out of identity values which allows you time to plan and fix these problems before they cause errors in your database.
Identity Columns on DBA Dash Summary pageIdentity Columns drilldown
SQL 2022 has the option to use Intel QAT backup compression. In software mode this is supported on standard and enterprise editions. It’s significantly faster, produces higher levels of compression and uses less resources. Hardware mode is just supported on enterprise edition and can significantly offload the CPU cost of compressing your backups.
Glenn Berry has some good articles on this here and here.
So what’s this got to do with DBA Dash? DBA Dash now captures the compression algorithm used from msdb.dbo.backupset for your latest backups. You can now where QAT_DEFLATE compression is in use.
New columns added to capture compression algorithm used for last backup
DBA Dash also tracks sys.configurations settings in use across all your SQL instances, including the new configuration options used to enable QAT_DEFLATE compression. You can see the configuration setting in use and also track when settings have been changed.
The first public release of DBA Dash was in January 2022 and version 2.21.0 is the 24th release.🎉 A lot has changed with many new features added as well as bug fixes. I haven’t really blogged much about the changes, but I thought it might be good to capture what its new for significant releases. So here is what is new and cool in 2.21.0!
The metrics tab was previously a wall of numbers – the Max, Min, Avg, Total & Current value of all the collected performance counters. At the click of a button you could see a chart for any counter over time for the selected period. It was limited to showing 1 chart at a time.
In 2.17.0 it was possible to configure threshold values for your counters – improving on the wall of numbers by highlighting counters that need your attention.
Now in 2.21.0 when you click view to view a chart for a particular counter, it adds a new chart. You can view charts for multiple counters simultaneously. Also, you can add other charts that are not os performance counters – CPU, Blocking, IO, Object Execution and Waits. This provides additional context for your performance counters. You can re-order the charts and remove them at the click of a button.
Once you’ve created a useful dashboard you can save and re-load it at the click of a button. You can save the view for yourself or share it with your team.
Tip: You can easily configure which performance counters are collected. Also, you can collect any metric you like that can be queried with T-SQL. See here for details on how to customize the collection.
Performance Summary tab
The performance summary tab now has saved views the same as the metrics tab. You could save the layout previously but it wasn’t possible to save multiple versions or to share those with the rest of the team.
