Jan 21, 2020 6:45:00 AM by Tim Fritz
SQL tuning is needed because structured data is here to stay—even as unstructured data becomes more prevalent and important.
Quest Software® partnered with Unisphere Research to study the virtual avalanche of new kinds and quantities of data that technology has unleashed in recent years. We were able to glimpse into data and retell our discoveries in this white paper, “The Real World of the Database Administrator." According to research by Dr. Elliot King, research analyst at Unisphere Research, a Division of Information Today, Inc., data is here to stay and continues to evolve, and that means SQL tuning is needed. Below is a screen shot of a table from that study.
A key takeaway from the study is this:
Structured data is still going to take the majority of DBA’s time in the foreseeable future. And structured data needs to be accessed, so that means that SQL will be the access method that professionals will depend on for ad hoc queries as well as application data access and manipulation. And, since transactional data makes up almost 80% of all new structured data within organizations, we know that customer-facing and other business-critical applications will depend upon high performing data access.
So, structured data leads us to the need for SQL tuning. Tuning SQL is rarely done with any regularity at your organization? You’re not alone. But according to Dr. Elliot’s research:
That seems to indicate that overall, SQL tuning is happening quite regularly. But, another finding in the report is that 78% of performance tuning, including SQL tuning, is done manually. More on that shortly.
The level of importance of tuning SQL statements is evidenced by the knowledge that around 83% of new structured data is coming from e-commerce and other transactional applications. Customers and users are likely directly impacted by poor-performing SQL in those types of applications (see the graph below, an excerpt from the King research previously cited).
Structured data is currently stored predominantly in relational databases. You might be asking why is SQL tuning so important since most of the commonly used relational database vendors supply internal, built-in SQL optimization mechanisms that handle re-writing the queries internally before execution, for example.
There are major deficiencies that cause the database’s internal SQL optimizer to fail to find a satisfactory access plan for a SQL statement, making SQL tuning by developers and DBAs necessary. To read more about those deficiencies, read the Quest® technical brief, “Solving the SQL tuning problem: Secrets of the Quest SQL Optimizer.”
The deficiencies, for example, lack of up-to-date statistics and inaccurate calculation of costs, hinder optimization of SQL unless a person (or tool) gets involved in the process. If a tool is used alternative rewrites of the SQL statement can be generated and then examined, and even compared visually with the original. Costs of the new plans are computed, but decisions on whether a new plan is “better” might be decided based on other criteria, such as the time to return the first row or total execution time. Yes, with SQL Optimizer, not only do new alternative SQL structures get generated, but you can easily execute those alternatives side-by-side to prove which is fastest. You will be able to choose which rewrite of the SQL is best to implement.
Quest Software® specializes in cross-database solutions and is vendor neutral. Quest® SQL Optimizer supports a growing number of platforms including these: DB2 for LUW, DB2 for z/OS, Oracle, SAP ASE, and SQL Server. Read more about these products:
*SQL Optimizer is only available in our Xpert, Developer or DBA editions in these products.
Why not save time of developers or DBAs, whoever in your organization is responsible for tuning SQL? Automation of tuning is really important—and is doable.
The King study reports that only about 15% of database workload performance tuning (activities such as rewriting SQL and adding indexes) is currently automated. So, the large majority of the effort of tuning SQL appears to be spent in manual processes. Manual processes take more time, are not as comprehensive, and are more prone to error when compared to automated processes for tuning SQL.
The most dramatic time savings are the result of the faster generation of rewritten SQL. Alternatives can be generated far more rapidly. Quest customers have reported seeing a long list of possible alternatives in a matter of hours where it would have taken many days for someone to actually rewrite by hand. The actual time savings that you might experience, of course, depends on many factors unique to your environment, database workloads, and goals for tuning.
With Quest SQL Optimizer, automating SQL tuning means important time-saving capabilities listed below as well as others we will cover in a future post:
All of these features add up to time-savings and optimal tuning efficiency and effectiveness.
As always, examples help, and this blog post by my colleague Jeff Surretsky illustrates much of the time-saving capabilities of SQL Optimizer from Quest Software by pointing you to past blog posts covering aspects of SQL Optimizer in his blog, “9 blogs to help tune your most challenging SQL Statements.” (Note that these posts focus on Oracle and SQL Server, but the concepts apply to other dbms types, too).
If you think your colleagues would benefit from this article, share it now on social media with the buttons located at the top of this blog post. Thanks!
Written by Tim Fritz
Quest Software solution architect - database performance management and administration solutions. Former DB2 developer and DBA. IBM Certified Database Administrator – DB2 LUW; Microsoft MCP, Certified Technical Specialist – SQL Server.