Taking the black art out of SQL tuning
Posted on November 12, 2009 by Anh Huynh
Filed Under News | Tags: develpers, SQL |
As any database admin knows, mastering the subtler nuances of SQL can be likened to the black arts. For me, tuning the efficiency of SQL queries on large, heavily used databases is a perfect example.
Sure, I know how to use the query analysis tools built into SQL Server and Oracle — but even with them, optimizing SQL still requires me to monitor queries during heavy load to find out whether I’ve truly fixed the code, without introducing new problems in the process.
[ Keep up with app dev issues and trends with InfoWorld's Fatal Exception and Strategic Developer ]
Last week Kyle Hailey of Embarcadero gave me a live demo of DB Optimizer 2 and made it very clear how I or any database programmer could use the DB Optimizer to methodically analyze, improve, and load test SQL queries. Two of the tools introduced in this package are Visual SQL Tuning Diagrams and Index Analysis. The package also includes sophisticated profiling and parallel load stress testing. The figure below shows profiling (click on it to see a full-size screen):
The next figure shows Visual SQL Tuning index analysis:
The query optimization that Kyle used as a demonstration was from a major package that shall remain nameless to protect the guilty.
I have to say, I was very impressed. I’m not sure exactly how big a shop you have to be to rationalize a $1,500 tool like this, but the ROI for optimizing a query versus throwing bigger hardware at it is usually easy to justify.
Interested admins can view a video demonstration of DB Optimizer 2 on Embarcadero’s Web site.
Leave a Reply