Various Steps of Database Tuning – A General Approach

Written by

When it comes to tuning the databases, it can be done by using the workloads in various ways. It can be done either though the GUI of the Database Engine Tuning Advisor (DETA) or by using the utility of the dta command-line. A workload can be defined as a set of statements of Transact-SQL, which are designed to execute in a particular database or a group of database that are needed to be tuned. The DETA or the Database Engine Tuning Advisor uses trace tables as well as trace files along with scripts of Transact-AQL as workload input while tuning the databases.

Irrespective of the interface that is chosen, using a definite workload for tuning database involves a repetitive process, which is chalked out below.

Creating the workloads

Workloads can be created in two ways:

  • The developer can launch the Query Editor in the SQL Server Management Studio
  • It can also be done by typing in the Transact-SQL script into the designated Query Editor, wherein the script needs to contain a series of Transact-SQL statements, which are designed to execute in a particular database or a series of databases that are subjected to tuning. The file thus created needs to be saved with .sql extension. The GUI of the Database Engine Tuning Advisor as well as the utility of the command-line can use this script as workload.

The Process of Database Tuning

The database can be tuned in 2 different ways: 1) Tuning with the use of Database Engine Tuning Advisor and 2) Tuning with the use of dta Utility

Tuning with the use of Database Engine Tuning Advisor

Database can be tuned with the help of a Database Engine Tuning Advisor GUI, by using the tables or the workload files. The GUI of the DETA helps in viewing the results of the current tuning sessions at a given point in time and in showing the results of the previous tuning sessions, without any hassle whatsoever.

databaseTuning

Tuning with the use of dta Utility

This ‘dta’ utility also comes up with a command prompt file that is executable. This file can be used to tune the database as well. The file provides the option of using the functionality of the Database Engine Tuning Advisor in scripts as well as in batch files. The dta utility considers the trace files and tables, along with the scripts of Transact-SQL as workloads. Besides, it also receives the XML input, which matches the scheme supported by the DETA XML.

View the Tuning Output

Database Engine Tuning Advisor also creates the tuning logs along with respective tuning summaries, reports, as well as recommendations that can be subsequently used in evaluation of the tuning sessions. With the help of all this information, it becomes subsequently possible to decide, whether the tuning is done with, and the recommendations of the Database Engine Tuning Advisor are ready to be finally implemented.

Performing the Exploratory Analysis

This particular step of remote DBA tuning is optional. Technically, it is a newly installed user-specified configuration feature of the DETA that can be used while reviewing the output of the DETA and deciding where further tuning is needed to arrive at a configuration that is more accurate and optimal. In fact, this particular feature helps in specifying a hypothetical or theoretical configuration for DETA, for analyzing without making any extra expense of implementing the theoretical configuration first, and then going for the subsequent fine tuning.

Implementation of the Tuning Recommendations

Once the entire process is down with, with satisfactory results, and once the best configuration is in place for the installation of the MSSQL Server, should the tuning recommendations be implemented into the live production environment.

Article Categories:
Technology

Comments

  • You are at the right place if you are searching for tuning the database. Most of the people don’t know about database tuning, but nowadays many companies give training to their employees for tuning. This article shows various important steps of tuning the database. The database can be tuned in two different ways: Tuning with the use of Database Engine Tuning Advisor and Tuning with the use of dta Utility Database tuning can be deeply explained on by the experts on remote dba support. Thank you for sharing this article.

    Andrew Thompson September 5, 2015 4:04 pm Reply
  • Nice article! As it is stated in the article that database tuning can be done through various ways. You can also find another ways to tune the database. If you have some problem in the steps of database tuning then you can take help of remote dba services, where the experts will give you correct guidance. Thank you for posting such a helpful article. Keep posting more such articles in future. Well done.

    Alexander Fernandise September 15, 2015 4:30 pm Reply

Leave a Comment

Your email address will not be published. Required fields are marked *