ALERT

[FREE DEMO] Deploy Your Enterprise Cloud in Minutes

Database Engine Tuning Advisor

Definition - 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.

Techopedia Deals

Connect with us

Techopedia on Linkedin
Techopedia on Linkedin
Tweat cdn.techopedia.com
"Techopedia" on Twitter


'@Techopedia'
Sign up for Techopedia's Free Newsletter!

Email Newsletter

Join thousands of others with our weekly newsletter

Resources
The 4th Era of IT Infrastructure: Superconverged Systems
The 4th Era of IT Infrastructure: Superconverged Systems:
Learn the benefits and limitations of the 3 generations of IT infrastructure – siloed, converged and hyperconverged – and discover how the 4th...
Approaches and Benefits of Network Virtualization
Approaches and Benefits of Network Virtualization:
Businesses today aspire to achieve a software-defined datacenter (SDDC) to enhance business agility and reduce operational complexity. However, the...
Free E-Book: Public Cloud Guide
Free E-Book: Public Cloud Guide:
This white paper is for leaders of Operations, Engineering, or Infrastructure teams who are creating or executing an IT roadmap.
Free Tool: Virtual Health Monitor
Free Tool: Virtual Health Monitor:
Virtual Health Monitor is a free virtualization monitoring and reporting tool for VMware, Hyper-V, RHEV, and XenServer environments.
Free 30 Day Trial – Turbonomic
Free 30 Day Trial – Turbonomic:
Turbonomic delivers an autonomic platform where virtual and cloud environments self-manage in real-time to assure application performance.