Indexing Tool : Database Engine Tuning Advisor


Related article : Indexing Tools for Microsoft SQL Server (You might want to read the article before this one to get a better picture)

The other indexing tool available in SQL Server is the Database Engine Tuning Advisor. This tool allows SQL Server to analyze a workload from a file, a table, or the plan cache. The output of the DTA can assist in providing recommendations for indexing and configuring partitions for the workload. The chief benefit of using the tool is that it doesn’t require a deep understanding of the underlying databases to make the recommendations.

The following are some of the core capabilities that the DTA can provide:

  • Recommend the best mix of indexes for databases by using the query optimizer to analyze queries in a workload
  • Recommend aligned or nonaligned partitions for databases referenced in a workload
  • Recommend indexed views for databases referenced in a workload
  • Analyze the effects of the proposed changes, including index usage, query distribution among tables, and query performance in the workload
  • Recommend ways to tune the database for a small set of problem queries
  • Allow you to customize the recommendation by specifying advanced options, such as disk space constraints
  • Provide reports that summarize the effects of implementing the recommendations for a given workload
  • Consider alternatives in which you supply possible design choices in the form of hypothetical configurations for DTA to evaluate

Although the DTA has quite a few capabilities, there are also a number of limitations on the tools. The following are some of these limitations:

  • Not able to recommend indexes on system tables.
  • Cannot add or drop unique indexes or indexes that enforce primary key or unique constraints.
  • May provide variations in recommendations on some workloads. The DTA samples data while it executes, which will influence the recommendations.
  • Unable to tune database from SQL Server 7.0 or earlier.
  • Unable to tune trace tables on remote servers.
  • Constraints placed on tuning workloads can have a negative impact on suggestions if the tuning session exceeds the constraints.

NoteThe DTA often suffers a bad rap as an indexing tool. This is mostly because of abuse and misuse by others who have used it. When using the tool, be sure to validate any change that is recommended and test any changes thoroughly before applying them in a production environment.

Explaining the DTA

There are two ways in which users can interact with the DTA. These are the graphical user interface (GUI) and the command-line utility. Both of these methods offer most of the same capabilities. Depending on your comfort level, you can choose either.

The GUI tool, which you will use throughout most of this chapter, provides a wrapper for the DTA. It allows you to select from the available options, and it enables you to view the tuning sessions that were previously executed. If you want to view tuning results, the GUI is well-suited to the task. Tuning sessions can be configured and executed through the GUI.

The command-line utility provides the same capabilities as the GUI when it comes to configuring and executing sessions. The command-line utility can be configured through either switches or an XML configuration file. Both of these options allow database administrators (DBAs) and developers to build processes to automate tuning activities for reviewing and analyzing workloads and to build an index tuning process that allows the DBA to work with results instead of going through the motions of setting up and configuring the tuning sessions.

With both tools, two general areas of configuration need to occur. The first determines how the tuning session will interact and makes suggestions with the physical design structures (PDSs). The second determines which type of partitioning strategy the DTA should employ when trying to tune the database.

There are two parts to the options on how physical design structure suggestions will be generated. The first option you will want to configure is which type of physical design structure can be utilized in the tuning. The following are the options for this:

  • Indexes and indexed views
  • Indexes (default option)
  • Evaluate utilization of existing PDSs only
  • Indexed views
  • Nonclustered indexes

Along with the following options, the tuning session can also consider whether the filtered indexes can be included in the suggestions. The other PDS option determines which objects to keep within the database. This option can help ensure that the tuning recommendations do not adversely affect tuning that was previously tested and deployed. The following are the options for PDS items to retain in the database:

  • Do not keep any existing PDSs
  • Keep all existing PDSs (default option)
  • Keep aligned partitioning
  • Keep indexes only
  • Keep clustered indexes only

The other general option with when configuring DTA is how the tuning session will consider table partitioning. The following are the options for table partition:

  • No partitioning (default option)
  • Aligned partitioning
  • Full partitioning

Outside these options, there are some advanced options that can be configured. These options configure how long the tuning session will run and how much memory the session can utilize. Also, for the index DDL, there is an option to determine whether the script for indexes will include online index rebuild options.

 Note  Before following along in the next section, run the code in Listing 1. If the index in Listing 3 has been created, drop the index using the DROP INDEX statement provided in Listing 4.

Using the DTA GUI

As mentioned earlier in the chapter, one of the ways to interact with the DTA is through the GUI. In this section, you’ll look at a scenario demonstrating how to use the DTA for index tuning. There are a few methods for launching the tool. The first option is within SQL Server Management Studio (SSMS). Within SSMS, you can choose Tools

Database Engine Tuning Advisor from the menu bar. The other option is to open the SQL Server Database Engine Tuning Advisor from the Start menu.

After launching the DTA, you will be prompted to connect to a SQL Server instance. Once connected, the tool will open a new tuning session for configuration. Figure 3 shows a DTA session.

deta

Figure 3. General configuration screen from the Database Engine Tuning Advisor

In the session launch screen on the General options tab, there are a few things to configure initially. To start, there is the session name. The session name can be any value you desire. The default value includes your username with the date and time. Next select the type of workload that will be used. There are three options for the workload.

  • File: A file containing SQL Trace output, an XML configuration, or SQL scripts.
  • Table: SQL Server database table containing SQL Trace output. Before using the table, be sure the trace populating it has been completed.
  • Plan Cache: The plan cache of the SQL Server that the tuning session is connected to. This capability is new to SQL Server 2012 and provides a powerful mechanism to tune execution plans that are being used in your SQL Server environment.

Each of the workloads can be used to provide recommendations. Through each of these workload sources, there is an opportunity to tune pretty much any type of workload that is needed. For the purposes of this exercise, select the Plan Cache option.

The next step is to select the database and tables to tune. With large databases, it will be critical to select only the tables that are part of the workload and for which index recommendations are needed. When the DTA executes, it will generate statistics based on information in the table, and the fewer tables that need to be considered, the faster the tuning session can complete. Check the box in the “Select databases and tables to tune” section next to the AdventureWorks2014 database before continuing.

Caution  Do not use the DTA in your production SQL Server environment. The tool uses brute-force tactics to identify index recommendations and create hypothetical indexes to support this effort. Running the tool in production can adversely affect the performance of other workloads on the server. Consider running the DTA from a command line and on a remote SQL Server for analyzing production databases.

With the General options configured, the next step is to configure the Tuning Options settings. On the screen shown in Figure 4, deselect the “Limit tuning time” option. For the other options, leave them as the default selections. These should be as follows:

  • Physical Design Structures (PDS) to use in database: Indexes
  • Partitioning strategy to employ: No partitioning
  • Physical Design Structures (PDS) to keep in database: Keep all existing PDS

dbta2

Figure 4. Tuning Options configuration screen from Database Engine Tuning Advisor

The next step is to start the Database Engine Tuning Advisor. This can be accomplished through the toolbar or the menu, by selecting Actions Start Analysis. After starting the DTA, the Progress tab will open, as shown in Figure 5.

deta3

Figure 5. Progress screen from the Database Engine Tuning Advisor

After a few minutes the tuning session will complete, though this will depend entirely on your computer’s workload. With the indexes from Listing 1, the results should be similar to those in Figure 6. In these results, there is one recommendation. While the names will vary

in your environment, the recommendation should be as follows:

  • Index on OrderDate and then DueDate including CustomerID

deta4

Figure 6. Recommendations from the Database Engine Tuning Advisor

This index is similar to the suggestion previously found with the missing index DMOs. In situations where there are multiple recommendations provided, you will need to go through the same considerations that were part of reviewing the suggestions from the missing index DMOs, such as “Can the recommendations be consolidated?” To remove any item from the list of recommendations, simply deselect the check box, and it will not be included in any of the recommendation outputs.

At this point, there are a few options that can be used to apply the indexes.

  • Apply the indexes: To apply the indexes, select Actions in the menu bar and select Apply Recommendations. In the Apply Recommendations window that comes up, leave the default, Apply Now, selected and click OK.
  • Apply the indexes in the future: To apply the indexes in the future, select Actions in the menu bar and select Apply Recommendations. In the Apply Recommendations window that comes up, select “Schedule for later.” Alter the scheduled date as desired and click OK. This will create the SQL Agent job. Ensure the SQL Agent is running and the agent service account has the required permissions to apply the indexes.
  • Save recommendations: To save recommendations, click the Save Recommendations icon in the menu bar and press the key combination Ctrl+S; or, select Actions Save Recommendations in the menu bar.

If the recommendations are saved, they will create a script like the one in Listing 5. Before applying indexes from the DTA, it is recommended that the names of indexes be changed to match your organization’s index naming standards. Also, when it comes to statistics, these are generally not created. SQL Server will create statistics as needed behind the scenes, removing the need for you to build your own statistics.

By using the DTA through its GUI, you are able to make quick work of a workload. The recommendations returned provide a level of index tuning above using the missing index DMOs. In essence, they provide a brute-force indexing exercise to improve performance without improving code. Instead of spending many hours on tuning that can be resolved with a few new indexes, you can focus your time on performance tuning issues that are beyond just adding an index.

Note  When the DTA is terminated while processing, it will sometimes leave behind hypothetical indexes that were used while it was investigating possible indexes that could improve an environment. A hypothetical index is an index that contains only statistics and no data. These indexes can be identified through the is_hypothetical column in sys.indexes. If they exist in your environment, they should always be dropped.

Using the DTA Utility

The GUI isn’t the only way to use the DTA within your SQL Server environment. The other method is through the command line with the DTA utility. What DTA utility lacks in an interactive interface, it makes up for with the flexibility to leverage the DTA utility in scripts and automation.

The syntax for using the DTA utility, shown in Listing 6, includes a number of arguments. These arguments, defined in Table 5, allow the DTA utility to contain the same features and flexibility of the GUI. Instead of clicking through a number of screens, the configuration information is passed in through the arguments.

Table 5. DTA Utility Arguments

Argument

Description

-?

Returns help information, including a list of all arguments.

-A

Provides a time limit, in minutes, in which the DTA utility will spend tuning the workload. The default time limit is 8 hours, or 640 minutes. Setting the limit to 0 will result in an unlimited tuning session.

-a

After the workload is tuned, the recommendations are applied without further prompting.

-B

Specifies the maximum size, in megabytes, that recommended indexes can consume. By default, this value is set to either three times the current raw data size or the free space on attached disk drives plus raw data size, whichever is smaller.

-c

Maximum number of key columns that DTA will recommend in an index. This value defaults to 16. The restriction does not include INCLUDED columns.

-C

Maximum number of columns that DTA will recommend in an index. The value defaults to 16 but can be raised as high as 1024, the maximum columns allowed in an index.

-d

Identifies the database that the DTA session connects to when the session begins. Only a single database can be specified for this argument.

-D

Identifies the databases that the DTA session will tune the workload against. One or more databases can be specified for this argument. To add multiple databases to a session, either include all the database names in a comma-separated list in one argument or add one argument per database.

-e

Identifies the name of the logging table or file where the DTA session will output events that could not be tuned. When specifying a table name, use the three-part naming convention of [database_name].[schema_name].[table_name]. With an output file, the extension for the file should be .xml.

-E

Sets the database connection using a trusted connection. The required argument if -U is not used.

-F

Grants DTA permission to overwrite an output file if it already exists.

-fa

Identifies the types of physical design structures that the DTA session can include in the recommendations. The default value for this argument is IDX. The available values are as follows:

  • IDX_IV: Indexes and indexed views
  • IDX: Indexes only
  • IX: Indexed views only
  • NCL_IDX: Nonclustered indexes only

-fi

Allows the DTA session to include recommendations for filtered indexes.

-fk

Sets the limitations on the existing physical design structures that the DTA session can modify in the recommendations. The available values are as follows:

  • NONE: No existing structures
  • ALL: All existing structures
  • ALIGNED: All partition-aligned structures
  • CL_IDX: All clustered indexes on tables
  • IDX: All clustered and nonclustered indexes on tables

-fp

Determines whether partitioning recommendations can be included in the DTA session recommendations. The default value for this argument is NONE. The available values are as follows:

  • NONE: No partitioning
  • FULL: Full partitioning
  • ALIGNED: Aligned partitioning

-fx

Limits the DTA session to only including recommendations to drop existing physical design structures. Lightly used indexes in the session are evaluated, and recommendations for dropping them are provided. This argument cannot be used with the arguments -fa, -fp, and -fk ALL.

-ID

Sets a numerical identifier for the DTA session. Either this argument or -s must be specified.

-ip

Set the source of the workload for the DTA session to the plan cache. The top –n plan cache events for the databases specified with argument –D are analyzed.

-ipf

Sets the source of the workload for the DTA session to the plan cache. The top –n plan cache events for all databases are analyzed.

-if

Sets the source of the workload for the DTA session to a file source. The path and file name are passed in through this argument. The file must be SQL Server Profiler trace file (trc), SQL file (sql), or SQL Server trace file (log).

-it

Sets the source of the workload for the DTA session to a table. When specifying a table name, use the three-part naming convention of [database_name].dbo.[table_name]. The schema for the table must be dbo.

-ix

Identifies an XML file containing the configuration information for the DTA session. The XML file must conform to the DTASchema.xsd (which is located at http://schemas.microsoft.com/sqlserver/2004/07/dta/dtaschema.xsd).

-m

Sets the minimum percentage of improvement that a recommendation must provide.

-n

Sets the number of events in the workload that the DTA session should tune. When specified for a trace file, the order of the events selected is based on the decreasing order of duration.

-N

Determines whether the physical design structures are created online or offline. The available values are as follows:

  • OFF: No objects are created online.
  • ON: All objects are created online.
  • MIXED: Objects are created where possible.

-of

Configures the DTA session to output the recommendations in a T-SQL format in the path and file specified.

-or

Configures the DTA session to output the recommendations to a report in an XML format. When a file name is not provided, a file name based on the session (-s) name will be used.

-ox

Configures the DTA session to output the recommendations in an XML format in the path and file specified.

-P

Sets the password to be used for the SQL login in the database connection.

-q

Sets the DTA session to execute in quiet mode.

-rl

Configures the reports that will be generated by the DTA session. One or more reports can be selected in a comma-separated list. The available values are as follows:

  • ALL: All analysis reports
  • STMT_COST: Statement cost report
  • EVT_FREQ: Event frequency report
  • STMT_DET: Statement detail report
  • CUR_STMT_IDX: Statement-index relations report (current configuration)
  • REC_STMT_IDX: Statement-index relations report (recommended configuration)
  • STMT_COSTRANGE: Statement cost range report
  • CUR_IDX_USAGE: Index usage report (current configuration)
  • REC_IDX_USAGE: Index usage report (recommended configuration)
  • CUR_IDX_DET: Index detail report (current configuration)
  • REC_IDX_DET: Index detail report (recommended configuration)
  • VIW_TAB: View-table relations report
  • WKLD_ANL: Workload analysis report
  • DB_ACCESS: Database access report
  • TAB_ACCESS: Table access report
  • COL_ACCESS: Column access report

-S

Sets the instance of SQL Server to be used for the DTA session.

-s

Sets the name of the DTA session.

-Tf

Identifies the name of a path and file containing a list of tables to be used for tuning. The file should contain one table per line using the three-part naming convention. After each table name, the number of rows can be specified to tune the workload for a scaled version of the table. If -Tf and -Tl is omitted, the DTA session will default to using all tables.

-Tl

Sets a list of tables to be used for tuning. Each table should be listed using the three-part naming convention, with each table name separated by a comma. If -Tf and -Tl are omitted, the DTA session will default to using all tables.

-U

Sets the username to be used for the SQL login in the database connection. The required argument if -E is not used.

-u

Launches the GUI interface for the DTA with all of the configuration values specified the to the DTA utility.

-x

Starts the DTA session and exists upon completion.

Using the DTA utility is fairly easy. You’ll look at two scenarios of using the tool that provide different outcomes. In the first scenario, you’ll use the DTA utility to recommend indexing changes with allowing only nonclustered indexing changes. For the second scenario, the DTA utility will be configured to recommend any change to the indexing that would improve the performance of the workload. In both scenarios, you’ll use the plan cache for SQL Server as the workload source. To populate the plan cache, execute the query in Listing 7.

For the first scenario, you’ll build a command-line script similar to the one shown in Listing 8. For your environment, the server name (-S) will be different. The rest, however, will be the same. The database (-D and –d arguments) will be AdventureWorks2014. The source of the workload will be the plan cache (-ip argument). The name of the session (-s argument) is “First Scenario”.

With the DTA utility syntax prepared, the next step is to execute the script through the Command Prompt window. Depending on your SQL Server instance and the amount of information in the plan cache, the execution may take a few minutes. When it completes, the output in the Command Prompt window will look similar to the output shown in Figure 7. This output indicates that the file C:\Temp\First Scenario.sql contains the recommendations for tuning the query in Listing 7.

cmd1

Figure 7. Command Prompt window for first scenario

Based on the arguments passed into the DTA utility and the current workload, the recommendation from the first scenario tuning session includes the creation of two nonclustered indexes and statistics on two columns, shown in Listing 9. These indexes function as covering indexes for the queries in Listing 7; as a result, the key lookup is no longer required as part of the execution plan. The statistics provide information that SQL Server can use to build good plans for queries on the columns used in the query.

Note  Listing 9 creates the dbo.SalesOrderDetail table.

The downside to the arguments that were selected in the first scenario is that there isn’t any information included that helps determine the value in adding this index and the statistics. For the next scenario, you’ll learn how to obtain that information along with moving deeper into providing recommendations on the physical structure of your databases.

To begin the next scenario, you’ll use the same database and query. The arguments, though, will be modified slightly to accommodate the new goals, as shown in Listing 10. First, you’ll change the name of the session (-s) to “Second Scenario”. Next, change the allowed physical structure changes (argument –fa) from nonclustered indexes only (NCL_IDX) to indexes and indexed views (IDX_IV). The final change, for the reporting output, is to add the report list (argument –rl) to the script with the all-analysis reports (ALL) option.

Executing the DTA utility using the second scenario produces entirely different results from the first scenario. Instead of recommending nonclustered indexes, the second scenario recommends a change in the clustered key columns. With this solution, the DTA session identified the SalesOrderID column as the column frequently used to access data and recommended that as the clustered index. Listing 11 shows these recommendations.

The one other difference with the second scenario is the creation of an XML report file. The session used the ALL option for the –rl argument, which includes all the reports listed for the argument in Table 5. These reports provide information regarding the statements that were tuned, the costs associated with the statements, the amount of improvement the recommendations provide, and much more (Figure 8). Through these reports, you are provided the information needed to make decisions about which recommendations to apply to your databases.

xml

Figure 8. Sample report output from DTA utility

One thing to remember with the last two scenarios is that the table being tuned was tuned in a vacuum. There were no constraints or foreign key relationships on the table that need to be considered. In the real world, this won’t be the way your database is designed, and foreign key relationships will affect how recommendations are provided. Also, the load for these scenarios contained only two queries. When building your workloads, be sure to use a sample that is representative of your environment.

Through the DTA scenarios provided in this section, you’ve laid a foundation for using tools in your index tuning activities. Not only can the DTA identify missing indexes, but, given a workload, it can also help identify where clustered indexes and partitioning can assist with performance. The physical changes that DTA can provide could be extremely useful when you quickly need to address performance issues with a database.

Excerpt from the book “Expert Performance Indexing in SQL Server by Jason Strate & Grant Fritchey published by APRESS

An Excellent read if you are interested.

 

For all your application development needs, visit www.verbat.com for a fiscally conscious proposal that meets your needs ( So I can keep this blog going as well!!!!)

Alternatively click through the link   if you found this article interesting. (This will help the companies Search engine rankings)

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s