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:
-
Follow Microsoft’s quick start instructions to run SQL Server 2019 on Red Hat Enterprise Linux 8
-
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:
Metric name |
Why is it important? |
|
mssql.os_workers_waiting_cpu.count |
Discovers if SQL Server worker threads are blocked and waiting for CPU resources. The higher the count, the more CPU contention, which can slow down the SQL workload. |
|
mssql.memory_manager.total_server_memory |
Helps to discover if server memory is an issue, such as other processes taking memory SQL Server needs to perform well. |
|
mssql.wait_statistics.network_io_waits |
Discovers how long SQL Server time is spent waiting for network I/O. Long wait times could mean that the network adapter is bottlenecked, and network settings need to be looked at. |
|
mssql.access_methods.page_splits |
Helps to understand the fragmentation of data tables. As you add more rows into a table, SQL Server will split the data page into multiple pages, to evenly balance the rows across the pages. Too much page splitting can impact performance since this is an expensive operation. |
|
mssql.sql_statistics.batch_requests |
Helps to understand the number of query batches that SQL Server receives per second. When slow query execution is reported, this is among the top metrics to look at along with CPU and I/O performance metrics. |
|
mssql.sql_statistics.recompilations |
After query compilation, the query plan is added to the SQL Server plan cache. These metrics help to understand the query plan cache hit ratio, and whether any additional compilations were done unnecessarily. |
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 Creating a new chart in pmchart for SQL Server metrics](/rhdc/managed-files/image2_33.png)
Creating a new chart in pmchart
for SQL Server metrics.
![Showing compilations and recompilation statistics for a SQL Query Showing compilations and recompilation statistics for a SQL Query](/rhdc/managed-files/image1_46.png)
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.
关于作者
Nathan is an engineer in Red Hat's Platform Tools group, leading the Grafana and PCP team.
Louis Imershein is a Product Manager at Red Hat focussed on Microsoft SQL Server and database workloads. He is responsible for working with Microsoft and Red Hat engineering to ensure that SQL Server performance, management, and security is optimized for Red Hat platforms. For more than 30 years, Louis has worked in technical support, engineering, software architecture, and product management on a wide range of OS, management, security, and storage software projects. Louis joined Red Hat as part of the acquisition of Permabit Technology Corporation, where he was VP of Product.
Don Pinto is a Technical Product Marketing Manager at Red Hat focused on helping customers understand why Red Hat Enterprise Linux is an ideal operating system platform for modern application workloads. Pinto is passionate about data management and operating systems, having authored several technical blogs and white papers on various tech topics. Pinto holds a Masters degree in Computer Science and a Bachelor's degree in Computer Engineering from the University of Toronto, Canada.
产品
工具
试用购买与出售
沟通
关于红帽
我们是世界领先的企业开源解决方案供应商,提供包括 Linux、云、容器和 Kubernetes。我们致力于提供经过安全强化的解决方案,从核心数据中心到网络边缘,让企业能够更轻松地跨平台和环境运营。