Performance tuning done by adjusting database parameters or physical layout of the database.
Database Tuning is quite a broad term and can involve a variety of optimisations that can be made to server configuration, physical storage layout, indexing and structure of the database, or recovery strategies.
Many performance issues can be resolved by modifying queries to run with an efficient execution plan or adding indexes to speed up slow queries. Sometimes performance issues can be caused by poor disk layout, or insufficient memory or contention over a finite pool of resources such as locks.
Index tuning involves examining poorly performing queries and adding indexes to the database that can be used by the optimiser.
Storage can be tuned in various ways to optimise performance. For example, it is often desirable to separate indexes onto their own volume. Another example of application specific tuning might be a data warehouse system tuned for fast table scans - a large stripe size on an array will typically improve sequential I/O performance.
System tuning can involve setting memory allocation strategies - a highly concurrent system may be set up to allocate a small amount of memory per session so that a large number of concurrent transactions do not consume excessive memory. A system characterised by queries across large data sets may be tuned to allocate a large amount of memory to sort operations. Some systems take a more automated approach to memory allocation.
Other factors such as logging and recovery strategies, processor and I/O affinity on NUMA systems, isolation level used on frequent transactions or the size of disk buffer caches relative to the working set of the application can also have a significant effect on performance. Tuning work may involve examining a variety of such factors to identify and resolve performance bottlenecks
