Enhance the Database Tuning by Engine Tuning Advisor
By using the dta.exe file, the database can perform tuning through the Engine Tuning Advisor. This Graphical User Interface can incorporate with the database command for smooth tuning sessions.
Initialize Tuning Advisor: In the initial stage, the database needs to be tuned with a moderator or system administrator. This db_owner performs the role of tuning databases and tables on its own. The administrator can take help of the SQL server or the dta utility. The extended stored procedure depends mainly on the xp_msver. As soon as the SQL server gets installed in your system, the database tuning runs by default. When the xp_msver remains absent, database administrators use the features of the hardware for its performance.
Introduce Tuning Advisor: After the initialization process, you may begin the activities of the Engine Tuning Advisor. To start from the Windows menu, you can go from the All Programs menu from the Start and then point the cursor to the Performance Tools from the Microsoft SQL server and finally select Database Engine Tuning Advisor. If you want to start direct from the SQL Server Management Studio, select the Tools menu and then click on Database Engine Tuning Advisor. The Tuning Advisor can also start its process from the Studio Query Editor. You can release a Transact-SQL script and then select the Analyze Query In Database Engine Tuning Advisor. If you are confused about the whole process, you can easily go through the remote dba services at remotedba.com.
Determine Tuning Events: You need to understand whether all the events can be tuned or not. You may check whether the workload reference table has been selected for tuning. You can identify whether the data table is very small within 10 pages or not, whether the specified time limit is sufficient for the tuning purpose or not. You are also required to produce a Show plan output from the event for tuning the database effectively. The Tuning Advisor always recommends a smaller workload. If your physical design structure and all the subsets of it are small in structure, the workload references need to be small too; otherwise the workload cannot be supported. If the dta utility can be used for tuning the events, the wrong databases can be listed under the –d argument in a sequence. Besides this, the transact SQL statements will not support the objects that can be tuned in the database.
Obtainable Tuning Options: There can be various tuning options available in the SQL server. You will have to check the dta utility for all the utility options. If you want to use all the GUI options, you may select F1 from Tuning Options tab. From the selected option you can determine the physical design structure, the duration of tuning can be specified, the disk space can be recommended, and all the unsupported tuning options can be rejected.
Select Tuning Advisor Report: Different analysis report can be obtained from the Tuning Advisor. The report names can be Index Detail Report, Index Usage Report, Statement Cost Range Report, Table Access Report, Workload Analysis Report, Column Access Report, Event Frequency Report and lots more.
Reduce Production Server Load: During the tuning activities, the recompilation of the server can take place. Therefore if the SQL server does not validate the plans, the production server can thus be eliminated. The tuning advisor can also take help of the Test Server to perform the actions. You will have to ensure that the test servers are present in both the SQL servers.
The session monitor can be used to evaluate all the tuning recommendations and thus the database tuning can be done effectively with the tuning advisor.