关于Hyperic 监控 SQL Server

 Hyperic 监控 SQL Server,由Hyperic 插件 Microsoft SQL Server plugin 提供。

支持监控SQL Server的版本

Microsoft SQL Server 2012

Microsoft SQL Server 2008 R2

Microsoft SQL Server 2008

Microsoft SQL Server 2005

Microsoft SQL Server 2005

SQL Server 资源和 Hyperic 资源模型

Hyperic 可监控SQL Server的实例 MsSQL和 数据库 MsSQL Database。

用Hyperic资源模型术语表示如下:

MsSQL的资源类型是"服务器".

MsSQL Database 的资源类型是“服务”。如果在一个SQL Server实例上有多个数据库,那么在 MsSQL下有多个MsSQL Database服务实例。

Microsoft SQL 配置说过程

步骤 1: 在MsSQL运行的服务器上安装 Hyperic Agent;

步骤 2:在仪表盘界面的自动发现Portlet中导入 SQL Server 资源;该资源应该是启动Hyperic Agent后自动发现的。

步骤 3: 配置SQL Server 的监控参数

1,转到 SQL Server配置属性界面;

2,在mssql.user字段,输入连接SQL Server的用户名;

3,在mssql.password字段,输入连接SQL Server的密码;

4,如果希望启用日志跟踪,请选择server.log_track.enable;

步骤 4: 验证配置信息,点击确定,如果配置有问题将显示错误信息提示。

Microsoft SQL资源类型和管理功能

MS SQL 自动发现

Microsoft SQL Server插件通过在Windows 注册表中搜索SOFTWARE\Microsoft\Microsoft SQL 来发现资源;

MS SQL指标采集

采集指标数据的插件类是 MsSQLMeasurementPlugin, 该类扩展自 Win32MeasurementPlugin 支持类.

该插件通过一个 SQL脚本获得"Free Space Percentage" 指标. (该脚本包含在插件的XML 定义文件中.) 其他指标值是通过Windows Performance Manager计数器获得. 更多细节,请阅读源码. :)

MS SQL 控制动作

SQL Server插件使用Win32ControlPlugin 支持类提供了3个控制动作:

■ stop

■start

■restart

Microsoft SQL 日志跟踪

SQL Server插件通过org.hyperic.hq.product.Win32EventLogTrackPlugin 跟踪日志。当启用日志跟踪时,插件将抓取包含SQL Server服务名的事件,该服务名是由插件的sqlserver_name属性指定,默认是 MSSQLSERVER。

数据库阻塞检测

在Hyperic CRP中,提供阻塞检测功能,对于超过指定时间的会话现象,将报告引起阻塞的会话和受影响的会话信息,具体包括程序名称,sql语句,等待类型,spid等信息。

Microsoft SQL采集的指标

  • Availability

SQLQuery指标

  • Availability
  • Query
  • Query Execution Time

吞吐量指标

  • Active Transactions
  • Transactions
  • Transactions per Minute
  • Transactions per Second

缓存指标

  • Worktables From Cache Ratio
  • Cache Hit Ratio
  • Cache Pages
  • Cache Object Counts

锁指标

  • Lock Requests
  • Lock Requests per Minute
  • Lock Timeouts
  • Lock Timeouts per Minute
  • Lock Waits
  • Lock Waits per Minute
  • Lock Wait Time

日志指标

  • Log File Size
  • Log File Used Size
  • Percent Log Used
  • Log Cache Hit Ratio
  • Log Flush Wait Time
  • Log Truncations
  • Log Growths
  • Log Shrinks

资源使用率指标

  • Data File Size
  • Replication Pending Transactions
  • Replication Transaction Rate
  • User Connections
  • Number of Deadlocks
  • Number of Deadlocks per Minute
  • Average Wait Time
  • Average Latch Wait Time
  • Total Latch Wait Time

内存指标

  • Connection Memory
  • Granted Workspace Memory
  • Lock Memory
  • Lock Blocks Allocated
  • Lock Owner Blocks Allocated
  • Lock Blocks
  • Lock Owner Blocks
  • Maximum Workspace Memory
  • Memory Grants Outstanding
  • Memory Grants Pending
  • Optimizer Memory
  • SQL Cache Memory
  • Total Server Memory

MsSQL 2000, 2005 and 2008 Database 指标

  • Database Free Percent
  • Availability
  • Data File Size
  • Log File Size
  • Log File Used Size
  • Percent Log Used
  • Active Transactions
  • Transactions
  • Transactions per Minute
  • Transactions
  • Replication Pending Transactions
  • Replication Transaction Rate
  • Log Cache Hit Ratio
  • Log Flush Wait Time
  • Log Truncations
  • Log Growths
  • Log Shrinks

MsSQL 2000, 2005 and 2008 Server指标

  • Availability
  • Data File Size
  • Log File Size
  • Log File Used Size
  • Percent Log Used
  • Active Transactions
  • Transactions
  • Transactions per Minute
  • Transactions
  • Replication Pending Transactions
  • Replication Transaction Rate
  • Log Cache Hit Ratio
  • Log Flush Wait Time
  • Log Truncations
  • Log Growths
  • Log Shrinks
  • Full Scans
  • Full Scans per Minute
  • Worktables Created
  • Worktables Created per Minute
  • Workfiles Created
  • Workfiles Created per Minute
  • Worktables From Cache Ratio
  • Page Splits
  • Page Splits per Minute
  • Logins
  • Logins per Minute
  • Logouts
  • Logouts per Minute
  • User Connections
  • Connection Memory
  • Granted Workspace Memory
  • Lock Memory
  • Lock Blocks Allocated
  • Lock Owner Blocks Allocated
  • Lock Blocks
  • Lock Owner Blocks
  • Maximum Workspace Memory
  • Memory Grants Outstanding
  • Memory Grants Pending
  • Optimizer Memory
  • SQL Cache Memory
  • Total Server Memory
  • Lock Requests
  • Lock Requests per Minute
  • Lock Timeouts
  • Lock Timeouts per Minute
  • Number of Deadlocks
  • Number of Deadlocks per Minute
  • Lock Waits
  • Lock Waits per Minute
  • Lock Wait Time
  • Average Wait Time
  • Average Latch Wait Time
  • Total Latch Wait Time
  • Page life expectancy
  • Lazy Writes
  • Lazy Writes per Minute
  • Checkpoint Pages
  • Checkpoint Pages per Minute
  • Page reads
  • Page reads per Minute
  • Page writes
  • Page writes per Minute
  • Free pages
  • Stolen pages
  • Buffer Cache hit ratio
  •