Chapter 5

Database Engine Tuning Advisor

SQL Server’s performance largely depends upon having proper indexes on the database tables. However, as the workload and data change over time, the existing indexes may not be entirely appropriate, and new indexes may be required. The task of deciding upon the correct indexes is complicated by the fact that an index change that benefits one set of queries may be detrimental to another set of queries.

To help you through this process, SQL Server provides a tool called the Database Engine Tuning Advisor. This tool helps identify an optimal set of indexes and statistics for a given workload without requiring an expert understanding of the database schema, workload, or SQL Server internals. It can also recommend tuning options for a small set of problem queries. In addition to the tool’s benefits, I cover its limitations in this chapter, because it is a tool that can cause more harm than good if used incorrectly.

In this chapter, I cover the following topics:

• How the Database Engine Tuning Advisor works
• How to use the Database Engine Tuning Advisor on a set of problematic queries for index recommendations, including how to define traces
• The limitations of the Database Engine Tuning Advisor

Database Engine Tuning Advisor Mechanisms

You can run the Database Engine Tuning Advisor directly by selecting Microsoft SQL Server 2012 Performance Tools Database Engine Tuning Advisor. You can also run it from the command prompt (dta.exe), from SQL Profiler (Tools Database Engine Tuning Advisor), from a query in Management Studio (highlight the required query, and select Query Analyze Query in Database Engine Tuning Advisor), or from Management Studio (select Tools Database Engine Tuning Advisor). Once the tool is opened and you’re connected to a server, you should see a window like the one in Figure 5-1. I’ll run through the options to define and run an analysis in this section and then follow up in the next session with some detailed examples.

The Database Engine Tuning Advisor is already connected to a server. From here, you begin to outline the workload and the objects you want to tune. Creating a session name is necessary to label the session for documentation purposes. Then you need to pick a workload. The workload can come from a trace file, a table, or, new with SQL Server 2012, you can use the queries that exist in the plan cache. Finally you need to browse to the appropriate location. The workload is defined depending on how you launched the Database Engine Tuning Advisor. If you launched it from a query window, you would see a Query radio button, and the File and Table radio buttons would be disabled. You also have to define the Database for Workload Analysis setting and finally select a database to tune.

 Tip The Database Engine Tuning Advisor recommends indexed views only for platforms that support them.
SQL Server 2008 Enterprise Edition does, but Standard Edition doesn’t.

When you select a database, you can also select individual tables to be tuned by clicking the drop-down box on the right side of the screen; you’ll see a list of tables like those shown in Figure 5-2.

Once you define the workload, you need to select the Tuning Options tab, which is shown in Figure 5-3.

You define the length of time you want the Database Engine Tuning Advisor to run by selecting Limit Tuning Time and then defining a date and time for the tuning to stop. The longer the Database Engine Tuning Advisor runs, the better recommendations it should make. You pick the type of physical design structures to be considered for creation by the Database Engine Tuning Advisor, and you can also set the partitioning strategy so that the Tuning Advisor knows whether it should consider partitioning the tables and indexes as part of the analysis. Just remember, partitioning isn’t necessarily a desirable outcome if your data and structures don’t warrant it. Finally, you can define the physical design structures that you want left alone within the database. Changing these options will narrow or widen the choices that the Database Engine Tuning Advisor can make to improve performance.

You can click the Advanced Options button to see even more options, as shown in Figure 5-4.

This dialog box allows you to limit the space of the recommendations and the number of columns that can be included in an index. You decide if you want to include plan cache events from every database on the system. Finally, you can define whether the new indexes or changes in indexes are done as an online or offline index operation.

Once you’ve appropriately defined all of these settings, you can start the Database Engine Tuning Advisor by clicking the Start Analysis button. The sessions created are kept in the msdb database for any server instance that you run the Database Engine Tuning Advisor against. It displays details about what is being analyzed and the progress made, which you can see in Figure 5-5.

You’ll see more detailed examples of the progress displayed in the example analysis in the next session.

After the analysis completes, you’ll get a list of recommendations (visible in Figure 5-6), and a number of reports become available. Table 5-1 describes the reports.

Table 5-1. Database Engine Tuning Advisor Reports

Report Name	Report Description
Column Access	Lists the columns and tables referenced in the workload
Database Access	Lists each database referenced in the workload and percentage of workload statements for each database
Event Frequency	Lists all events in the workload ordered by frequency of occurrence
Index Detail (Current)	Defines indexes and their properties referenced by the workload
Index Detail (Recommended)	Is the same as the Index Detail (Current) report but shows the information about the indexes recommended by the Database Engine Tuning Advisor
Index Usage (Current)	Lists the indexes and the percentage of their use referenced by the workload
Index Usage (Recommended)	Is the same as the Index Usage (Current) report but from the recommended indexes
Statement Cost	Lists the performance improvements for each statement if the recommendations are implemented
Statement Cost Range	Breaks down the cost improvements by percentiles to show how much benefit you can achieve for any given set of changes; these costs are estimated values provided by the optimizer.
Statement Detail	Lists the statements in the workload, their cost, and the reduced cost if the recommendations are implemented
Statement-to-Index Relationship	Lists the indexes referenced by individual statements; current and recommended versions of the report are available.
Table Access	Lists the tables referenced by the workload
View-to-Table Relationship	Lists the tables referenced by materialized views
Workload Analysis	Gives details about the workload, including the number of statements, the number of statements whose cost is decreased, and the number where the cost remains the same

Database Engine Tuning Advisor Examples

The best way to learn how to use the Database Engine Tuning Advisor is to use it. It’s not a terribly difficult tool to master, so I recommend opening it and getting started.

Tuning a Query

You can use the Database Engine Tuning Advisor to recommend indexes for a complete database by using a workload that fairly represents all SQL activities. You can also use it to recommend indexes for a set of problematic queries.

To learn how you can use the Database Engine Tuning Advisor to get index recommendations on a set of problematic queries, say you have a simple query that is called rather frequently. Because of the frequency, you want a quick turnaround for some tuning. This is the query:

SELECT soh.DueDate,

        soh.CustomerID,

  soh.Status

FROM  Sales.SalesOrderHeader AS soh

WHERE  soh.DueDate BETWEEN '1/1/2008' AND '2/1/2008';

To analyze the query, right-click it in the query window, and select Analyze Query in the Database Engine Tuning Advisor. The advisor opens with a window where you can change the session name to something meaningful. In this case, I chose Report Query Round 1 – 11/17/2011. The database and tables don’t need to be edited. The first tab, General, will look like Figure 5-6 when you’re done.

Because this query is important and tuning it is extremely critical to the business, I’m going to change some settings on the Tuning Options tab to try to maximize the possible suggestions. For the purposes of the example, I’m going to let the Database Engine Tuning Advisor run for the default of one hour, but for bigger loads or more complex queries, you might want to consider giving the system more time. I’m going to select the Include Filtered Indexes check box so that if a filtered index will help, it can be considered. I’m also going to switch the Partitioning Strategy to Employ setting from No Partitioning to Full Partitioning. Finally, I’m going to allow the Database Engine Tuning Advisor to come up with structural changes if it can find any that will help by switching from Keep All Existing PDS to Do Not Keep Any Existing PDS. Once completed, the Tuning Options tab will look like Figure 5-7.

After starting the analysis, the progress screen should appear. Although the settings were for 15 minutes of evaluations, it took only about a minute for the DTA to evaluate this query. The initial recommendations were not a good set of choices. As you can see in Figure 5-8, the Database Engine Tuning Advisor has recommended dropping a huge swath of indexes in the database. This is not the type of recommendation that you want when running the tool.

This is because the Database Engine Tuning Advisor assumes that the load being tested is the full load of the database. If there are indexes not being used, then they should be removed. This is a best practice and one that should be implemented on any database. However, in this case, this is a single query, not a full load of the system. To see whether the advisor can come up with a more meaningful set of recommendations, you must start a new session.

This time, I’ll adjust the options so that the Database Engine Tuning Advisor will not be able to drop any of the existing structure. This is set on the Tuning Options tab (shown earlier in Figure 5-7). There I’ll change the Physical Design Structure (PDS) to Keep in Database setting from Do Not Keep Any Existing PDS to Keep All Existing PDS. I’ll keep the running time the same because the evaluation worked well within the time frame. After running the Database Engine Tuning Advisor again, it finishes in less than a minute and displays the recommendations shown in Figure 5-9.

The first time through, the Database Engine Tuning Advisor suggested dropping most of the indexes on the tables being tested and a bunch of the related tables. This time it suggests creating a covering index on the columns referenced in the query. As outlined in Chapter 4, a covering index can be one of the best performing methods of indexing. The Database Engine Tuning Advisor was able to recognize that an index with all the columns referenced by the query, a covering index, would perform best.

Once you’ve received a recommendation, you should closely examine the proposed T-SQL command. The suggestions are not always helpful, so you need to evaluate them to be sure. Assuming the examined recommendation looks good, you’ll want to apply it. Select Actions Evaluate Recommendations. This opens a new Database Engine Tuning Advisor session and allows you to evaluate whether the recommendations will work using the same measures that made the recommendations in the first place. All of this is to validate that the original recommendation has the effect that it claims it will have. The new session looks just like a regular evaluation report. If you’re still happy with the recommendations, select Actions Apply Recommendation. This opens a dialog box that allows you to apply the recommendation immediately or schedule the application (see Figure 5-10).

If you click the OK button, the Database Engine Tuning Advisor will apply the index to the database where you’ve been testing queries (see Figure 5-11).

After you generate recommendations, you may want to, instead of applying them on the spot, save the T-SQL statements to a file and accumulate a series of changes for release to your production environment during scheduled deployment windows. Remember that applying indexes to tables, especially large tables, can cause a performance impact to processes actively running on the system while the index is being created.

Although getting index suggestions one at a time is nice, it would be better to be able to get large swaths of the database checked all at once. That’s where tuning a trace workload comes in.

Tuning a Trace Workload

Capturing a trace from the real-world queries that are running against a production server is a way to feed meaningful data to the Database Engine Tuning Advisor. (Capturing traces will be covered in Chapter 15.) The easiest way to define a trace for use in the Database Engine Tuning Advisor is to implement the trace using the Tuning template. Start the trace on the system you need to tune. I generated an artificial load by running queries in a loop from the PowerShell sqlps.exe command prompt. This is the PowerShell command prompt with the SQL Server configuration settings. It gets installed with SQL Server.

In order to find something interesting, I’m going to create one stored procedure with an obvious tuning issue:

CREATE PROCEDURE dbo.uspProductSize

AS

SELECT p.ProductID,

  p.Size

FROM Production.Product AS p

WHERE p.Size = '62';

Here is the very simple script I used. You’ll need to adjust the connection string for your environment. After you have downloaded the file to a location, you’ll be able to run it by simply referencing the file and the full path through the command prompt. You may run into security issues since this is an unsigned, raw script. Follow the help guidance provided in that error message if you need to (queryload.ps1):

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

# Get the connection

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlConnection.ConnectionString =

"Server=DOJORANDORI;Database=AdventureWorks2008R2;Integrated

Security=True"

# Load Product data

$ProdCmd = New-Object System.Data.SqlClient.SqlCommand

$ProdCmd.CommandText = "SELECT ProductID FROM Production.Product"

$ProdCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

$SqlAdapter.SelectCommand = $ProdCmd

$ProdDataSet = New-Object System.Data.DataSet

$SqlAdapter.Fill($ProdDataSet)

# Load the Employee data

$EmpCmd = New-Object System.Data.SqlClient.SqlCommand

$EmpCmd.CommandText = "SELECT BusinessEntityID FROM

HumanResources.Employee"

$EmpCmd.Connection = $SqlConnection

$SqlAdapter.SelectCommand = $EmpCmd

$EmpDataSet = New-Object System.Data.DataSet

$SqlAdapter.Fill($EmpDataSet)

# Set up the procedure to be run

$WhereCmd = New-Object System.Data.SqlClient.SqlCommand

$WhereCmd.CommandText = "dbo.uspGetWhereUsedProductID

@StartProductID = @ProductId, @CheckDate=NULL"

$WhereCmd.Parameters.Add("@ProductID",[System.Data.SqlDbType]"Int")

$WhereCmd.Connection = $SqlConnection

# And another one

$BomCmd = New-Object System.Data.SqlClient.SqlCommand

$BomCmd.CommandText = "dbo.uspGetBillOfMaterials @StartProductID =

@ProductId, @CheckDate=NULL"

$BomCmd.Parameters.Add("@ProductID",[System.Data.SqlDbType]"Int")

$BomCmd.Connection = $SqlConnection

# And one more

$ManCmd = New-Object System.Data.SqlClient.SqlCommand

$ManCmd.CommandText = "dbo.uspGetEmployeeManagers @BusinessEntityID

=@EmpId"

$ManCmd.Parameters.Add("@EmpId",[System.Data.SqlDbType]"Int")

$ManCmd.Connection = $SqlConnection

# And the special

$SpecCmd = New-Object System.Data.SqlClient.SqlCommand

$SpecCmd.CommandText = "dbo.uspProductSize"

$SpecCmd.Connection = $SqlConnection

# Loop forever

while(1 -ne 0)

{

  foreach($row in $ProdDataSet.Tables[0])

  {

    $SqlConnection.Open()

    $ProductId = $row[0]

    $WhereCmd.Parameters["@ProductID"].Value = $ProductId

    $WhereCmd.ExecuteNonQuery() | Out-Null

    $SqlConnection.Close()

    foreach($row in $EmpDataSet.Tables[0])

    {

      $SqlConnection.Open()

      $EmpId = $row[0]

      $ManCmd.Parameters["@EmpID"].Value = $EmpId

      $ManCmd.ExecuteNonQuery() | Out-Null

      $SqlConnection.Close()

    }

    $SqlConnection.Open()

    $BomCmd.Parameters["@ProductID"].Value = $ProductId

    $BomCmd.ExecuteNonQuery() | Out-Null

    $SpecCmd.ExecuteNonQuery() | Out-Null

    $SqlConnection.Close()

  }

}

Note For more information on PowerShell, check out Pro Windows PowerShell by Hristo Deshev (Apress, 2008).

Once you’ve created the trace file, open the Database Engine Tuning Advisor. It defaults to a file type, so you’ll only have to browse to the trace file location. As before, you’ll want to select the AdventureWorks2008R2 database as the database for workload analysis from the drop-down list. To limit the suggestions, also select AdventureWorks2008R2 from the list of databases at the bottom of the screen. Set the appropriate tuning options, and start the analysis. This time, it will take more than a minute to run (see Figure 5-12).

As you can see, simply passing any number to the uspGetEmployeeManagers procedure, and others, can generate instances where no tables were referenced. The processing runs for about 15 minutes on my machine. Then it generates output, shown in Figure 5-13.

After running all the queries through the Database Engine Tuning Advisor, the Tuning Advisor came up with a suggestion for a new statistic to increase performance. Personally, I would have suggested adding an index, not a statistic, but you can see how suggestions are derived from the load presented to the Database Engine Tuning Advisor.

Tuning from the Procedure Cache

Introduced in SQL Server 2012 is the ability to use the query plans that are stored in the cache as a source for tuning recommendations. The process is very simple. There’s just one more choice on the General page that lets you choose that as a source for the tuning effort, as shown in Figure 5-14.

All other options behave exactly the same way as previously outlined in this chapter. The processing time is radically less than when the Tuning Advisor processes a workload. It has only the queries in cache to process, so, depending on the amount of memory in your system, this can be a very short list. The results from processing my cache suggested two indexes based on some system-generated statistics. These are estimated to enhance performance by about 45 percent, as you can see in Figure 5-15.

This gives you one more mechanism to try to tune your system in an automated fashion.

Database Engine Tuning Advisor Limitations

The Database Engine Tuning Advisor recommendations are based on the input workload. If the input workload is not a true representation of the actual workload, then the recommended indexes may sometimes have a negative effect on some queries that are missing in the workload. But most importantly, as you saw in the second example of this chapter, the Database Engine Tuning Advisor may not recognize possible tuning opportunities. It has a sophisticated testing engine, but in some scenarios, its capabilities are limited.

For a production server, you should ensure that the SQL trace includes a complete representation of the database workload. For most database applications, capturing a trace for a complete day usually includes most of the queries executed on the database, although there are exceptions to this. Be sure you understand your load and what’s needed to capture it appropriately. A few of the other considerations/limitations with the Database Engine Tuning Advisor are as follows:

• Trace input using the SQL:BatchCompleted event: As mentioned earlier, the SQL trace input to the Database Engine Tuning Advisor must include the SOL:BatchCompleted event; otherwise, the wizard won’t be able to identify the queries in the workload.
• Query distribution in the workload: In a workload, a query may be executed multiple times with the same parameter value. Even a small performance improvement to the most common query can make a bigger contribution to the performance of the overall workload, compared to a large improvement in performance of a query that is executed only once.
• Index hints: Index hints in a SQL query can prevent the Database Engine Tuning Advisor from choosing a better execution plan. The wizard includes all index hints used in a SQL query as part of its recommendations. Because these indexes may not be optimal for the table, remove all index hints from queries before submitting the workload to the wizard, bearing in mind that you need to add them back in to see whether they do actually improve performance.

Summary

As you learned in this chapter, the Database Engine Tuning Advisor is a useful tool for analyzing the effectiveness of existing indexes and recommending new indexes for a SQL workload. As the SQL workload changes over time, you can use this tool to determine which existing indexes are no longer in use and which new indexes are required to improve performance. It can be a good idea to run the wizard occasionally just to check that your existing indexes really are the best fit for your current workload. It also provides many useful reports for analyzing the SQL workload and the effectiveness of its own recommendations. Just remember that the limitations of the tool prevent it from spotting all tuning opportunities. Also remember that the suggestions provided by the DTA are only as good as the input that you provide to it. If your database is in bad shape, this tool can give you a quick leg up. If you’re already monitoring and tuning your queries regularly, you may see no benefit from the recommendations of the Database Engine Tuning Advisor.

Frequently, you will rely on nonclustered indexes to improve the performance of a SQL workload. This assumes that you’ve already assigned a clustered index to your tables. Because the performance of a nonclustered index is highly dependent on the cost of the bookmark lookup associated with the nonclustered index, you will see in the next chapter how to analyze and resolve a bookmark lookup.