1. Computing

Discuss in my forum

Database Engine Tuning Advisor in SQL Server 2012

By

Database Engine Tuning Advisor in SQL Server 2012
SQL Server 2012’s Database Engine Tuning Advisor (DETA) provides database administrators with a powerful way to tune your database for your specific environment. DETA allows you to design a customized workload that mimics your operational environment and then analyze that workload to determine the optimal configuration settings for your SQL Server instance. DETA’s recommendations include adding normal and clustered indexes to your database, partitioning tables and creating indexed views, where appropriate.

Getting Started with DETA

To run DETA for the first time, you must be a member of the sysadmin fixed server role, as DETA must create several system-wide tables in the msdb database. Once this first-time initialization is complete, any user who is a member of the db_owner database role for a particular database may use DETA to tune that database.

You can run DETA several different ways, depending upon your current location in the server environment:
  • From the Start menu choose Microsoft SQL Server 2012, then select Performance Tools, and finally click on Database Engine Tuning Advisor.
  • If you are within SQL Server Management Studio, select Database Engine Tuning Advisor from the SSMS Tools menu.
  • If you are in the SQL Server Management Studio Query Editor, open a Transact-SQL script and select the text that you wish to analyze. Right-click on that text and choose Analyze Query in Database Engine Tuning Advisor to get started in DETA with the selected workload.
  • If you are running SQL Server Profiler, choose Database Engine Tuning Advisor from the Tools menu.

Choosing a Tuning Workload

DETA provides environment-specific recommendations for tuning SQL Server performance based upon an administrator-specified workload. It is critically important to select a workload that is representative of the environment you are seeking to tune, as DETA will make design recommendations that optimize performance for the provided workload. Database administrators have several choices for providing a DETA workload:
  • SQL Server Profiler Traces -- One of the best ways to design a SQL Server workload is to create a database trace using SQL Server Profiler. With this approach, you use the Profiler tool to capture database activity during a time where you can either capture a representative workload or simulate the expected workload. SQL Profiler will capture this activity in a trace file that may be used as an input to DETA.
  • Custom-designed workload -- Alternatively, you may write your own Transact-SQL script that contains the queries you want to optimize your database to perform. You can create this script using Query Editor or the text editor of your choice.
  • Use the SQL Server plan cache -- SQL Server maintains a cache of query execution plans to improve the operational efficiency of the database server. This cache contains information on recently executed SQL queries and may be used to create a DETA workload. This is the easiest way to get started with DETA, as it does not require any special administrator configuration. However, it should only be used if you are relatively certain that recent database activity reflects the conditions you wish to optimize your database against.

Running DETA

Once you’ve selected your workload and started DETA using one of the methods described earlier, follow this process to run DETA:
  1. Provide the connection details for your database server in the Connect to Server window. Click Connect to open the database connection.
  2. Select the appropriate radio button in the Workload section of the window and, if applicable, browse to the file you wish to use for your workload. This file may be either a SQL Trace, XML file or SQL file. It's essential to choose a workload representative of normal activity for your database, as DETA will optimize the database's performance based upon this information.
  3. Confirm the database that DETA should use for workload analysis using the drop-down box.
  4. Using the checkboxes in the grid at the bottom section of the screen, select the database(s) and/or table(s) that you wish to tune.
  5. Click the Start Analysis button and wait for the database tuning analysis to complete.
  6. Review the recommendations presented by DETA and implement those that you deem appropriate.
  1. About.com
  2. Computing
  3. Databases
  4. SQL Server
  5. Database Engine Tuning Advisor in SQL Server 2012

©2014 About.com. All rights reserved.