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
-
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.
-
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 |
|
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 |
|
PD_DS |
|
PD_EE |
Number of the following messages that are written to the SQL Server error log at the collection interval specified for the PD_EE record:
|
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 |
|
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 |
|
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: SQL Server version 2014 or earlier: SELECT count(*) FROM master..sysdatabases 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:
|
PD_SQL#2 |
The number of instances is 0. |
PD_SS |
The number of instances is 1. |
PD_USER |
|
- #1
-
This record is a reserved record and cannot be used.
- #2
-
This record can only be used in real time.