Add a DB counter

DB counters allow you to track metrics on the status, availability, usage, system performance, and resource usage of your enterprise database. DB counters require a connection to the target database using its data source name (DSN).

Only queries that return a single number value can be used to capture statistics. For examples of queries that return a single number value, see Sample SQL queries.

Complete these steps to add a DB counter:

1. Verify ODBC drivers.
2. Set the system DSN.
3. Start ArcGIS Monitor Administrator.

   The Connections view appears.

4. Click Open on the Monitor Server connection to which you want to add the counter.

   The collection pane appears.

5. In the tree view, expand the collection and monitor service to which you want to add the counter.
6. Click DB in the tree view to choose it.
7. Right-click DB and click Add.
8. Expand DB in the tree view and click New DB.

   The Config tab appears.

9. Type a name for the counter in the Name text box.

   Tip:

   For easy identification, the name should include the DSN, database name, and table name.

10. Click Add Credential.

    The Add Database Credential dialog box appears.

11. Type a name for the credential in the Name text box.
12. Use one of the following samples to add the DSN Connection:

    Database authentication:

    DSN=<System DSN>; UID=<User ID>; PWD=<Password>

    Windows user authentication:

    DSN=<System DSN>; UID=/; PWD=;

    Tip:

    The connection string text is masked for security purposes. You can type the connection query in a text editor and paste it in the Connection text box if necessary.

13. Click Add.
14. Click the Select a credential drop-down arrow and choose a credential.
15. Choose a sample interval from the Sample Interval drop-down menu.

    Note:

    It is recommended that you not use sample intervals greater than 5 minutes for critical alerts as this can skew availability statistics. For more information, see View collection availability.

16. Optionally type a description in the Description text box.
17. Type a name for the SQL query in the Name text box.
18. Type a SQL query in the Query text box.

    Note:

    Only queries that return a single number value can be used to capture statistics. For examples of queries that return a single number value, see Sample SQL queries.

19. Click Add Query.

    The SQL query is added to the Query List.

20. Click Test to test the counter.

    The test results are displayed below the Test button.

21. Click Save to save the counter.

Now you can configure alerts for the counter. You can also view and analyze counter status and metrics in the ArcGIS Monitor Server application by clicking Categories > Database.

Sample SQL queries

Only queries that return a single number value can be used to capture statistics. See the subsections below for a list of sample SQL queries that return a single number value.

Geodatabases in Oracle

Process_Information

SELECT count(*) FROM sde.process_information;

Versions

SELECT count(*) FROM sde.versions;

States

SELECT count(*) FROM sde.states;

State_Lineages

SELECT count(*) FROM sde.state_lineages;

Lineage_Depth_Default

SELECT count(*) as depth FROM sde.state_lineages WHERE lineage_name in (SELECT lineage_name FROM sde.states WHERE state_id in (SELECT state_id FROM sde.versions WHERE name = 'DEFAULT'));

Compress_Sec

SELECT round((COMPRESS_END - COMPRESS_START) * 24*60*60 ) Seconds FROM SDE.COMPRESS_LOG WHERE (sde_id = (SELECT max(sde_id) FROM sde.compress_log)) and compress_end is not null order by compress_start;

A_Tables_Ora_Stats

SELECT sum(num_rows)FROM dba_tables WHERE table_name in (SELECT 'A'||registration_id FROM sde.table_registry);

D_Tables_Ora_Stats

SELECT sum(num_rows)FROM dba_tables WHERE table_name in (SELECT 'D'||registration_id FROM sde.table_registry);

Oracle databases

The following queries return average values over a predefined period. The sample interval you choose in the Sample Interval drop-down menu needs to match the query. The following sample queries use 5-minute sample intervals:

CPU utilization

SELECT avg(value) CPU FROM v$sysmetric_history WHERE METRIC_NAME = 'Host CPU Utilization (%)' and END_TIME> (sysdate-1/(24*60)*5);

Logical_Reads_Per_Sec

SELECT avg(value) Logical_Reads_Per_Sec FROM v$sysmetric_history WHERE METRIC_NAME = 'Logical Reads Per Sec' and END_TIME> (sysdate-1/(24*60)*5);

IO_Megabytes_per_Second

SELECT avg(value) IO_Megabytes_per_Second FROM v$sysmetric_history WHERE METRIC_NAME = 'I/O Megabytes per Second 'and END_TIME>(sysdate-1/(24*60)*5);

IO_Requests_per_Second

SELECT avg(value) IO_Requests_per_Second FROM v$sysmetric_history WHERE METRIC_NAME = 'I/O Requests per Second' and END_TIME> (sysdate-1/(24*60)*5);

Block_Read_Latency_ms

SELECT avg(value) Block_Read_Latency_ms FROM v$sysmetric_history WHERE METRIC_NAME = 'Average Synchronous Single-Block Read Latency' and END_TIME>(sysdate-1/(24*60)*5);

Active_Sessions

SELECT avg(value) Active_Sessions FROM v$sysmetric_history WHERE METRIC_NAME = 'Average Active Sessions' and END_TIME> (sysdate-1/(24*60)*5);

Physical_Reads_Per_Sec

SELECT avg(value) Physical_Reads_Per_Sec FROM v$sysmetric_history WHERE METRIC_NAME = 'Physical Reads Per Sec' and END_TIME> (sysdate-1/(24*60)*5);

Geodatabases in SQL Server

Some of the following queries assume a dbo-owned schema and a database name of database. Change database to your actual database name. If your geodatabase uses an sde schema, change dbo to sde.

Process_Information

SELECT count(*) FROM database.dbo.SDE_process_information

Versions

SELECT count(*) FROM database.dbo.SDE_versions

States

SELECT count(*) FROM database.dbo.SDE_states

State_Lineages

SELECT count(*) FROM database.dbo.SDE_State_Lineages

Lineage_Depth_Default

SELECT count(*) as depth from database.dbo.SDE_state_lineages WHERE lineage_name in (SELECT lineage_name FROM database.dbo.SDE_states WHERE state_id in (SELECT state_id FROM database.dbo.SDE_versions WHERE name = 'DEFAULT'));

Versions > 07 days old

SELECT count(*) FROM dbo.sde_versions WHERE creation_time < (SELECT CAST(GETDATE() As int )) - 7

Number of states compressed

SELECT dbo.SDE_COMPRESS_LOG.start_state_count - dbo.SDE_COMPRESS_LOG.end_state_count FROM dbo.SDE_COMPRESS_LOG

A table rows

SELECT SUM(sPTN.rows) AS [RowCount] FROM sys.objects AS sOBJ INNER JOIN sys.partitions AS sPTN ON sOBJ.object_id = sPTN.object_id INNER JOIN SDE_table_registry ON sOBJ.name = 'A' + CAST(SDE_table_registry.registration_id AS varchar) WHERE (sOBJ.type = 'U') AND (sOBJ.is_ms_shipped = 0x0) AND (sPTN.index_id < 2) GROUP BY sOBJ.schema_id

D table rows

SELECT SUM(sPTN.rows) AS [RowCount] FROM sys.objects AS sOBJ INNER JOIN sys.partitions AS sPTN ON sOBJ.object_id = sPTN.object_id INNER JOIN SDE_table_registry ON sOBJ.name = 'D' + CAST(SDE_table_registry.registration_id AS varchar) WHERE (sOBJ.type = 'U') AND (sOBJ.is_ms_shipped = 0x0) AND (sPTN.index_id < 2) GROUP BY sOBJ.schema_id

Number of editors

SELECT count (distinct sde_id) FROM dbo.sde_state_locks WHERE lock_type<>'S'

Number of viewers

SELECT count (distinct sde_id) FROM dbo.sde_state_locks WHERE lock_type='S'

Number of versions not reconciled in 30 days

SELECT count(*) from dbo.sde_states WHERE closing_time < (SELECT CAST(GETDATE() As int )) - 30 and state_id in (SELECT state_id FROM dbo.sde_versions)

Number of seconds it takes for the compress to run

SELECT datediff (second, compress_start, compress_end) FROM dbo.SDE_compress_log WHERE sde_id = (SELECT max(sde_id) FROM dbo.SDE_COMPRESS_LOG)

Number of minutes it takes for the compress to run

SELECT datediff (minute, compress_start, compress_end) FROM dbo.SDE_compress_log WHERE sde_id = (SELECT max(sde_id) FROM dbo.SDE_COMPRESS_LOG)

SQL Server databases

Page reads per second

SELECT cntr_value FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Buffer Manager%' AND [counter_name] = 'Page reads/sec'