Last week I blogged about Using the Wait Statistics Report in Azure Data Studio, using a Linux VM in Azure as an example host. As that was my first time running wait stats analysis on a Linux host, I found that the PREEMPTIVE_OS_FLUSHFILEBUFFERS waits on Linux will show up as the top wait on SQL Server 2017 CU6 and higher with default options.

The simple reason for this is that on some Linux servers where the underling I/O subsystem cannot guarantee durable writes when the power fails (i.e. it’s using a volatile write cache), a power failure could lead to data loss within SQL Server. To work around this, 2017 CU6 introduced forced-flush behavior for log and data writing, including things like backups and writes to FILESTREAM files. This means that the writes will be done as normal, and then a call is made to the Windows FlushFileBuffers function, which is translated via the SQLPAL/HostExtension wrapper on Linux into an fdatasync call. There’s a very deep explanation of all the file system internals in Bob Dorr’s excellent post SQL Server On Linux: Forced Unit Access (Fua) Internals.

If you know your I/O subsystem is resilient, you can disable this behavior, as described in KB 4131496.

However, as this will be by far the highest wait on many Linux systems, and as it’s benign, I’ve added it to the list of waits I ignore and updated the Server Reports extension for Azure Data Studio to filter it out as well (this is now live as v1.4).


PS I also just added the VDI_CLIENT_OTHER wait to the ignore list as well (common from Availability Group seeding), and submitted GitHub changes to Server Reports yesterday, which will be v1.5.

Using the Wait Statistics Report in Azure Data Studio

Azure Data Studio, which used to be called SQL Operations Studio, has a bunch of extensions available, including one called Server Reports from Microsoft. Last year they took my wait stats query (from here) and made it into a report as part of Server Reports extension. In this quick post I’ll show you how to install that extension and look at the report.

I’ve been using Azure Data Studio a lot as part of working with SQL Server 2017 on Linux in Azure and I like it a lot. It’s obviously not as comprehensive as SSMS, but for simple stuff it does the job.

You can see the pretty big list of available extensions by hitting the Extensions icon:

In the list that appears, find the Server Reports extension and click the Install button:

Once an extension is installed, you need to activate it. Without reading any documentation, the first time I used Azure Data Studio I couldn’t figure out why the extension wasn’t there once I’d installed it. Click the Reload button to active the extension:

The extension will now show up in the list of enabled extensions:

If the Server Dashboard screen isn’t showing, right-click on one of the servers you’re connected to and select Manage. All the active extensions will show as tabs at the top of the screen, so select Server Reports and then click the heart-shaped icon and you’ll see the current aggregate wait stats for the instance:

Note that the x-axis is percentage of all waits, not wait count. You’ll see that PREEMPTIVE_OS_FLUSHFILEBUFFERS is the top wait on my Linux instance – that’s by design and I’ll blog about that next. I’ve also submitted a GitHub change to add that wait to the list of waits filtered out by script the extension uses.

Anyway, you can drill in to the details by clicking the ellipsis at the top-right of the graph and selecting ‘Show Details’. That’ll give all the waits and by selecting each one you can see the usual output from my waits script. To get more information on what each wait means, select the bottom cell, right-click on the URL to copy it, and paste into your favorite browser to go to my waits library. And of course, you can refresh the results via the ellipsis as well.

There are many more useful extensions that you should check out too – enjoy!

Changing the SSH port for a RHEL Azure VM

I’m working with Red Hat Enterprise Linux (RHEL) VMs on Azure and one of the best practices that Microsoft recommends is to change the default SSH port from 22 to something else. I was looking for a consolidated set of instructions to do this and I couldn’t find one, so I thought I’d do a quick post on it.

The steps that need to be performed are:

  • Allow the new port in the RHEL firewall
  • Change the SSH daemon to listen on the new port
  • Add an incoming rule in the VM network security group for the new port
  • Remove the rule that allows port 22

Changing the SSH port has to be done after creating the VM, as the SSH daemon is set up to listen on port 22 by default, so as part of the VM configuration, make sure to allow port 22 to be opened. Once the VM is created and running, connect to it using your favorite SSH client.

Let’s choose port 52019 to use instead of 22.

To allow the port in the RHEL firewall:

sudo firewall-cmd --permanent --zone=public --add-port=52019/tcp
sudo firewall-cmd --reload

You should see a ‘Success’ message after each of these commands, and you can double check that the firewall rule was added using:

sudo iptables-save | grep '52019'

And you should see:

-A IN_public_allow -p tcp -m tcp --dport 52019 -m conntrack --ctstate NEW -j ACCEPT

To change the SSH daemon to use port 52019 we need to change it’s configuration and restart it. Edit the config file using:

sudo vi /etc/ssh/sshd_config

Now vi is not very user friendly, but it’s easy once you learn it. Use the down arrow so your cursor is over the # at the start of the line that says:

#Port 22

Hit your Insert key until the word REPLACE shows at the bottom of the SSH window. Then type:

Port 52019

Now hit Esc then :w and then Esc then :q to write the file and exit.

To restart the SSH daemon:

sudo service sshd restart

Now go into the Azure Portal and for that VM:

  • Click Networking in the Settings pane and click the blue ‘Add inbound port rule’ button on the right
  • In the wizard that comes up, change the ‘Destination port ranges’ to 52019 and the ‘Name’ to something like ‘IncomingSSH’
  • Click the blue ‘Add’ button

Once the rule has been added, you should be able to create a new SSH connection to the VM using port 52019. Once you’ve done that, drop the initial SSH connection that’s still using port 22 by going back to the Networking tab in the Azure Portal, finding the existing rule for port 22, click the ‘…’ on the right and selecting Delete.

Hope this helps anyone else looking for concise instructions.