The traditional approach to tuning database servers for increases in performance is to focus wholesale on critical servers - or on ones that are experiencing performance issues.
Exhaustively tuning a complex database server can take hours or days, tying up the most experienced DBA´s trying to squeeze the most out of available hardware resources on that server. In a VM world, you need to think differently.
Since all instances within a virtualized server are competing for shared resources, a more efficient approach is to first target quick wins across all the instances in the VM to free up disk, memory and CPU resources so they can be allocated where it is needed most. With the proper tools, this smarter tuning strategy produces quick wins to keep your databases running at peak performance. Only when this is done should you consider tuning in depth on a single instance.
The old way
When a server is experiencing performance issues, the established tuning method is to focus on the problem server or instance. This approach typically involves specialized tuning application as well as top DBA´s spending many hours to optimize and fine-tune the server. Comprehensive tuning entails optimizing indexes, memory usage, buffer pools, execution plans, etc. This is fine, but since most modern servers are running on virtual machines (VM’s), a more up-to-date technique is called for.
The “quick-win” solution
The database instances and applications installed within a given VM environment are all competing or sharing CPU cores, memory and Storage Area Network (SAN) channel capacity. This means that several smaller instances can be collectively consuming a large share of resources, causing more important database instances to suffer performance issues – even if those large instances are extensively optimized. Thus, trying to tune the lagging database can often be a futile gesture.
A better approach is to optimize all other instances on the VM in order to free up resources, particularly from SAN’s and their associated channels, for the more critical database instances. This technique produces quickly realizable gains in performance for the troubled server.
So, the sequence of operations should be to first do a global optimization of all instances in the VM to remove all the smaller resource hogs. Only when this is done should you deep-dive into tuning the critical ones.
Although a more efficient and less technically challenging approach than conventional tuning, without the proper tools the focus on smaller loads can still be a time-consuming process – especially when database administrators attempt to do it manually. An automated tool that can survey all database instances and identify the loads most likely to free up environmental resources allows you to act quickly to turn off or tune any problem instances.
In our experience, many sites will have a lot of smaller instances running, usually MS SQL Server, that are not very well configured or tuned. While individually small, together this they represent a significant part of total resource consumption in a VM. We often see performance improvements of 20-50% this way with a similar reduction in resource usage. This frees up the resources you desperately need for the big and critical database servers.
dbWatch specializes in complete database management solutions for Oracle, MS SQL Server, Sybase and Postgres environments.
dbWatch provides solutions to simplify, automate and optimize monitoring and management and tuning of large installations of MS SQL Server, Oracle, Sybase and Postgres for Managed Service Providers and Enterprise customers.
Read more about how dbWatch can help you optimize your SQL Server environment.