Nolio RA MSSQL Server Performance Problems
search cancel

Nolio RA MSSQL Server Performance Problems

book

Article ID: 207754

calendar_today

Updated On:

Products

CA Release Automation - DataManagement Server (Nolio) CA Release Automation - Release Operations Center (Nolio)

Issue/Introduction

SQL Performance problems can cause various problems in Nolio Release Automation. The problems might range from deployments taking an extremely long time to failures in ROC actions and/or agent connectivity failures (if the NES heartbeats are not being processed in a timely manner). 

Environment

Release : 6.6 and above

Cause

This general topic of this article is to shed light on various things that can help isolate/remediate performance problems in MSSQL Server. It includes configuration settings, tools that can be used to evaluate performance of queries, performance metrics that can be used to monitor for performance problems in MSSQL Server. This article is not specific to the Nolio database server. This article is not a definitive guide for troubleshooting performance problems. Troubleshooting performance problems should be handled by the MSSQL Server's DBA. This article offers suggestions. Some utilities may or may not be available. If they are not available then they should not be installed without the proper evaluation by your company. 

Resolution

Performance Metrics

Performance metrics can help identify areas of the system that are not performing well. These are some performance counters that may shed light on some performance problems. A problem with performance counters is knowing what's good and what's bad. With this in mind some notes are included below pointing to references that offer perspective on acceptable values. Performance metrics are something that can be captured in a variety of ways. There may be options in SQL Server. There is Windows Perfmon/Resource Monitor, pssdiag/sqldiag and other performance monitoring utilities like Unified Infrastructure Management. 

 

CPU Counters:

Processor Information(_Total)\% Privileged Time
Processor Information(_Total)\% Processor Time
Processor Information(_Total)\% User Time

Process(sqlservr)\% Privileged Time
Process(sqlservr)\% Processor Time
Process(sqlservr)\% User Time

SQLServer:SQL Statistics\Batch Request/sec
SQLServer:SQL Statistics\SQL Compilations/sec
SQLServer:SQL Statistics\SQL Re-Compilations/sec

 

Memory Counters:

SQLServer:Buffer Manager\Buffer cache hit ratio
SQLServer:Buffer Manager\Checkpoint pages/sec
SQLServer:Buffer Manager\Lazy writes/sec
SQLServer:Buffer Manager\Page life expectancy
SQLServer:Memory Manager\Granted Workspace Memory (KB)
SQLServer:Memory Manager\Memory Grants Outstanding
SQLServer:Memory Manager\Memory Grants Pending
SQLServer:Memory Manager\Target Server Memory (KB)
SQLServer:Memory Manager\Total Server Memory (KB)
SQLServer:Memory Manager\Memory Grants Pending

 

Disk Counters:

LogicalDisk (DriveLetter, not _Total)\Avg. Disk sec/Read
LogicalDisk (DriveLetter, not _Total)\Avg. Disk sec/Transfer
LogicalDisk (DriveLetter, not _Total)\Avg. Disk sec/Write
LogicalDisk (DriveLetter, not _Total)\Disk Bytes/sec

 

Searching google for acceptable disk latency metric values, at this time, generally finds that Avg. Disk sec/(read or Write or transfer):

  • Less than 10 ms - very good
  • Between 10 - 20 - okay (0.010 - 0.020)
  • Between 20 - 50 ms - slow, needs attention (0.020 - 0.050)
  • Greater than 50 ms - Serious I/O bottleneck (0.050 - X.000)

 

 

Wait Types:

  • SOS_WORK_DISPATCHER
    • This wait type is where a thread in SQLOS is waiting for something to do. This wait starts when the thread becomes idle and stops when the thread is given some work to do. This is benign and can be ignored.
  • HADR_NOTIFICATION_DEQUEUE
  • HADR_WORK_QUEUE
    • This wait type is when a thread is waiting for some work to be entered into the AG work queue. A benign wait type.
  • HADR_CLUSAPI_CALL
  • HADR_TIMER_TASK

 

Configuration Settings: 

The following configuration settings may help the performance of some queries:

  • LEGACY_CARDINALITY_ESTIMATION from disabled to enabled
  • QUERY_OPTIMIZER_HOTFIXES from disabled to enabled.
  • compatibility_level is set to 130+/SQL2016+ to 120/SQL2014 or below.

To get LEGACY_CARDINALITY_ESTIMATION and QUERY_OPTIMIZER_HOTFIXES settings:
select @@servername, DB_NAME() AS DBName, * from sys.database_scoped_configurations

To get whether or not the NolioDB is using a compatibility mode other than its current version: SQL Server 2016 (130), 2014 (120), 2012 (110), 2008 (100)
select @@servername, name, compatibility_level from sys.databases

Links with more information regarding these settings:

 

Tools

Some of the following tools may help identify problematic queries. If there is a underlying performance problem with Memory, Disk, CPU, etc.. then these tools may or may not help identify those problems. However, it may help isolate queries that are not performing optimally in a way that might be less intrusive than other tools (like Profiler). These tools are not CA/Broadcom tools, CA/Broadcom are not affiliated with the providers and this is not an official endorsement. These tools have been seen to be helpful. However, if they are not already installed on your system then please evaluate them accordingly based on your company's policy. 

 

 

 

Powered by Wolken Software