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 PFM - Agent for Microsoft SQL server.

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-1.

Important

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.

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_DI	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`
PI_ESI^#1	The number of instances is 0.
PI_GENI^#1	The number of instances is 0.
PI_RPDB	Number of the environment setting options shown in the result set when you execute the sp_replcounters system stored procedure.
PI_SERV	The number of instances is 1.
PI_SRV2	The number of instances is 1.
PI_SI	The number of instances is 1.
PI_TLOG	Number of the environment setting options shown in the result set when you execute the DBCC SQLPERF (LOGSPACE) statement.
PI_UCTR	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_EE	Number of the following messages that are written to the Microsoft SQL Server error log at the collection interval specified for the PD_EE record: Japanese Windows, English Windows, Shimplified Chinese Windows: The word is equal to Error in each language Other language Windows: The number of instances is 0. The number of errors by the collecion method of PD_EE record is as follows. When collect historical or evaluate an alarm: The value of instance information "`LIMIT_PD_EE_NUMBER`" and the number of errors written in at error log of Microsoft SQL Server after last collection time either small one. When display real-time report: The number of errors written in at error log of Microsoft SQL Server. The Microsoft SQL Server error log is the error log file for the monitored Microsoft SQL Server instance specified in `SQL_ERRORLOG`.
PD_ES	The number of instances is 1.
PD_GEND^#1	The number of instances is 0.
PD_IA	The number of instances is 1.
PD_JH	`SELECT count(*)` `FROM msdb..sysjobhistory a` `LEFT JOIN msdb..sysjobsteps b ON a.job_id = b.job_id AND a.step_id = b.step_id`
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_LIC	The number of instances is 1.
PD_LOCK	The number of instances is 1.
PD_PCAC	The number of instances is 1.
PD_PDES	The number of instances is 0.
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_RD	Execute the following SQL statement: USE distribution `EXEC sp_tables MSdistribution_history, dbo` If the error "Message 911, Level 16" occurs, the number of instances is 0. If the SQL statement executed successfully and the result is 0, the number of instances is 0. If the SQL statement executed successfully and the result is 1, the number of instances is the execution result of the following statement: 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_RH^#1	The number of instances is 0.
PD_RS	Execute the following SQL statement: USE distribution `EXEC sp_tables MSdistribution_history, dbo` If the error "message 911, level 16" occurs, the number of instances is 0. If the SQL statement executed successfully and the result is 0, the number of instances is 0. If the SQL statement executed successfully and the result is 1, the number of instances is 1.
PD_SQL^#2	The number of instances is 0.
PD_SS	The number of instances is 1.
PD_USER	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`

#1: This record is a reserved record and cannot be used.
#2: This record can only be used in real time.

To Page Top