SQL Databases Test

This test monitors the transactions that occur on every database of a Microsoft SQL server.

Target of the test : A Microsoft SQL server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every database on the Microsoft SQL server monitored

Configurable parameters for the test
  1. TEST PERIOD - How often should the test be executed
  2. Host – The IP address of the Microsoft SQL server.
  3. Port - The port number through which the Microsoft SQL server communicates. The default port is 1433.
  4. instance – The name of a specific Microsoft SQL instance to be monitored. The default value of this parameter is “default”. To monitor a Microsoft SQL instance named “CFS”, enter this as the value of the “instance” parameter.
  5. ssl - By default, the SSL flag is set to No, indicating that the target Microsoft SQL server is not SSL-enabled by default. To enable the test to connect to an SSL-enabled Microsoft SQL server, set the SSL flag to Yes.
  6. useperfmon – By default, this flag is set to Yes, indicating that this test uses the Windows Perfmon utility by default to pull out the metrics of interest. To instruct the test to use queries for metrics collection and not Perfmon, set this flag to No. Typically, when monitoring a Microsoft SQL server in an agent-based manner, its best to go with the default setting – i.e., use Perfmon for metrics collection. However, when monitoring the Microsoft SQL server in an agentless manner, its ideal to use queries instead of Perfmon to collect the required metrics. In such cases, set this flag to No. 
  7. ISPASSIVE – If the value chosen is yes, then the Microsoft SQL server under consideration is a passive server in a SQL cluster. No alerts will be generated if the server is not running. Measures will be reported as “Not applicable" by the agent if the server is not up.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Active transactions:

This value indicates the current number of active transactions yet to be committed to the database.

Number

A large number for this value may indicate a large number of active transactions. Alternately this may also indicate that due to some reasons the users are not able to complete the transactions. 

Transaction rate:

This measure indicates the number of transactions that are started for the database per second.

Trans/Sec

A high value of this metric indicates a lot of transactional activity happening to the database

Replication transaction rate:

This value indicates the number of transactions per second read out of the transaction log of the publication database and delivered to the distribution database.

Trans/Sec

A high value indicates that there is more replicated transactions happening from the publication database and is being sent to the distribution database.

Pending replication transactions:

Indicates the number of pending replication transactions in the database.  

Number

This is the number of transactions in the transaction log of the publication database marked for replication, but not yet delivered to the distribution database.

Data file size:

This metric is the cumulative size of all the data files in the database server.

MB

The value of this metric provides an idea of the growth of the databases hosted by the database server. 

Log flush waits:

This value indicates the number of transaction commits that are waiting in the log flush ready to be flushed.

Waits/Sec

A high value here may indicate non-optimal allocation of the log buffer related parameters.

Write transaction rate:

Indicates the number of transactions that wrote to the database and committed, in the last second.

Trans/Sec

 

Workaround:

Issue the following command:

DBCC UPDATEUSAGE (db_name)

NOTE: DBCC UPDATEUSAGE may take a long time to run because this command updates the dpages value in sysindexes for all the tables in the database, not just the dpages value for syslogs.

After issuing this statement, space reporting should be accurate. If it is not, try issuing the CHECKPOINT command to flush changes in the cache to disk so that they can be read in and calculated correctly.