When you buy through affiliate links in our content, we may earn a commission at no extra cost to you. Learn What Does Database Engine Tuning Advisor Mean?
Database Engine Tuning Advisor is a tool for analyzing workloads involved in database functioning. It enables the tuning of databases for improved query processing and the creation of an optimal set of indexes, indexed views and partitions. These functions do not require a deep understanding of database structure or SQL server internals. Database Engine Tuning Advisor 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 Advisor.
Techopedia Explains Database Engine Tuning Advisor
Database Engine Tuning Advisor may use a graphical user interface tool for tuning databases, providing recommendations and generating reports. Or, it may use a command line utility program, which includes scripting and automated tuning. Tuning is generally performed when complex queries are executed and consume additional memory space. A set of such Transact-SQL statements that require tuning is called a workload. A tuning advisor analyzes this workload and recommends physical design structures, reducing the cost of query optimizers as they analyze workload.
The first step in using Database Tuning Advisor is defining the tuning task. The next step involved is executing the tuning task. This is performed using EXECUTE_TUNING_TASK. This function enables the tuning advisor to analyze the workload. After tuning all task executions, the advisor displays and reports its findings. The function used to perform reporting is the REPORT_ TUNING_ TASK.
The session monitor window displays all tuning results, which are stored in the MSDB database of the SQL server database engine. The advanced tuning options permit users to limit the maximum space used by physical data structures and the maximum number of columns per index. Upon completing tuning, there is also an option to evaluate recommendations. These recommendations are saved in an XML file or SQL script.
