Reading Fewer Data Blocks = Happy Users
Imagine how many enterprise IT customers have bought way more storage, compute, and network capability than they actually needed simply because they either don’t have the resources available or the expertise on staff to analyze query performance?
Over the last year, we have delivered some interesting consulting engagements where the common theme is performance analysis and tuning. For example, a Client has recently upgraded some key portion of their environment – their storage, compute, or network, for example – and since the upgrade, the application’s performance has not improved, or, in some cases, performance has actually declined.
Traditional performance monitoring of all the key components of the system don’t indicate an external bottleneck, and a review of the configuration settings in use within each component of the stack, doesn’t yield any low hanging fruit either.
What to do?
When I’m asked to try and come up with an action plan for remedying these sort of issues, honestly, it can be a tall order. The problem could reside in one of 4-5 places in your environment, combing through each of those areas can be very time consuming, and our team’s findings may not yield very much impact.
Let me explain.
An engineer could spend 1-2 weeks looking at the performance and configuration parameters for the Operating System, the storage subsystem, the virtualization software, and the network switches – basically a comprehensive review. At the end of the day, while I might have some valuable findings as a result, will those recommendations make a significant dent into the problems that end users are complaining about?
What I have learned is that more than likely, the answer is no.
Tuning the SQL query? Only the ISV Can Do That, Right? Wrong!
Until recently, I thought that tuning the SQL statements of a large database driven application, for example, an ERP software package, was next to impossible without the assistance of software publisher (the ISV). I thought, “You’d have to open a support case and ask the developers to tune the query. You can’t do that yourself.”
Boy, was I wrong. (I was partially right, you can open a case, but you won’t get very far in most cases I have seen).
It turns out to be alot quicker, easier, and cheaper than I thought. Moreover, the wait time reductions that are realized (users are not waiting for queries to return data to their screen) actually pay for themselves when you think about the enhanced productivity of that particular end user.
In other words, if an end user is waiting on queries to return for X number of minutes per day, it is fairly easy to assign a dollar value to that time by analyzing the employee’s annual salary plus benefits and determining what the cost is to the firm. Multiply that cost by the number of days spent annually using the system, and then by the total number of users of the system, and you can see how the figures start to add up.
Trust the Process
After observing the process of tuning SQL queries up-close this past year, I have quickly become a believer in this process. Nowadays, when we are asked to get involved with one of these scenarios, I almost always suggest that in addition to reviewing the standard areas like storage and compute performance, we will suggest carving off some time for a specialist on our team to review the SQL query performance of the system. In many of these cases, if I can, I try to prioritize tackling the query tuning before we embark on the other performance analysis activities.
So how does the process work? It is actually very simple, but the engineering expertise required can be a tall order for some organizations.
First, you should assemble a multi-disciplined team consisting of actual end users of the system, the system administrators (DBA, storage, and compute), and a proven SQL database tuning resource who hopefully has expertise tuning your particular software package.
Next, install a tool like Solarwinds Database Performance Advisor (DPA) to baseline the database workload, give you visibility into your system, and act as the “scorecard” of record. The tool provides us with empirical data that everyone can agree upon and rely upon. It’s easy to install, and it quickly begins collecting workload data about your database. We normally let the tool run for 1-2 weeks, and then review the DPA reports. Here is an example report from DPA from a busy MS-SQL server:
This stacked bar graph shows us what the total database wait time was for each day in the collection period.
For example, on Monday, November 16th, the database wait time was 6hrs 53min, and exactly one week later on Monday, November 23rd, the database wait was 2hrs 45min. But, what caused the wait time to go down? Yup, you guessed it. We had implemented 3 query recommendations the week of the 16th. More on how we did that in a minute…
Then, a developer will analyze each query, review the SQL (which DPA provides for us), and then he or she will make suggestions to tune the query. This is the part that requires some serious skill and expertise. How do they do it? The answer boils down to one simple question, “How can I optimize the SQL to read fewer blocks?” Reading fewer blocks on disk results in less work for every part of the stack. What I mean is that you should not be reading 10,000 data blocks for that query, and, instead, could alternatively read 200 blocks to get the same results.
There are 3 elements to every SQL plan running on every relational database (Oracle, MS SQL Server, DB2, etc.). Those three parts are the join sequence, join methods and access methods. We have a proven methodology for tuning all 3 of those elements.
Lastly, we make the change to the database query or the database itself, and then go about the process of measuring the impact that the change had. Here are some examples of queries where you can see a before and after view of the query performance. The general theme is that while the execution load stays roughly constant the database wait time as reported by DPA is actually down significantly.
The below query was tuned on 11.19.15 and notice how the database wait is dropped to almost zero but yet the execution history doesn’t change all that much.
The below query was tuned on 11.22.15 and notice how the database wait is dropped to less than half but yet the execution history remains constant.
The below query was tuned on 12.06.15 and notice how the database wait is dropped to almost zero but yet the execution history doesn’t change.
If you are the application owner, DBA, or system admin for your IT organization, and you are struggling to find the performance bottleneck in your environment, I suggest you let Headwaters Group take a close look at your database query performance. In 30 minutes, we can show you how our approach will yield results of at least 2x faster elapsed query times and a 50% reduction in hardware usage. Many of our Clients experience even higher levels of improvement.
Query performance could be the missing piece in your organization, and we have the unique expertise to help.