SQL Server for Linux Performance Monitoring Improvements
Last year I posted a blog with examples using Performance Co-Pilot, https://bobsql.com/sql-server-on-linux-quick-performance-monitoring, to monitor SQL Server on Linux.
I wanted to let you know about and give a big thanks to the the Red Hat team for upgrading and enhancing the SQL Server PMDA collector: https://github.com/performancecopilot/pcp/tree/master/src/pmdas/mssql. Red Hat also added new visualization tools such as: https://grafana-pcp.readthedocs.io/en/latest The upgraded PMDA collector script provides similar data and visualizations as the traditional Windows, Performance Monitor.
I have been experimenting with an alternate form of the SQL Server pmda collector that performs (background collection, dynamic counter discovery and even streams some DMV queries…) Since I am not a pmda expert and I am not sure when I would be able to complete the experiment, I am providing the code (AS IS, Experimental Only) in case anyone else cares to leverage the concepts and continue to enhance the SQL Server pmda collector.
#!/usr/bin/env pmpython
”’
Python PMDA providing Microsoft SQL Server performance counters.
”’
#
# *** WARNING: This is AS-IS, Experimental Only. ***
#
# You are free to use the concepts and examples to build
# and test a production level script.
#
# LOG FILE: cat /var/log/pcp/pmcd/MSSQL.log
# cat /var/log/pcp/pmcd/pmcd.log
# CONF FILE: $PCP_PMDAS_DIR/mssql/mssql.conf
# NAMESPACE: cat /var/lib/pcp/pmns/root
#
import configparser as ConfigParser
import cpmapi as c_api
import ctypes
import json
import os
import platform
import pyodbc
import re
import sys
import threading
import time
import traceback
from collections import OrderedDict
from ctypes import c_int, pointer, cast
from enum import Enum
from pcp.pmapi import pmUnits, pmContext as PCP
from pcp.pmda import PMDA, pmdaMetric
####################################
# SafeString
#
def SafeString(obj):
if obj is None:
return ”
elif type(”) != type(obj):
try:
output = obj.decode(“utf-8”)
except:
output = ‘ecvt’
return output
else:
return obj
#========================================================
# MicrosoftSQLServerMetricKey
#========================================================
class MicrosoftSQLServerMetricKey:
def __init__(self, cluster, item):
self.cluster = cluster
self.item = item
def GetCluster(self):
return self.cluster
def GetItem(self):
return self.item
def ToString(self):
return str(self.cluster) + ‘:’ + str(self.item)
#========================================================
# MetricType
#========================================================
class MetricType(Enum):
Thumbprint = 1
PerfCounter = 2
DMV = 3
#========================================================
# MetricClear
#========================================================
class MetricClear(Enum):
Yes = True
No = False
#========================================================
# MicrosoftSQLServerMetric
#========================================================
class MicrosoftSQLServerMetric:
def __init__(self, cluster, item, value, name, onelineHelp, metricObject, clearOnFetch, metricType):
self.key = MicrosoftSQLServerMetricKey(cluster, item)
self.value = value
self.name = name
self.onelineHelp = onelineHelp
self.metricObject = metricObject
self.clearOnFetch = clearOnFetch
self.metricType = metricType
def GetKey(self):
return self.key
def GetValue(self):
return self.value
def GetName(self):
return self.name
def GetOnelineHelp(self):
return self.onelineHelp
def GetObject(self):
return self.metricObject
def ClearOnFetch(self):
return self.clearOnFetch
def ClearValue(self):
self.value = None
def GetMetricType(self):
return self.metricType
#========================================================
# MicrosoftSQLServerMetrics
#========================================================
class MicrosoftSQLServerMetrics:
def __init__(self, parent, baseClusterId):
self.parent = parent
self.baseClusterId = baseClusterId
self.metrics = OrderedDict()
self.collectorEvent = threading.Event();
#——————————————
# SignalCollector
#
def SignalCollector(self):
self.collectorEvent.set()
#——————————————
# WaitForCollectorSignal
#
def WaitForCollectorSignal(self):
self.collectorEvent.wait()
#——————————————
# ResetCollectorSignal
#
def ResetCollectorSignal(self):
self.collectorEvent.clear()
#——————————————
# ScrubName
#
def ScrubName(self, name):
return re.sub(‘[^a-z0-9_]’, ‘_’, name.rstrip().lower())
#——————————————
# ConvertCounterTypeToSemantics
#
def ConvertCounterTypeToSemantics(self, type):
# Fraction
if type == 1073939712:
return c_api.PM_SEM_INSTANT
# Raw count
if type == 65792:
return c_api.PM_SEM_INSTANT
# All others
#
return c_api.PM_SEM_COUNTER
#——————————————
# ConvertTypeToUnits
#
# Reference: https://docs.microsoft.com/en-us/windows/desktop/WmiSdk/wmi-performance-counter-types
#
# PMDA: pmUnits (dimSpace, dimTime, dimCount, scaleSpace, scaleTime, scaleCount)
#
def ConvertCounterTypeToUnits(self, type):
# Sec Avg
if type == 272696320:
return pmUnits(0, 0, 0, 0, c_api.PM_TIME_SEC, c_api.PM_COUNT_ONE)
# Sec Avg Big
if type == 272696576:
return pmUnits(0, 0, 0, 0, c_api.PM_TIME_SEC, c_api.PM_COUNT_ONE)
# Fraction
if type == 1073939712:
return pmUnits(0, 0, 0, 0, 0, c_api.PM_COUNT_ONE)
# MS Avg
if type == 1073874176:
return pmUnits(0, 0, 0, 0, c_api.PM_TIME_MSEC, c_api.PM_COUNT_ONE)
# Percentage over time
if type == 537003264:
return pmUnits(0, 0, 0, 0, c_api.PM_TIME_SEC, c_api.PM_COUNT_ONE)
# Raw count
if type == 65792:
return pmUnits(0, 0, 0, 0, 0, c_api.PM_COUNT_ONE)
# All others
#
return pmUnits(0, 0, 0, 0, 0, c_api.PM_COUNT_ONE)
#——————————————
# Add
#
def Add(self, metric):
self.metrics[metric.GetKey().ToString()] = metric
#——————————————
# GetMetric
#
def GetMetric(self, cluster, item):
key = MicrosoftSQLServerMetricKey(cluster, item).ToString()
metric = self.metrics[key]
return metric;
#——————————————
# RemoveMetric
#
def RemoveMetric(self, metric):
self.parent.LogVerbose(‘Removing metric: %s’ % metric.GetName())
self.metrics.pop(metric.GetKey().ToString())
#========================================================
# PerfCounters
#========================================================
class PerfCounters(MicrosoftSQLServerMetrics):
#——————————————
# __init__
#
def __init__(self, parent, baseClusterId):
MicrosoftSQLServerMetrics.__init__(self, parent, baseClusterId)
#——————————————
# Collect
#
def Collect(self):
self.parent.LogVerbose(‘Collecting performance counters’)
sqlConn = pyodbc.connect(self.parent.connectionString, autocommit=True)
cluster = self.baseClusterId
item = 0
instance = c_api.PM_INDOM_NULL
clusterName = ”
# Ordered select so we can use simple control break cluster id processing for pmid assignment
#
crsr = sqlConn.cursor()
self.parent.ExecuteCursor(crsr, “select object_name, counter_name, isnull(
case
when upper(instance_name) = upper( isnull(cast(serverproperty(‘InstanceName’) as sysname), ‘MSSQLSERVER’)) then ”
else instance_name
end, ”) as [instance_name],
cntr_value, cntr_type
from sys.dm_os_performance_counters
order by object_name, counter_name, instance_name”)
rows = crsr.fetchall()
# Process each entry
#
for row in rows:
name = ‘MSSQL.’ + self.ScrubName(row.object_name) + ‘.’ + self.ScrubName(row.counter_name)
if clusterName != name:
cluster = cluster + 1
item = 0
clusterName = name
instance = self.ScrubName(row.instance_name)
if len(instance) != 0:
name += ‘.’ + instance
self.parent.LogVerbose(“Collected metric: %s:%s %s Value: %s” % (cluster, item, name, row.cntr_value))
pmid = self.parent.pmid(cluster, item)
obj = pmdaMetric(pmid,
c_api.PM_TYPE_64,
c_api.PM_INDOM_NULL,
self.ConvertCounterTypeToSemantics(row.cntr_type),
self.ConvertCounterTypeToUnits(row.cntr_type))
self.Add(MicrosoftSQLServerMetric(cluster, item, row.cntr_value, name, name + ‘ ‘ + row.counter_name, obj, MetricClear.Yes, MetricType.PerfCounter))
item = item + 1
crsr.close()
sqlConn.close()
self.parent.SetPerfCounters(self)
#========================================================
# DmvEvents
#========================================================
class DmvEvents(MicrosoftSQLServerMetrics):
#——————————————
# __init__
#
def __init__(self, parent, baseClusterId):
MicrosoftSQLServerMetrics.__init__(self, parent, baseClusterId)
self.dmvs = [
[‘serverproperty’, “select
cast(isnull(serverproperty(‘BuildClrVersion’), ”) as sysname) as [BuildClrVersion],
cast(isnull(serverproperty(‘Collation’), ”) as sysname) as Collation,
cast(isnull(serverproperty(‘CollationID’), ”) as sysname) as CollationID,
cast(isnull(serverproperty(‘ComparisonStyle’), ”) as sysname) as ComparisonStyle,
cast(isnull(serverproperty(‘ComputerNamePhysicalNetBIOS’), ”) as sysname) as ComputerNamePhysicalNetBIOS,
cast(isnull(serverproperty(‘Edition’), ”) as sysname) as [BuildClrVersion],
cast(isnull(serverproperty(‘EditionID’), ”) as sysname) as Edition,
cast(isnull(serverproperty(‘EngineEdition’), ”) as sysname) as EngineEdition,
cast(isnull(serverproperty(‘HadrManagerStatus’), ”) as sysname) as HadrManagerStatus,
cast(isnull(serverproperty(‘InstanceDefaultDataPath’), ”) as sysname) as InstanceDefaultDataPath,
cast(isnull(serverproperty(‘InstanceDefaultLogPath’), ”) as sysname) as HadrManagerStatus,
cast(isnull(serverproperty(‘InstanceName’), ”) as sysname) as InstanceName,
cast(isnull(serverproperty(‘IsAdvancedAnalyticsInstalled’), ”) as sysname) as IsAdvancedAnalyticsInstalled,
cast(isnull(serverproperty(‘IsClustered’), ”) as sysname) as IsClustered,
cast(isnull(serverproperty(‘IsFullTextInstalled’), ”) as sysname) as IsFullTextInstalled,
cast(isnull(serverproperty(‘IsHadrEnabled’), ”) as sysname) as IsHadrEnabled,
cast(isnull(serverproperty(‘IsIntegratedSecurityOnly’), ”) as sysname) as IsIntegratedSecurityOnly,
cast(isnull(serverproperty(‘IsLocalDB’), ”) as sysname) as IsLocalDB,
cast(isnull(serverproperty(‘IsPolyBaseInstalled’), ”) as sysname) as IsPolyBaseInstalled,
cast(isnull(serverproperty(‘IsSingleUser’), ”) as sysname) as IsSingleUser,
cast(isnull(serverproperty(‘IsXTPSupported’), ”) as sysname) as IsXTPSupported,
cast(isnull(serverproperty(‘LCID’), ”) as sysname) as LCID,
cast(isnull(serverproperty(‘LicenseType’), ”) as sysname) as LicenseType,
cast(isnull(serverproperty(‘MachineName’), ”) as sysname) as MachineName,
cast(isnull(serverproperty(‘NumLicenses’), ”) as sysname) as NumLicenses,
cast(isnull(serverproperty(‘ProcessID’), ”) as sysname) as ProcessID,
cast(isnull(serverproperty(‘ProductBuild’), ”) as sysname) as ProductBuild,
cast(isnull(serverproperty(‘ProductLevel’), ”) as sysname) as ProductLevel,
cast(isnull(serverproperty(‘ProductMajorVersion’), ”) as sysname) as ProductMajorVersion,
cast(isnull(serverproperty(‘ProductMinorVersion’), ”) as sysname) as ProductMinorVersion,
cast(isnull(serverproperty(‘ProductUpdateLevel’), ”) as sysname) as ProductUpdateLevel,
cast(isnull(serverproperty(‘ProductUpdateReference’), ”) as sysname) as ProductUpdateReference,
cast(isnull(serverproperty(‘ProductVersion’), ”) as sysname) as ProductVersion,
cast(isnull(serverproperty(‘ResourceLastUpdateDateTime’), ”) as sysname) as ResourceLastUpdateDateTime,
cast(isnull(serverproperty(‘ResourceVersion’), ”) as sysname) as ResourceVersion,
cast(isnull(serverproperty(‘ServerName’), ”) as sysname) as ServerName,
cast(isnull(serverproperty(‘SqlCharSet’), ”) as sysname) as SqlCharSet,
cast(isnull(serverproperty(‘SqlCharSetName’), ”) as sysname) as SqlCharSetName,
cast(isnull(serverproperty(‘SqlSortOrder’), ”) as sysname) as SqlSortOrder,
cast(isnull(serverproperty(‘SqlSortOrderName’), ”) as sysname) as SqlSortOrderName,
cast(isnull(serverproperty(‘FilestreamShareName’), ”) as sysname) as FilestreamShareName,
cast(isnull(serverproperty(‘FilestreamConfiguredLevel’), ”) as sysname) as FilestreamConfiguredLevel,
cast(isnull(serverproperty(‘FilestreamEffectiveLevel’), ”) as sysname) as FilestreamEffectiveLevel,
cast(isnull(serverproperty(‘SqlSortOrderName’), ”) as sysname) as SqlSortOrderName,
cast(isnull(serverproperty(‘SqlSortOrderName’), ”) as sysname) as SqlSortOrderName”],
[‘sys.dm_os_host_info’, None],
[‘sys.dm_os_windows_info’, None],
[‘sys.dm_linux_proc_cpuinfo’, “if object_id(‘sys.dm_linux_proc_cpuinfo’) is not null select * from sys.dm_linux_proc_cpuinfo; else select ‘Not Found’ “],
[‘sys.dm_linux_proc_all_stat’, “if object_id(‘sys.dm_linux_proc_all_stat’) is not null select * from sys.dm_linux_proc_all_stat; else select ‘Not Found’ “],
[‘sys.dm_linux_proc_sql_threads’, “if object_id(‘sys.dm_linux_proc_sql_threads’) is not null select * from sys.dm_linux_proc_sql_threads; else select ‘Not Found’ “],
[‘sys.dm_linux_proc_meminfo’, “if object_id(‘sys.dm_linux_proc_meminfo’) is not null select * from sys.dm_linux_proc_meminfo; else select ‘Not Found’ “],
[‘sys.dm_linux_proc_sql_maps’, “if object_id(‘sys.dm_linux_proc_sql_maps’) is not null select * from sys.dm_linux_proc_sql_maps; else select ‘Not Found’ “],
[‘sys.dm_os_virtual_address_dump’, None],
[‘sys.dm_exec_connections’, None],
[‘sys.dm_exec_sessions’, None],
[‘sys.dm_exec_requests’, None],
[‘sys.dm_os_sys_info’, None],
[‘sys.dm_os_ring_buffers’, None],
[‘sys.databases’, None],
[‘sys.dm_os_volume_stats’, “SELECT f.database_id, f.file_id, volume_mount_point, total_bytes, available_bytes
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)”],
[‘sys.dm_os_loaded_modules’, None],
[‘sys.dm_os_schedulers’, None],
[‘sys.dm_os_dispatcher_pools’, None],
[‘sys.dm_os_threads’, None],
[‘sys.dm_os_workers’, None],
[‘sys.dm_os_tasks’, None],
[‘sys.dm_os_sublatches’, None],
[‘sys.dm_os_wait_stats’, None],
[‘sys.dm_os_spinlock_stats’, None],
[‘sys.dm_os_nodes’, None],
[‘sys.dm_os_latch_stats’, None],
[‘sys.dm_os_enumerate_fixed_drives’, None],
[‘sys.dm_server_registry’, None],
[‘sys.dm_server_services’, None],
[‘sys.dm_server_memory_dumps’, None],
[‘sys.dm_server_audit_status’, None],
[‘run_levels’, “select cast(@@VERSION as varchar(256)),
cast(serverproperty(‘Edition’) as sysname),
ISNULL(cast(serverproperty(‘ComputerNamePhysicalNetBIOS’) as sysname), @@SERVERNAME),
cast(serverproperty(‘ProductVersion’) as varchar(20)),
dateadd(mi, datediff(mi, GetDate(), GetUTCDate()), sqlserver_start_time) as ‘sqlserver_start_time’,
datediff(hh, sqlserver_start_time, GetDate()),
cpu_count,
committed_kb,
committed_target_kb,
(select count(*) from sys.dm_os_workers) as ‘current_workers’,
max_workers_count,
(select sum(quantum_used) from sys.dm_os_workers) as ‘quantum_used’,
process_kernel_time_ms,
process_user_time_ms,
virtual_machine_type_desc,
(select count(*) from sys.dm_exec_requests) as ‘active_requests’,
(select count(*) from sys.dm_exec_sessions) as ‘sessions’
from sys.dm_os_sys_info”],
[‘session_status’, “select program_name, status, count(*) as ‘SessionCount’ from sys.dm_exec_sessions group by program_name, status”],
[‘encryption_state’, “select f.name, f.database_id,
encryption_state_desc =
CASE k.encryption_state
WHEN 0 THEN ‘No database encryption key present, no encryption’
WHEN 1 THEN ‘Unencrypted’
WHEN 2 THEN ‘Encryption in progress’
WHEN 3 THEN ‘Encrypted’
WHEN 4 THEN ‘Key change in progress’
WHEN 5 THEN ‘Decryption in progress’
WHEN 6 THEN ‘Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed’
ELSE ‘No Status’
END,
k.percent_complete,
f.file_id, f.type, f.physical_name, f.state, f.state_desc, f.size, f.max_size, f.growth, f.is_read_only, f.is_sparse, f.is_percent_growth,
k.key_algorithm, k.encryptor_type
from master.sys.master_files f
left join master.sys.dm_database_encryption_keys k on k.database_id = f.database_id”],
[‘sys.dm_os_cluster_nodes’, None],
[‘sys.dm_os_cluster_properties’, None],
[‘sys.dm_os_process_memory’, None],
[‘sys.dm_os_memory_pools’, None],
[‘sys.dm_os_memory_cache_counters’, None],
[‘sys.dm_os_memory_clerks’, None],
[‘sys.dm_os_memory_broker_clerks’, None],
[‘sys.dm_os_memory_cache_clock_hands’, None],
[‘sys.dm_os_memory_objects’, None],
[‘sys.dm_exec_query_resource_semaphores’, None],
[‘sys.dm_resource_governor_configuration’, None],
[‘sys.dm_resource_governor_external_resource_pool_affinity’, None],
[‘sys.dm_resource_governor_external_resource_pools’, None],
[‘sys.dm_resource_governor_resource_pool_affinity’, None],
[‘sys.dm_resource_governor_resource_pool_volumes’, None],
[‘sys.dm_resource_governor_resource_pools’, None],
[‘sys.dm_resource_governor_workload_groups’, None],
[‘sys.resource_governor_configuration’, None],
[‘sys.resource_governor_external_resource_pool_affinity’, None],
[‘sys.resource_governor_external_resource_pools’, None],
[‘sys.resource_governor_resource_pool_affinity’, None],
[‘sys.resource_governor_resource_pools’, None],
[‘sys.resource_governor_workload_groups’, None],
[‘sys.traces’, None],
[‘sys.server_event_sessions’, None],
[‘sys.dm_xe_sessions’, None],
[‘sys.dm_xe_session_targets’, None],
[‘xe_ring_buffer_sessions’, “select s.name, target_data
from sys.dm_xe_session_targets t
inner join sys.dm_xe_sessions s on t.event_session_address = s.address and t.target_name in (‘ring_buffer’, ‘router’) and target_data is not null
“],
[‘sys.sp_server_diagnostics’, ‘set nocount on; exec sys.sp_server_diagnostics’],
[‘sys.sp_replcounters’, ‘exec sys.sp_replcounters’],
[‘sys.sp_distcounters’, ‘exec sys.sp_distcounters’],
[‘sys.dm_external_script_execution_stats’, None],
[‘sys.dm_db_script_level’, None],
[‘sys.servers’, None],
[‘sys.sp_monitor’, ‘set nocount on; exec sys.sp_monitor’]
]
# DEBUG: Use for debugging
#
# self.dmvs = [ [‘sys.sp_server_diagnostics’, ‘set nocount on; set rowcount 1; exec sys.sp_server_diagnostics’] ]
#——————————————
# DMVEntries
#
# We may not be able to collect each entry but we expose
# all entries in the MSSQL namespace.
#
def DMVEntries(self):
return len(self.dmvs) / 2
#——————————————
# CollectDmvData
#
def CollectDmvData(self, sqlConn, dmvDisplay, dmvQuery):
self.parent.LogVerbose(‘Collecting DMVs for %s’ % dmvDisplay)
dataDict = {}
metadataDict = {}
# Type of sql_variant is not avail in pyodbc so always convert to JSON to be safe
#
if dmvQuery is None:
dmvQuery = “select * from ” + dmvDisplay + ” FOR JSON PATH”
crsr = sqlConn.cursor()
try:
self.parent.ExecuteCursor(crsr, dmvQuery)
data = crsr.fetchall()
# Description: list of 7-item tuples, each containing (name, type_code, display_size, internal_size, precision, scale, null_ok).
# pyodbc only provides values for name, type_code, internal_size, and null_ok. The other values are set to None.
#
for columnInfo in crsr.description:
metadataDict[columnInfo[0]] = columnInfo[1].__name__
dataDict = [dict((crsr.description[i][0], SafeString(str(value))) for i, value in enumerate(row)) for row in data]
tableDict = {}
tableDict[‘metadata’] = metadataDict
tableDict[‘data’] = dataDict
except Exception as e:
self.parent.err(‘Attempt to execute: %s failed with error [%s]. Depending on target this may be expected.’ % (dmvDisplay, e))
self.parent.LoggedWrapped(traceback.format_exc())
tableDict = {}
crsr.close()
return tableDict
#——————————————
# Collect
#
def Collect(self):
self.parent.LogVerbose(‘Collecting DMVs’)
sqlConn = pyodbc.connect(self.parent.connectionString, autocommit=True)
cluster = cluster = self.baseClusterId
item = 0
for dmv in self.dmvs:
name = ‘MSSQL.dmv.’ + self.ScrubName(dmv[0])
dmvDisplay = dmv[0]
dmvQuery = dmv[1]
self.parent.LogVerbose(“Collecting metric: %s:%s:0 %s” % (cluster, item, name))
obj = pmdaMetric(self.parent.pmid(cluster, item),
c_api.PM_TYPE_STRING,
c_api.PM_INDOM_NULL,
c_api.PM_SEM_INSTANT,
pmUnits(0, 0, 0, 0, 0, 0))
if self.parent.includeData == True:
tableDict = self.CollectDmvData(sqlConn, dmvDisplay, dmvQuery)
dmvJson = json.dumps(tableDict, sort_keys=False, ensure_ascii=False, default=str)
else:
dmvJson = None
self.Add(MicrosoftSQLServerMetric(cluster, item, dmvJson, name, ‘Collected information from: ‘ + dmvDisplay, obj, MetricClear.Yes, MetricType.DMV))
item = item + 1
sqlConn.close()
self.parent.SetDmvEvents(self)
#========================================================
# Thumbprint for tracking similiar systems/containers/etc.
#
# Thumbprint System Only – just basic OS info
# Thumbprint System + Architecture
# Thumbprint Full – Include System + Architecture + Machine + CPUs + Memory
#
#========================================================
class Thumbprint(MicrosoftSQLServerMetrics):
#——————————————
# __init__
#
def __init__(self, parent, baseClusterId):
MicrosoftSQLServerMetrics.__init__(self, parent, baseClusterId)
#——————————————
# Collect
#
def Collect(self):
self.parent.LogVerbose(‘Collecting thumbprint’)
sqlConn = pyodbc.connect(self.parent.connectionString, autocommit=True)
system = 0
name = 0
node = 1
version = 1
release = 2
id = 2
machine = 4
processor = 5
# Reference os.sysconf_names
#
info = {}
info[‘system’] = platform.uname()[system].lower()
info[‘distribution’] = {“name”: platform.dist()[name].lower(), “version”: platform.dist()[version].lower(), “id”: platform.dist()[id].lower() }
info[‘machine’] = platform.uname()[machine].lower()
info[‘processor’] = platform.uname()[processor].lower()
info[‘byteorder’] = sys.byteorder.lower()
info[‘physicalmemory (bytes)’] = os.sysconf(‘SC_PAGE_SIZE’) * os.sysconf(‘SC_PHYS_PAGES’)
info[‘cpucount’] = os.sysconf(‘SC_NPROCESSORS_ONLN’)
crsr = sqlConn.cursor()
query = “select cpu_count, scheduler_count, physical_memory_kb, max_workers_count, sql_memory_model_desc, virtual_machine_type_desc,
cast(serverproperty(‘ProductMajorVersion’) as sysname) as ProductMajorVersion,
cast(serverproperty(‘ProductMinorVersion’) as sysname) as ProductMinorVersion,
cast(serverproperty(‘ProductUpdateLevel’) as sysname) as ProductUpdateLevel
from sys.dm_os_sys_info”
self.parent.ExecuteCursor(crsr, query)
rows = crsr.fetchall()
info[‘sqltarget’] = { ‘cpucount’: rows[0][0],
‘scheduler_count’: rows[0][1],
‘physical_memory_kb’: rows[0][2],
‘max_workers_count’: rows[0][3],
‘sql_memory_model_desc’: rows[0][4].lower(),
‘virtual_machine_type_desc’: rows[0][5].lower(),
}
crsr.close();
sqlConn.close()
value = json.dumps({“evtsrc”: info},
indent=None,
sort_keys=True)
obj = pmdaMetric(self.parent.pmid(self.baseClusterId, 0),
c_api.PM_TYPE_STRING,
c_api.PM_INDOM_NULL,
c_api.PM_SEM_INSTANT,
pmUnits(0, 0, 0, 0, 0, 0))
self.Add(MicrosoftSQLServerMetric(self.baseClusterId, 0, value, ‘MSSQL.thumbprint’, ‘MSSQL collection thumbprint’, obj, MetricClear.No, MetricType.Thumbprint))
#========================================================
# MicrosoftSQLServerPMDA
#========================================================
class MicrosoftSQLServerPMDA(PMDA):
”’
PMDA rendering the Microsoft SQL Server performance counters
Reference: sys.dm_os_performance_counters
”’
#——————————————
# SetDmvEvents
#
def SetDmvEvents(self, dmvEvents):
self.LogVerbose(‘Set dmv events %s’ % dmvEvents)
self.LockMetrics()
self.dmvEvents = dmvEvents
self.UnlockMetrics()
#——————————————
# SetPerfCounters
#
def SetPerfCounters(self, perfCounters):
self.LogVerbose(‘Set performance counters %s’ % perfCounters)
self.LockMetrics()
self.perfCounters = perfCounters
self.UnlockMetrics()
#——————————————
# ReplaceMetrics
#
def ReplaceMetrics(self, newDict, metricType):
self.LogVerbose(‘>>>>> ENTERING: InsertMetrics %s’ % metricType)
toRemove = []
# Remove previous references for the type
#
for key, metric in self.exposedMetrics.metrics.items():
self.log(‘———— %s %s’ % (key, metric))
if metric.GetMetricType() == metricType:
toRemove.append(metric)
for metric in toRemove:
self.exposedMetrics.RemoveMetric(metric)
# Append new entries
#
for key, metric in newDict.items():
self.exposedMetrics.Add(metric)
self.LogVerbose(‘<<<<< EXITING: InsertMetrics %s’ % metricType)
#——————————————
# DumpExposedMetrics
#
def DumpExposedMetrics(self):
if self.verboseInternal == True:
self.LogVerbose(‘tEXPOSED METRIC NAMESPACE’)
self.LogVerbose(‘t————————‘)
for key, metric in self.exposedMetrics.metrics.items():
self.LogVerbose(‘tMETRIC: %s (%s) %s Value: %s’ % (key, metric.GetKey().ToString(), metric.GetName(), str(metric.GetValue())[:64]))
#——————————————
# FetchMetrics
#
# Fetch is called on the timer interval. For example if you
# set the timer to 5 seconds in pmchart fetch is called on
# 5 second intervals. If the timer is set to .25 seconds
# fetch is called 4 times per second. The timer can be
# dynamically adjusted in pmchart.
#
# Note: Caller to fetch uses 5 second timeout and if fetch
# takes longer than 5 seconds the pduread is terminated from
# the pmcd collection
#
def FetchMetrics(self):
self.LogVerbose(‘>>>>> ENTERING: fetch_callback::GetMetric’)
self.LockMetrics()
try:
self.LogVerbose(‘tChecking for thumbprint metrics’)
self.ReplaceMetrics(self.thumbprint.metrics, MetricType.Thumbprint)
self.LogVerbose(‘tChecking for performance counter changes’)
if self.perfCounters is not None:
self.ReplaceMetrics(self.perfCounters.metrics, MetricType.PerfCounter)
self.perfCounters.SignalCollector()
self.perfCounters = None
self.LogVerbose(‘tChecking for dmv changes’)
if self.dmvEvents is not None:
self.ReplaceMetrics(self.dmvEvents.metrics, MetricType.DMV)
self.dmvEvents.SignalCollector()
self.dmvEvents = None
self.LogVerbose(‘tExposing updated MSSQL namespace metrics and events’)
self.clear_metrics() # Make sure no longer exposed by to PMCD
# Add all metrics to match that of tracked metrics
#
for key, metric in self.exposedMetrics.metrics.items():
self.LogVerbose(‘ttEXPOSING IN MSSQL NAMESPACE: %s’ % metric.GetName())
self.add_metric(metric.GetName(), metric.GetObject(), metric.GetOnelineHelp(), metric.GetName())
except Exception as e:
self.err(‘FetchMetrics failed with error [%s]’ % (e))
self.LoggedWrapped(traceback.format_exc())
finally:
self.UnlockMetrics()
self.LogVerbose(‘<<<<< EXITING: fetch_callback::GetMetric’)
return 1
#——————————————
# GetMetricValue
#
def GetMetricValue(self, cluster, item, inst):
self.LogVerbose(‘>>>>> ENTERING: fetch_callback::GetMetricValue %s:%s:%s’ % (cluster, item, inst))
returnValue = None
try:
# Make sure to only try as a full wait could deadlock
# the background thread attemping to update the PMSN
# from a async fetach invocation. This should be a very
# small window.
#
metric = None
if self.LockMetrics(False) == True: # No wait
metric = self.exposedMetrics.GetMetric(cluster, item)
self.UnlockMetrics()
if metric is None:
self.LogVerbose(‘(%s:%s:%s) fetch_callback metric error – not found’ % (cluster, item, inst))
returnValue = [c_api.PM_ERR_PMID, 0]
else:
value = metric.GetValue()
if value is not None:
self.LogVerbose(‘Returning %s fetch_callback value: %s…’ % (metric.GetName(), str(value)[:64]))
returnValue = [value, 1]
# Save some memory and streaming from DMVs and such
#
if metric.ClearOnFetch() == MetricClear.Yes:
metric.ClearValue()
#else:
# self.LogVerbose(‘Returning %s fetch_callback: PM_ERR_AGAIN’ % metric.GetName())
# returnValue = [c_api.PM_ERR_AGAIN, 0]
except Exception as e:
self.err(‘GetMetricValue (%s:%s:%s) failed with error [%s]’ % (cluster, item, inst, e))
self.LoggedWrapped(traceback.format_exc())
returnValue = [c_api.PM_ERR_PMID, 0]
self.LogVerbose(‘<<<<< EXITING: fetch_callback::GetMetricValue’)
return returnValue
#——————————————
# ReadConfSettings
#
def ReadConfSettings(self):
confFileName = PCP.pmGetConfig(‘PCP_PMDAS_DIR’)
confFileName += ‘/’ + self.read_name() + ‘/’ + self.read_name() + ‘.conf’
self.log(‘Reading configuration settings from: %s’ % confFileName)
config = ConfigParser.SafeConfigParser()
config.read(confFileName)
if config.has_section(‘logging’) == True:
for opt in config.options(‘logging’):
if opt == ‘verbose’:
self.verbose = config.getboolean(‘logging’, opt)
# “ODBC Driver 17 for SQL Server”,
# “SQL Server Native Client 11.0”
# “SQL Server Native Client 10.0”,
# “SQL Server”
#
# DRIVER={ODBC Driver 17 for SQL Server};SERVER=.;Connection Timeout=30;
#
self.connectionString = ‘DRIVER={ODBC Driver 17 for SQL Server};SERVER=.;Connection Timeout=30;’
if config.has_section(‘mssql’) == True:
for opt in config.options(‘mssql’):
if opt == ‘connectionstring’:
self.connectionString = config.get(‘mssql’, opt)
self.log(‘Connection string %s’ % self.connectionString)
# TODO: Lookup user and password from a key vault / secure location
# or use integrated security capabilities (Trusted Connection=true)
#
self.connectionString += “;UID=” + self.GetSecureUserId()
self.connectionString += “;PWD=” + self.GetSecurePassword()
#——————————————
# StartPCPXESession
#
def StartPCPXESession(self):
self.log(‘Starting PCPXESession on target server’)
sessionDef = “if((select count(*) from sys.server_event_sessions where name = ‘PCPEvents’) > 0)
begin
DROP EVENT SESSION [PCPEvents] ON SERVER
end
CREATE EVENT SESSION [PCPEvents] ON SERVER
ADD EVENT sqlserver.blocked_process_report(
ACTION(package0.event_sequence,sqlos.system_thread_id,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.session_id,sqlserver.transaction_id,sqlserver.username)),
ADD EVENT sqlserver.dbcc_checkdb_error_reported(
ACTION(package0.event_sequence,sqlos.system_thread_id,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.session_id,sqlserver.transaction_id,sqlserver.username)),
ADD EVENT sqlserver.error_reported(
ACTION(package0.event_sequence,sqlos.system_thread_id,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.session_id,sqlserver.transaction_id,sqlserver.username)),
ADD EVENT sqlserver.lock_deadlock(
ACTION(package0.event_sequence,sqlos.system_thread_id,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.session_id,sqlserver.transaction_id,sqlserver.username)),
ADD EVENT sqlserver.query_memory_grant_blocking(
ACTION(package0.event_sequence,sqlos.system_thread_id,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.session_id,sqlserver.transaction_id,sqlserver.username))
ADD TARGET package0.ring_buffer(SET max_events_limit=(0),max_memory=(655360))
WITH (MAX_MEMORY=1024000 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=5 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=PER_CPU, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF)
ALTER EVENT SESSION [PCPEvents] ON SERVER STATE = START”
sqlConn = pyodbc.connect(self.connectionString, autocommit=True)
crsr = sqlConn.cursor()
self.ExecuteCursor(crsr, sessionDef)
crsr.close()
#——————————————
# LoggedWrapped
#
def LoggedWrapped(self, value):
pos = 0
size = len(value)
chunkSize = 128
while pos < size:
self.log(‘%s’ % value[pos:pos+chunkSize])
pos += chunkSize
#——————————————
# ExecuteCursor
#
def ExecuteCursor(self, cursor, query):
if self.verboseInternal == True:
self.LogVerbose(‘Executing Query’)
self.LogVerbose(‘———————————— START’)
self.LoggedWrapped(query)
self.LogVerbose(‘———————————— END’)
cursor.execute(query)
#——————————————
# Add verbose log entry
#
def LogVerbose(self, message, *args):
if self.verboseInternal == True:
self.log(message % args)
#——————————————
# LockMetrics
#pytghon
def LockMetrics(self, wait=True):
self.LogVerbose(‘tLOCKING: %s’ % self.metricCacheLockInternal)
result = self.metricCacheLockInternal.acquire(wait)
return result
#——————————————
# UnlockMetrics
#
def UnlockMetrics(self):
self.LogVerbose(‘tUNLOCKING: %s’ % self.metricCacheLockInternal)
self.metricCacheLockInternal.release()
#——————————————
# BackgroundCollector
#
def BackgroundCollector(self, metrics, delay):
self.LogVerbose(‘>>>> ENTERING: Background collector %s’ % threading.current_thread().name)
while self.stop == False:
self.LogVerbose(‘Waiting for collection signal: %s’ % threading.current_thread().name)
metrics.WaitForCollectorSignal()
metrics.ResetCollectorSignal()
if self.stop == False:
metrics.Collect()
if delay:
self.LogVerbose(‘Delaying before execution: %s’ % threading.current_thread().name)
for loop in range(0, delay):
if self.stop == False:
time.sleep(1)
self.LogVerbose(‘<<<<< EXITING: Background collector %s’ % threading.current_thread().name)
#——————————————
# GetSecureUserId
#
# User should have limited access to SQL Server to view
# the DMV only and not do other activities.
#
# NOTE: Establish your keyvault activity here
#
def GetSecureUserId(self):
return ‘sa’
#——————————————
# GetSecurePassword
#
# NOTE: Establish your keyvault activity here
#
def GetSecurePassword(self):
return ‘Yukon900’
#——————————————
# __del__
#
def __del__(self):
self.log(‘>>>>> ENTERING: MicrosoftSQLServerPMDA::__del__’)
self.log(‘<<<< EXITING: MicrosoftSQLServerPMDA::__del__’)
#——————————————
# __init__
#
def __init__(self, name, domain):
PMDA.__init__(self, name, domain)
self.log(‘>>>>> ENTERING: MicrosoftSQLServerPMDA::__init__’)
self.pmda_notready()
# Do minimal work to get MSSQL PMDA up and running and avoid
# 5 second timeouts from the infrastructure
#
self.includeData = False
self.stop = False
self.verboseInternal = True
self.ReadConfSettings()
self.StartPCPXESession()
self.metricCacheLockInternal = threading.Lock()
clusterId = 0
self.exposedMetrics = MicrosoftSQLServerMetrics(self, clusterId)
# One time collection, no background collector needed
#
clusterId = 1
self.thumbprint = Thumbprint(self, clusterId)
self.thumbprint.Collect()
self.dmvEvents = None
clusterId = 2
self.dmvEventsCollector = DmvEvents(self, clusterId)
self.dmvEventsCollector.Collect()
# Always last as it uses dynamic set of counters so the cluster id
# has to be determined from previous, static metrics
#
self.perfCounters = None
clusterId = int(2 + self.dmvEventsCollector.DMVEntries())
self.perfCountersCollector = PerfCounters(self, clusterId)
self.perfCountersCollector.Collect()
# Make everything available / register with PCMD (pminfo, …)
#
self.FetchMetrics()
# Callers can now trigger full data for metrics and events
#
self.includeData = True
# DEBUGGING: Easy way to test callbacks from ./Install
#
”’
self.DumpExposedMetrics()
self.dmvEventsCollector.Collect()
self.perfCountersCollector.Collect()
self.FetchMetrics()
self.DumpExposedMetrics()
self.GetMetricValue(3, 0, 0)
self.DumpExposedMetrics()
”’
# Fetch is called to reload counters and then
# fetch callback to get individual values
#
self.set_fetch(self.FetchMetrics)
self.set_fetch_callback(self.GetMetricValue)
self.perfCountersCollector.SignalCollector()
self.perfCounterThread = threading.Thread(None, self.BackgroundCollector, ‘PerfCounters’, [self.perfCountersCollector, 0])
self.perfCounterThread.start()
self.dmvEventsCollector.SignalCollector()
self.dmvEventsThread = threading.Thread(None, self.BackgroundCollector, ‘DMV Events’, [self.dmvEventsCollector, 10])
self.dmvEventsThread.start()
self.log(‘<<<<< EXITING: MicrosoftSQLServerPMDA::__init__’)
#——————————————
# Stop
#
def Stop(self):
self.LogVerbose(‘Signalling stop’)
self.includeData = False
self.stop = True
self.perfCountersCollector.SignalCollector()
self.dmvEventsCollector.SignalCollector()
self.LogVerbose(‘Wait for [performance counter thread] to stop’)
self.perfCounterThread.join()
self.perfCounterThread = None
self.LogVerbose(‘Wait for [dmv events thread] to stop’)
self.dmvEventsThread.join()
self.dmvEventsThread = None
if __name__ == ‘__main__’:
MSSQL = None
try:
MSSQL = MicrosoftSQLServerPMDA(‘MSSQL’, 109)
MSSQL.pmda_ready()
MSSQL.run()
finally:
if MSSQL:
MSSQL.Stop()
if MSSQL:
MSSQL.DumpExposedMetrics()
Linux Co-PilotPerformance