Transactions

A transaction is a unit of work that is performed against a database. In other words, if you are creating a record, for example, then you are performing transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors.

This resource contains monitors specific to the current status of transactions. The icons of the listed performance counters in the Statistic Name column changes color to reflect the listed counter's current state. Clicking any performance counter in the Statistic Name column takes you to the Performance Counter Details page for that counter.

If the value of a listed performance counter approaches or crosses the Warning threshold, a yellow band will appear for the listed counter allowing plotted values that cross the threshold to easily be visualized against the yellow band. If the value of a listed performance counter approaches or crosses the Critical threshold, a red band will appear for the listed counter allowing plotted values that cross the threshold to easily be visualized against the red band. Hovering over any time period within the chart will display detailed information about that time period in the tooltip. Hovering over a counter will give you detailed information about that performance counter.

Counter Expert Knowledge

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. See The Data Loading Performance Guide http://technet.microsoft.com/en-us/library/dd425070(v=SQL.100).aspx

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.

Zooming

You can have the chart show a predetermined time period of data by clicking on any one of the three Zoom buttons in the head of the Zoom column. Alternatively, you can have the chart show a specific date range by dragging the mouse over an area of the chart. The time and date displayed at the top of the resource shows the amount of data currently loaded into the chart. This can be changed by clicking Edit from within the resource.

The Value from Last Poll column shows the most recent data available for the listed statistic.