Longest Transaction Running Time

Definition:
This performance counter displays the length of time, in seconds, the transaction that has been running the longest, has been active.

Information:
Transactions that run longer than others use more resources. They can be used to track down procedures and calls that are taking longer than expected by identifying the specific transaction(s).

Possible problems:
Long running transactions can prevent truncation of transaction logs. This can cause the transaction log files to grow until they consume all available physical disk space shutting down access to the database.

Remediation:
Check the functionality of the query and/or redesign the long running transaction.

Transactions/
sec

Definition:
This performance counter returns the number of transactions started for the database, per second.

Information:
Transaction rate is affected by general system performance and resource constraints, such as I/O, number of users, cache size, and the complexity of requests.

Possible problems:
A high rate of transactions can indicate that some transactions are not completing.

Remediation:
Using stored procedures in transactions speeds transaction processing because SQL code in stored procedures runs locally on the server. The transactions can finish much faster without the network traffic required by the transactions.

Active Transactions

Definition:
This performance counter returns the number of active transactions for the database.

Information:
During an active transaction, no other transaction can modify the data held by the active transaction. While you are modifying data, an exclusive lock is held on that data. Conversely, you cannot read data held by another transaction because another transaction holds an exclusive lock on the data that prevents it from being read. The returned value for this monitor should be less than 200.

Possible problems:
A high number of active transactions can indicate that certain transactions are never completing.

Remediation:
Keeping a low level of active transactions assists in keeping requested data accessible.

Repl. Trans. Rate

(Replication Transactions Rate)

Definition:
This performance counter returns the number of transactions read out of the transaction log and sent to the distribution database

Information:
SQL Server Overall Replication Performance Analysis provides a graph of several SQL Server overall replication performance counters for the specified server during the specified time period. Performance counter data is averaged by the hour. The graph displays the following replication performance counters:

• SQLServer:Databases / Replication Pending Xacts (SQL Server; Scale=1\100)
• SQLServer:Databases / Replication Transaction Rate (SQL Server; Scale=1\100)
• SQLServer Replication Published DB / Replicated Transactions Per Second

Bulk Copy Rows/sec

Definition:
This monitor returns the number of rows bulk copied per second.

Information:
Measure the number of rows coming into the database. Optimization yields a higher number. To learn more, see The Data Loading Performance Guide (© 2020 Microsoft Corp., available at docs.microsoft.com, obtained on June 19, 2020).

Possible problems:
BCP to bulk load data over the network, having the correct throughput configured is crucial.

Bulk Copy Throughput/
Sec

Definition:
This monitor returns the amount of data bulk copied (in kilobytes) per second.

Possible problems:
Overall throughput is mostly be limited by your I/O subsystem.