피드 구독

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: 

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
mssql.memory_manager.stolen_server_memory
mssql.memory_manager.reserved_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
mssql.sql_statistics.compilations
mssql.plan_cache.cache_hit_ratio

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.

 

Showing compilations and recompilation statistics for a SQL Query

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.


저자 소개

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.

Read full bio

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.

Read full bio
UI_Icon-Red_Hat-Close-A-Black-RGB

채널별 검색

automation icon

오토메이션

기술, 팀, 인프라를 위한 IT 자동화 최신 동향

AI icon

인공지능

고객이 어디서나 AI 워크로드를 실행할 수 있도록 지원하는 플랫폼 업데이트

open hybrid cloud icon

오픈 하이브리드 클라우드

하이브리드 클라우드로 더욱 유연한 미래를 구축하는 방법을 알아보세요

security icon

보안

환경과 기술 전반에 걸쳐 리스크를 감소하는 방법에 대한 최신 정보

edge icon

엣지 컴퓨팅

엣지에서의 운영을 단순화하는 플랫폼 업데이트

Infrastructure icon

인프라

세계적으로 인정받은 기업용 Linux 플랫폼에 대한 최신 정보

application development icon

애플리케이션

복잡한 애플리케이션에 대한 솔루션 더 보기

Original series icon

오리지널 쇼

엔터프라이즈 기술 분야의 제작자와 리더가 전하는 흥미로운 스토리