T-SQL Tuesday #154 Invitation โ€“ SQL Server 2022

Introduction

This is my first time participating in T-SQL Tuesday. The Invitation from Glenn Berry asks to write about what you’ve been doing with SQL 2022.

Getting DBA Dash ready for SQL 2022

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.

DBA Dash โ€“ Whatโ€™s new in 2.22.0? ๐ŸŽ‰

Identity Columns collection

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 page
Identity Columns drilldown

More information here.

Backup Compression Algorithms for SQL 2022

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.

‘#Tracking changes to sys.configurations

Download

Download Now

DBA Dash – What’s new in 2.21.0? ๐Ÿš€

Intro

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!

Note: You can see the full change log here.

Metrics tab

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.

See it in action here:

DBA Dash Metrics tab
DBA Dash – Metrics tab

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.