A.4 Calculating the number of instances for records
The following table shows how to calculate the number of instances for each record collected by RM for Microsoft SQL server.
- Note
-
The number of instances is used as dynamic information about the monitored Microsoft SQL Server. Since the number of instances dynamically changes due to factors such as the number of connections with the monitored Microsoft SQL Server, use the maximum of the obtained values for your estimate.
Use sqlcmd or a similar program to access the Microsoft SQL Server to be monitored, and execute an SQL statement to obtain the number of instances. If a calculation method for the target record is provided in the table, use that method.
- To use osql to obtain the number of instances:
-
-
Start sqlcmd.
-
As a user who has the following privilege, connect to the Microsoft SQL Server to be monitored:
- sysadmin
-
For the record for which you want to check the number of instances, execute the SQL statement shown in Table A-5.
Table A‒1: Calculating the number of instances for records Record
SQL statements that obtains the number of instances, and calculation method
PI
The number of instances is 1.
PI_PI2
The number of instances is 1.
PI_SERV
The number of instances is 1.
PI_SRV2
The number of instances is 1.
PI_SI
The number of instances is 1.
PD
The number of instances is 1.
PD_CD
Number of the environment setting options
shown in the result set when you execute the sp_configure system stored procedure.
PD_DD
- Microsoft SQL Server version 2014 or earlier:
-
SELECT count(*) FROM master..sysdatabases
- Microsoft SQL Server version 2016 or later:
-
SELECT count(*) FROM master.sys.databases
PD_DS
- Microsoft SQL Server version 2014 or earlier:
-
SELECT count(*) FROM master..sysdatabases
- Microsoft SQL Server version 2016 or later:
-
SELECT count(*) FROM master.sys.databases
PD_IA
The number of instances is 1.
PD_LD
- Microsoft SQL Server version 2014 or earlier:
-
SELECT count(*) FROM master..syslockinfo
- Microsoft SQL Server version 2016 or later:
-
SELECT count(*) FROM master.sys.dm_tran_locks
PD_LOCK
The number of instances is 1.
PD_PDET
- Microsoft SQL Server version 2014 or earlier:
-
SELECT count(*)
FROM ( master..sysprocesses a
LEFT JOIN master..syslogins b ON a.sid = b.sid )
LEFT JOIN master..sysusers c ON a.sid = c.sid
- Microsoft SQL Server version 2016 or later:
-
SELECT count(*)
FROM master.sys.dm_exec_sessions a
LEFT JOIN master.sys.server_principals b ON a.security_id = b.sid
PD_SS
The number of instances is 1.
-