SQL Server on Red Hat Enterprise Linux at Data Platform Virtual Summit 2021

At this year's Data Platform Virtual Summit (DPS) 2021, I was honored to present “Monitoring SQL Server on Linux performance with Performance Co-Pilot and Grafana.” In this post I'll cover some of the topics I touched on during my talk and include some suggestions for DBAs who might be new to Linux.

DPS was traditionally geared toward DBAs in the Microsoft ecosystem, some of whom may have only worked in Windows environments. These days, with one third of SQL Server DBAs surveyed running SQL Server on Linux, you'll find an increasing number of presentations covering topics around Linux and Kubernetes environments. 

With an audience that's new to Linux in mind, I started off my presentation with a general overview of the more traditional Linux performance tools used today such as top, ps, vmstat, and sar.  

We then looked at the new tools (bcc-tools and PCP) that powers the new Microsoft SQL Server PCP Grafana dashboard, which I developed for SQL Server on Red Hat Enterprise Linux (RHEL). Following this, I demonstrated how to install and configure Grafana and the PCP dashboard for SQL Server. I wrapped up by demonstrating some of the ways database and system administrators can help increase performance of SQL Server on RHELwith tuning,  and spoke a bit of the impact that those changes can have on overall performance of SQL Server workloads.

If you attended the live Q&A session or listened to the recording, your next step is to gain some hands-on experience with SQL Server on Red Hat Enterprise Linux and try out the new performance tools. Now there's an easier way to do that — the good news is that several of the environments I used in my presentation are available as interactive labs at lab.redhat.com.  

If you’re a DBA, here are some of my top recommended performance labs that you should definitely check out:

• Using bcc-tools with RHEL 

• Configuring PCP to monitor SQL Server on RHEL performance 

• Tuning SQL Server and RHEL for performance 

Use XFS for SQL Server

One question that I am frequently asked is, “How do I properly configure a file system for use with SQL Server on RHEL?” I strongly recommend that you use the XFS file system for SQL Server volumes. By using XFS you will be able to take advantage of Microsoft’s traceflag 3979 and control.writethrough features, which allows you to take advantage of Force Unit Access (FUA) support in your storage layer to increase write performance.   

You should also consider creating any dedicated database (data, log, or temp) filesystems with the mkfs_xfs -n version=ci option. This creates an XFS filesystem that is case preserving but not case-sensitive, the normal behavior for Windows filesystems. If you use this option SQL Server has less work to do when checking for existing files since it doesn’t have to worry about the case of existing files to locate them.  

Additionally, for best performance, mount your database filesystems with the -o noatime option so that the RHEL kernel doesn't need to update the access time every time an insert or query is made in a high-performance online transaction processing scenario.

Check out these talks as well

There were several other talks about SQL Server on Linux at the DPS conference I'd recommend. Three of my favorites were delivered by Microsoft’s Amit Khandelwal: “SQL Server on Linux- Deployments made easy!”, “SQL Server on Linux/Containers and Kubernetes”, and “SQL Stateful Containers on Kubernetes with Helm!” 

In addition to talks about SQL Server on Linux, there were also talks that covered Microsoft’s SQL Server Big Data Clusters and Azure Arc-enabled Data Services, both of which can help you to orchestrate SQL Server deployments on Red Hat OpenShift. 
There’s more — In addition to attending this conference, which I highly recommend, if you’re interested in learning more about running SQL Server on Red Hat Enterprise Linux or Red Hat OpenShift, check out Red Hat’s Microsoft SQL Server on Red Hat Enterprise Linux partner page for analyst research, customer stories, educational videos and more!