Open Transaction – detection with AimBetter monitor
by Henry Kaye
Introduction
In one large SQL Server installation, a sudden uptick in user response times was reported, and it persisted for several hours. Their investigations were not producing any good solutions, so they approached our AimBetter team for suggestions. With our ability to install the AimBetter monitoring tool quickly, and produce meaningful results within a few minutes, we were able to pinpoint to the root cause and enable a fix, in just a few minutes.
The AimBetter way
AimBetter monitors over 600 individual performance metrics across the whole spectrum of SQL, CPU, storage and network elements. These are analyzed and measured against preset levels to detect abnormal or unexpected behavior. Any matters requiring attention are then displayed on the simple Alerts dashboard, saving the need to examine all the redundant data which shows normal behavior, highlighting only what needs action.
Immediately after the AimBetter agent started running, it detected and reported an open transaction – as shown here.
Open transactions are a frequent occurrence in SQL environments and solving them can be a time-consuming job, because they can be the result of a number of completely different things. Most common causes are:
- Something prevented the transaction from being committed
- Insufficient free memory
- Insufficient RAM forcing excessive page file swapping
- Inefficient query design
- Missing indexes
Without a solution, there is a danger of the collapse of the system or risk that the DB will enter SUSPECT mode and in this situation, the client will lose data.
Without a clear lead, it is not possible to know where to start looking for the real cause, but with a single click, AimBetter opens up a display analyzing all currently running queries, leading to a clear pointer to the culprit. What is obvious from this screen is that one query has been running for more than 16 hours, and is currently in ‘Sleeping’ status, which means that it is unable to complete and commit.
With this information, everything we needed to know about the offending query is available. Just to illustrate that the conclusion is valid, the next screen shows a different picture, based on database statistics, illustrating that the transaction has been running smoothly for the full time, consuming exactly the same level of resources. This is an important indicator that it is a query in some kind of loop, and not simply a valid query that is taking a long time to complete.
Summary
If the AimBetter solution had been installed at the time of the malfunction, after a few minutes an alert would be received.
There are many situations that the log swells like on a TEMPDB or other DB that can not figure out the source of the problem
In the event that the chip is not installed, the DBA will clean the log and the company will again be exposed to the risk that the problem will return. The DBA in many situations runs a job to clean the log and ignores the problem or performs a KILL. AimBetter allows you to identify quickly and give a warning and with the help of professional staff to provide a solution. The bottom line is to address the source of the problem.
In just a few steps, AimBetter was able to alert this customer to the cause of a long-persisting error and produced a positive outcome. AimBetter works with the most comprehensive range of database metrics and does extensive analysis, but presents simple visual output that presents the complete picture and enables logical steps to be followed for detection and solution of any problems. Even without extensive training in database administration, IT personnel can get a handle on all performance issues from a single monitor screen, and concentrate on issues needing attention.
How AimBetter delivers.
AimBetter brings a host of special features and benefits that are specially designed to make the management of SQL databases more efficient and to simplify the work of database administrators. The particular features that worked in favor of our customers, in this case, can be summarized as follows:
- Immediately raises alerts to any unexpected behavior
- Identifies all current queries elements
- Allows query selection on specific criteria
- Displays all live Queries statistics
- Enables historical display of queries
- Retrieves full query syntax
- Retrieves details for both queries involved in the deadlock
- Monitors core SQL database performance
- Reports on SQL Exceptions
- Enables comparison of current with historical metrics
- Displays in a single comprehensive dashboard
Putting all these features into a single package and then having the results at the fingertips of both our experts and the customers’ own database managers was the key element that allowed for this happy ending.
AimBetter is the best tool in the market that helps you handle malfunctioning queries and doesn’t require you to use complex code in order to troubleshoot. Additionally, it monitors all of your database operations for you automatically, therefore, it saves you a lot of time and effort and is the best practice for your db maintenance.
