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