Performance Co-Pilot for Microsoft SQL Server with RHEL 8.2

table { border: #ddd solid 1px; } td, th { padding: 8px; border: #ddd solid 1px; } td p { font-size: 15px !important; }

Microsoft SQL Server 2017 and SQL Server 2019 are now certified for production workloads on Red Hat Enterprise Linux (RHEL) 7.7/7.8 and RHEL 8.1/8.2. With these certifications, customers can take advantage of the new features in RHEL 8.2 including Performance Co-Pilot module for SQL Server as well as HA support for SQL Server distributed availability groups. This post focuses on the Performance Co-Pilot capabilities of RHEL for SQL Server.

If you’re using SQL Server with RHEL, have you ever wondered how to track down database performance issues?  For many database administrators, performance analysis is considered a black art, requiring deep expertise with the operating system, applications, networking stack and platform diagnostic tools. Today many Linux distributions come with a plethora of tools to monitor and troubleshoot OS performance issues. However, many of these tools are hard to integrate together and don’t play well with application level performance metrics.

Performance Co-pilot (PCP) in RHEL is an open source performance monitoring and management toolkit for analyzing, and remediating live and historical system performance issues. It’s distributed, and has a plugin based architecture, making it well-suited for centralized analysis of complex environments including those running mission-critical database workloads like Microsoft SQL Server on RHEL.

Red Hat and Microsoft have been collaborating for several years to deliver interoperable high-performance data solutions for enterprise customers.  RHEL 8.2 introduces the SQL Server plug-in for PCP for the first time and the code specific to SQL Server within PCP can be found here.  

In this post, let’s take a look at how we can now use PCP to monitor and troubleshoot performance with  Microsoft SQL Server 2019 on RHEL 8.2. 

Setting up PCP support for SQL Server

First, let’s install the required PCP packages using yum.

> sudo yum install pcp-zeroconf -y

Getting started with SQL Server 2019 on RHEL

If you haven’t yet tried out SQL Server on RHEL, here are a few quick and handy links to get you started: 

1. Follow Microsoft’s quick start instructions to run SQL Server 2019 on Red Hat Enterprise Linux 8

2. You also need to install the Microsoft ODBC driver installed on Red Hat Enterprise Linux 8

Getting dependant Python and GCC packages

The PCP Microsoft SQL Server Performance Metrics Domain Agent (PMDA) queries important performance metrics from SQL Server using SQL Servers Dynamic Management Views (DMVs). The current implementation had its genesis many years ago on Windows, where it was written in Perl and was used to extend the available metrics from APIs there. That code was rewritten as  PMDA in Python at the request of the Red Hat performance team. It interacts with SQL Server using the Python-based pyodbc driver.   

Install the GCC and Python libraries, before installing pyodbc: 

> sudo yum install gcc-c++ python3-devel unixODBC-devel -y
> sudo pip3 install pyodbc

Set up and install PMDA agent for SQL Server

Now that we have all the required dependencies, let’s install the module.

> sudo yum install pcp-pmda-mssql -y

Before we install the agent and PCP runs it, we need to configure it to connect to our SQL Server instance.

To configure the agent, edit the file /var/lib/pcp/pmdas/mssql/mssql.conf to set the following parameters: 

• username - a SQL Server account with access rights to performance data

• password - SQL Server user password for this account

The contents of the mssql.conf file should look like this, and you can modify the username and password that should be used to connect to SQL Server.

[connection]
driver={ODBC Driver 17 for SQL Server}
server=tcp:localhost
timeout=2

[authentication]
username=pcpuser
password=P4$$W0rd

Note: You should set up the user to have the fewest privileges necessary to query the DMV in SQL Server. To query a dynamic management view or function in SQL Server requires SELECT permissions on the object and VIEW SERVER STATE or VIEW DATABASE STATE permissions. More information can be found here.

You should also make sure that the configuration file mentioned above, is owned by the root user and group, and that appropriate access permissions are set. 

> sudo chown root:root /var/lib/pcp/pmdas/mssql/mssql.conf

> sudo chmod 400 /var/lib/pcp/pmdas/mssql/mssql.conf

Finally, install the agent: 

> cd /var/lib/pcp/pmdas/mssql

> sudo ./Install

Output of installation should look like this: 

[dpredhat@localhost mssql]$ sudo ./Install
Updating the Performance Metrics Name Space (PMNS) ...
Terminate PMDA if already installed ...
Updating the PMCD control file, and notifying PMCD ...
Check mssql metrics have appeared ... 168 metrics and 598 values

Note the successful message indicating that mssql metrics have appeared.

Verify PCP has loaded the SQL Server PMDA Agent 

Use the PCP command to verify that SQL Server PMDA agent (mssql) is loaded and running.

> pcp
Performance Co-Pilot configuration on localhost.localdomain:
 platform: Linux localhost.localdomain 4.18.0-167.el8.x86_64 #1 SMP Sun Dec 15 01:24:23 UTC 2019 x86_64
 hardware: 6 cpus, 1 disk, 1 node, 3777MB RAM
 timezone: EDT+4
 services: pmcd
     pmcd: Version 5.0.2-1, 11 agents, 4 clients
     pmda: root pmcd proc pmproxy xfs linux mmv kvm 
 pmlogger: primary logger: /var/log/pcp/pmlogger/localhost.localdomain/20200415.00.12<
     pmie: primary engine: /var/log/pcp/pmie/localhost.localdomain/pmie.log

SQL Server Metrics

Use the PCP pminfo command to see a complete list of the metrics run in SQL Server on RHEL.

> pminfo mssql

There are more than 150 metrics tracked across different SQL Server resources. For example, the mssql.latches.latch_waits metric captures a number of latch waits in SQL Server, which can be defined as a latch request that cannot be granted immediately. Long I/O latch waits may indicate problems with the underlying disk subsystem that must be looked at.

If you’re a system administrator, here are some other key metrics that you might want to consider when understanding SQL Server performance: 

Printing and visually tracing metric results

Using the pmrep command, you can take any of the available metrics and report them in ASCII tables. The ASCII table output makes it suitable for exporting into databases or other reporting generators. In the example below, we’re printing the SQL Server query compilation metric every 1 second, over a 5 second time window. 

pmrep -t 1 -T 5 mssql.sql_statistics.compilations

In addition to printing metrics on the command line, you can create graphical charts using the PCP charts application (pmchart). To use PCP charts, install the pcp-gui package using yum.

> sudo yum install pcp-gui -y

To launch PCP charts, type and run the pmchart command.

> pmchart

Pmchart is a desktop-based graphical application that enables you to chart useful data including PCP metrics for SQL Server.  For example, you can chart the rates at which you’re seeing different types of query errors, or useful information about transactions or query times that might help you identify performance issues.  Check the RHEL documentation to learn more details on how to use pmchart. 

Creating a new chart in pmchart for SQL Server metrics.

Showing compilations and recompilation statistics for a SQL Query 

Future evolution

In the future, we are looking to extend the SQL Server agent in PCP by adding Active Directory support and automating the set up of collecting SQL Server metrics through a RHEL system role. 

Conclusion

While you may choose to address SQL Server performance issues at the system level, by adding memory or processors, these solutions can be expensive and usually don’t address root causes of slow database performance.  

The information provided by PCP can assist you with improving performance by helping you track down common issues such as poorly written T-SQL queries and inefficient index configuration giving you a far more pointed solution instead of masking a problem by adding hardware.

The RHEL 8 documentation provides additional content which is designed to help you to diagnose and address operating system performance-related problems. This shows you how to use the new features of Performance Co-Pilot to help monitor and troubleshoot performance issues with  SQL Server 2019 on RHEL 8, but the best way to get to know the technology is to follow the steps above and try it out yourself.

The Quickstart Guide from Microsoft will show you how to get started with installing and configuring SQL Server on RHEL. For more general information on SQL Server on RHEL 8 please visit Red Hat’s  Microsoft SQL Server on Red Hat Enterprise Linux site.