“…just two days after turning AimBetter on, we found the cause of an SQL Server query malfunction that we had attacked using Profiler for more than 3 months without progress.“
Dan Ben Amar
CEO/Founder of Rapid Image Ltd
About Rapid Image
Rapid Image Ltd. specializes in providing software and service solutions for medical and veterinary clinics and medical centers. In 1999, Rapid Image launched its flagship product, Rapid Med, a pioneering medical practice management solution and the first Microsoft Windows based product of its kind in Israel.
Today, the company specializes in developing business software leveraging the latest Microsoft technologies and database solutions.
Throughout its years of operation, the company has provided hundreds of clinics and medical centers with diverse solutions, while applying the highest standards and superior technologies. Rapid Image Ltd. achieves this by remaining attentive to its clients needs, and consistently striving to deliver the best quality service that we can.
How AimBetter worked for us solving an intractable problem
In one of our SAAS products, we’ve come across a certain version update causing an unexplained system lockout. Our QA department tried to find the problem for weeks, with many hours of log analysis of the system using standard Microsoft tools, and without result. At one point we were able to prove that the problem is not in the IIS servers but rather something in SQL. We tried to locate the problem using SQL Profiler but could not “catch” the event in real time. At this point, we decided to install the AimBetter monitoring application on this customer because of previous successful experience with this excellent system.
After 48 hours the problem occurred again. Until we had AimBetter’s help, the difficulty was in identifying the problem query. Now, the simple diagnosis came from AimBetter in just a few minutes as soon as the problem re-occurred.
AimBetter alerted us to it immediately in real time and isolated it exactly to the query that caused the tables to lock. Using the AimBetter step-by-step drill-down method beginning from the alert, we were shown a full picture of the query which was the source of the problem, the affected session and the actual plan that was being executed.
With the complete picture available by following the history of the query, we could offer a working solution to the customer…
Change the query to add the keyword WITH NOLOCK after each table name in the FROM and JOIN section, or add the statement ‘set transaction isolation level read uncommitted’
This code is now converted to a Stored Procedure, including NOLOCK, and the problem has not re-occurred.
What we learned
Now looking back, based on what we experienced with AimBetter, we can see that Profiler is not always the best tool to analyze online activity as it does not show clearly the status for queries that have not yet completed.