(content originally posted by Bert Scalzo on 3/8/2017)
There is no such thing as the perfect database. No matter how hard the DBA may try, something outside the planned norms will eventually occur and then performance will suffer. The good news is this means job stability for the DBA. But all kidding aside, what is one to do when database performance tanks and users are up in arms? There is a recommended approach – and it’s actually obvious once you realize that tools exist designed to assist in this specific endeavor. Look at the flow chart below. Often people either can’t afford or don’t know that their database vendor offers a workload capture/replay option (shown in orange) and thus they revert to the old fashioned, painful and costly way (shown in pink) way of doing it. Sometimes however there is another, better option. Depending upon the database platform being used database profiling tools may exist (shown in yellow) which offer the simplicity of a workload capture and replay without the burdensome cost. The difference is that these tools may sample the workload at user defined intervals to best approximate the actual workload characteristics without having to capture the entire workload. Often that’s sufficient to find, diagnose and fix typical database performance problems.
You can try IDERA DB Optimizer free for 14 days to see how it handles database workload profiling (and replay) for Oracle, SQL Server, Sybase ASE and DB2.