Query on active_connection_ratio calculation in sqlserver probe

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

Can you please let us know the underlying logic or sql query for KPI "active_connection_ratio" in the sqlserver probe.

Monitors ratio of active connections to total allowed connections.

Is it from db level or server level since on one of the DBs the connection was set as unlimited but there was an alert for 99%

Currently it has 2.3% when the connection is 800 in db. please provide the logic and sql query used for this KPI.

Environment

Release : 20.3

Component : UIM - SQLSERVER

Cause

- guidance

Resolution

Monitors ratio of active connections to total allowed connections.

{"active_connection_ratio",
  "select (select CASE value WHEN 0 THEN 32767 ELSE value END  from sys.sysconfigures where comment like '%user connections%') as total,"
  " (select count(dbid) from sys.sysprocesses where hostname <> '' and dbid > 0) as active"}

{"active_connection_ratio",
  "SET DEADLOCK_PRIORITY LOW  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED select (select CASE value WHEN 0 THEN 32767 ELSE value END  from dbo.sysconfigures where comment like '%user connections%') as total, "
  "  (select count(dbid) from dbo.sysprocesses where hostname <> '' and dbid > 0) as active"}

---------------------------------

This is the same as the Microsoft SQL Server query for number of open connections.

Here is the core query that can be run manually to check the results:

select (select CASE value WHEN 0 THEN 32767 ELSE value END  from sys.sysconfigures where comment like '%user connections%') as total,(select count(dbid) from sys.sysprocesses where hostname <> '' and dbid > 0) as active

When executed, the result of the query gives you a count of total and active connections, for example in a lab environment:

total      active
32767   508

By default, SQL Server allows a maximum of 32767 concurrent connections which is the maximum number of users that can simultaneously log in to the SQL server instance, so that is what the value is, when set to 'unlimited.'