Database tuning adviser (DTA) is a tool analyzing workloads involved in database functioning. It enables tuning of databases for improved query processing and the creation of optimal set of indexes, indexed views and partitions. These functions do not require a deep understanding of the structure of database or SQL server internals. DTA closely examines how queries are processed in a database and recommends methods to improve query processing.
This term is also known as SQL server database tuning adviser.
A database tuning adviser may use a graphical user interface tool for tuning databases, viewing recommendations and generating reports. Or, it may use a command line utility program, which includes scripting and automated tuning. Commonly used tuning options present in a database tuning adviser are indexed view, indexes, keep indexes only, keep clustered indexes only, aligned portioning, full portioning, no portioning, etc. Tuning is generally performed when complex queries are executed and consuming additional memory space. A set of such Transact-SQL statements requiring tuning is called a workload. A database tuning adviser analyzes this workload. It also recommends physical design structures, reducing the cost of query optimizers as they analyze workload.
The first step in using a database tuning adviser is defining the tuning task. This can be defined through procedures. The next step involved is executing the tuning task. This is performed using EXECUTE_TUNING_TASK. This function enables the tuning adviser to analyze the workload. After tuning all task executions, the adviser displays and reports its findings. The function used to perform reporting is the REPORT_ TUNING_ TASK.
Get Techopedia delivered to your inbox!