Symptoms: A transaction is not committed and not rolled back, remains open, 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 the 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 increases response times dramatically.
Recommended action :
Using AimBetter Observer, identify the processes that are occupying most RAM. If possible, achieve the optimal balance between OS RAM and page file allocation. If no other possibility, add RAM.
Inefficient query design Priority: Medium
Incorrect query code may result in inefficient data access.
Recommended action :
- 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
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.
