SQL: Queries open transaction

Symptoms : A transaction is not committed and not rolled back, remains idle and is using resources that could have been freed up for other processes. SQL functions are running slower, affecting user response times and degrading performance.

Impact : Medium

Slow SQL responses will degrade the user experience, resulting in poor efficiency of your organization’s operations.
Other queries that depend on the not-yet-committed transaction will use data that is not up to date and errors might be caused
Log files keep inflating, leading to full disks.

Expected behavior :

SQL imposes no absolute limits on the length of time needed to commit any query. For this reason, use past performance together with current behavior to set appropriate threshold levels to handle all possible cases.

Possible causes

Something prevented the transaction from being committed  Priority : Medium

Recommended action :
Check what else is running that might block resources needed by the open transaction.

Insufficient free memory  Priority : Medium
Insufficient RAM can force excessive page file swapping, which reduces overall speed to disk-access times and increses response response times dramatically.
Recommended action :
Using AimBetter Observer, identify the processes that are occupying most RAM. If possible, achieve optimal banalance between OS RAM and page file alloation. If no other possibility, add RAM.

Inefficient query design  Priority : Medium
Incorrect query code may result in inefficient data access.
Recommended action :
Check what are the bottlenecks of the long running queries, and try to optimize long running queries accordingly. Look for functions over fields that can be tuned or avoided in order to improve performance. Minimize the number of repeats (reading records more than once) as much as possible. Minimize the number of fields and records in the result sets as much as possible so that they would only return what is needed and nothing more. Break complex queries to more simple steps. Look for often used “Cluster Lookup” and “Hush Merger” and minimize their frequency by optimizing the code. Make sure that each transaction has COMMIT (in case of no errors) and ROLLBACK (in any case of an error) statements where applicable.

Missing indexes  Priority : Medium
Recommended action :
Detect missing indexes by looking at the execution plans.

Background

Transactions are committed by SQL when successful or rolled back when not. Unless a resource they need is locked they shouldn’t be idle. As long as the oldest transaction among the transactions in memory is open, the log file(s) continue to grow and the newer committed transactions remain in the log file(s) and do not get cleared. This is one of the main causes for running out of disk space.