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.
Check what else is running that might block resources needed by the open transaction.
Insufficient RAM can force excessive page file swapping, which reduces overall speed to disk-access times and increses response response times dramatically.
Using AimBetter Observer, identify the processes that are occupying most RAM. If possible, achieve optimal balance between OS RAM and page file allocation. If no other possibility, add RAM.
Incorrect query code may result in inefficient data access.
- Make sure that each transaction has COMMIT (in case of no errors) and ROLLBACK (in any case of an error) statements where applicable
- 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
Detect missing indexes by looking at the execution plans.
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.