How Can You Run Database Tuning Through The Workload Process?

Tuning the database signifies that particular cluster of activities where the optimization can take place regarding the performance of the database. The Workloads can be used for database tuning by the Database Engine Tuning Advisor or by the DTA Utility. The transact-SQL statements oppose the database that needs to be tuned. You need to follow some simple steps to create the workload process for the database tuning.

 

Image Credit : www.novell.com

Creating The Workloads:

When you are using the SQL Server profiler, you can easily generate the trace file and also the trace workloads.

• You can go to the START menu , from there All Programs > MS SQL Server > Performance Tools and then finally select the SQL Server profiler
• You can also go to the Tools menu and then to the SQL Server Profiler from the SQL Server Management Studio

After launching the SQL Server profiler, you can easily create the trace table or the file using the tuning template.

Tuning The Database:

As mentioned earlier, the database can be tuned in both ways, by the graphical user interface Database Engine Tuning Advisor or by the DTA Utility.

Image Credit : www.quazoo.com

By Database Engine Tuning Advisor:

Step 1:

You need to settle on the database features in the initial stage. You may decide whether you need the advisor to modify or remove your scrutiny.

Step 2:

After that you have to create the workloads.

Step 3:

Then introduce the Database Engine Tuning Advisor and sign into the MS SQL Server.

Step 4:

For creating the new session, you can just type a name in the section of Session name of the General tab.

Step 5:

You can select either a file or a table for your workload as required.

Step 6:

In this stage you can select the database table by clicking on the arrow of Selected Tables.

Step 7:

You can save a copy of the tuning log for future references.

Step 8:

You can select the best tuning options from the available list.

Step 9:

Finally you may start the analysis by selecting the Start Analysis from the toolbar.

If you want to resume the action of the analysis you can immediately select the Stop Analysis or Stop Analysis (With Recommendations).

Image Credit : scriptingmysql.wordpress.com

By Using The DTA Utility:

When you are using the default settings of the DTA Utility, you need to determine the database feature, and then create the workload. After that the command can be provided with the database name, ID and password. Tuning can also be done within a specified duration and a name for a remote database. XML input file can also be essential if you are tuning with the DTA Utility function.

Viewing The Tuning Output :

For the purpose of viewing, the Session Monitor Window can be selected. If the existing session has been introduced or the new sessions of tuning are complete, the page of Recommendations will appear in front of you. You may either select the Partition Recommendations or the Index Recommendations. You can also save all the recommendations for your future reference from the Action menu. You can have a look on all the analysis and the reports of the GUI Database Engine Tuning Advisor.

Therefore, you can create the exclusive reports and analysis by implementing all the tuning recommendations. Regardless of the user interface you are using, you can definitely go forward for database tuning if you are aware of the process of tracing tables, files, and SQL statement through the workload process. If you are satisfied with your SQL server, you are all set for the process of database tuning.