Introduction

In a previous chapter, we learned how to use the Tuning Advisor to analyze queries and receive recommendations about indexes, partitions and statistics. In this new chapter, we will learn how to use the command line tool called DTA. The DTA is the command line of the Tuning Advisor.

The DTA is a very powerful tool that can be used to automate some tuning tasks. It can be used combined with the SQL Agent, SSIS, or customized and external tools like programs made in C# or Java.

In this article, we will show how to use this tool.

Requirements

• The Tuning Advisor, which is included in all the SQL Server Editions except in the Express editions

• We are using SQL Server 2014, but earlier versions can be used
• To detect the SQL Server edition, you can use this query:
  1 2 3

  select SERVERPROPERTY('Edition') AS Edition

• You will also need the AdventureWorks database installed. In our example, the AdventureWorks2014 is being used

Getting started

1. We are going to drop an existing index of the Person.person table of the AdventureWorks2014 database in order to force some recommendations:
   1 2 3 4

   DROP INDEX [IX_Person_LastName_FirstName_MiddleName] ON [Person].[Person] GO

2. In the SQL Server Management Studio save this query in a file named input.sql. This query will be analyzed by the DTA and after the analysis, some suggestion will be created:
   1 2 3 4 5 6 7 8

   SELECT BusinessEntityID, FirstName, MiddleName, LastName from Person.Person where LastName = 'Allison'     SELECT COUNT(*) FROM Person.Person

3. Now open the command prompt (cmd):

   
   

   Figure 1. The command Prompt

4. Run this query:

   dta -S ServerName -E -D AdventureWorks2014 -if c:\script2\input.sql -F -of c:\script2\scriptrec.sql -A 0 -fa IDX_IV -s Mysession

5. We are now going to explain the arguments used.

   -S – ServerName is used to connect to the SQL Server. ServerName is the name of the SQL Server instance. If your instance is not a default instance, use Servername\Instancename.

   -E – This argument is used to authenticate using Windows Authentication. It will use the current Windows user to connect to the database. If you want to use SQL Authentication, you can use the argument –U and specify the SQL Server Login and –P with the SQL Server Login password. For example, if the SQL Login is John and the password is M&%#1!aa, you will use the following arguments:

   -U John –P M&%#1!aa

   -D – Is used to specify the SQL Server database. In this example, the AdventureWorks2014 is used. If you want to set multiple databases, you can use the following arguments:

   -D databasename1,databasename2,databasename3

   –if – This argument is used to specify the Workload file. We created a file in the step 2. This Workload file can be a .sql file like the one created in step 2 or it can be a trace file (see our article A great tool to create SQL Server Indexes for more information about trace files). IF means input file.

   -F – This argument is used to overwrite an existing output file.

   –of – This argument is used to specify the output path and file with the recommendations like indexes, partitions or statistics. In this example, we are storing the recommendations in the c:\script2\scriptrec.sql path. OF means output file.

   -A – This is used to limit the time for tuning in minutes. If the workload is big, it can take a lot time to analyze the information. It is recommended to limit the time if the workload is high. Otherwise, the analysis will take forever. By default, the value is 0 which is an unlimited time.

   –fa – When the workload is big, it is a good practice to filter by type of objects to check. In this example, we are using IDX_IV, which means index and index view. IDX means index only and IV index view only. Finally, the option NCL_IDX means non-cluster index only.

   -s – Is used to assign a session name.

6. If you run the command line and everything is OK, you will receive a message that tuning session is successfully created:

   
   

   Figure 2. The command line results

7. Once executed your command you can check the output file. In this example, the output file is the scriptrec.sql file:

   
   

   Figure 3. The DTA Command line recommendation

8. As you can see in step 7, the command line recommends creating a new non-clustered index in the table Person.Person. The final step is to run the script generated with the recommendations. Check our article about tuning Wizard about how to choose indexes. Try to apply indexes only in frequently used queries. An excessive number of indexes is not good either. To analyze the indexes required, it is good to use the reports. In order to include all the reports, you can use the –rl ALL to include all the reports. You can use the argument –rl STMT_COST to see the statement cost report and –rl EVT_FREQ for the event frequency report. For a complete list of all the reports available, check the references.

9. In order to run the reports, run this query:

   dta -S ServerName -E -D AdventureWorks2014 -if c:\script2\input.sql -F -of c:\script2\scriptrec.sql -A 0 -fa IDX_IV -s Mysession2 –rl ALL

10. Once you run you will have a message similar to this one:

    
    

    Figure 4. The report message

11. The command line will indicate where the reports were stored. The reports are stored in a txt file:

    
    

    Figure 5. The report file

12. If you open the file, you will notice that it is an XML file:

    
    

    Figure 6. The xml report information

    If you have XML experience, it is very easy to read the XML file. The first report name is StatementCostReport. This report says that the query SELECT COUNT(*) from person.person will improve 32.38 % with the recommendation.

13. The second report in the same file is the Event Weight Report. It shows the weight of the queries, which is used to measure the frequency of use. The more frequency, the more recommendable to create the index. The other report is the Statement Detail Report. It shows the cost of the query. SQL Server measure the performance using the cost. The record shows the current cost and the recommended cost:

    

    Figure 7. More xml reports.

14. Finally, we will filter the tables to analyze. In the AdventureWorks2014 database we want just to analyze the tables with more rows. We are going to use this query to count the number of rows of all the tables in the AdventureWorks2014 database:
    1 2 3 4

    select 'select count(*) as count'+[TABLE_NAME]+' from '+[TABLE_SCHEMA]+'.'+[TABLE_NAME] from [INFORMATION_SCHEMA].[TABLES]

15. The query will generate a set of queries to get the number of rows of all the tables of a database:

    
    

    Figure 8. The queries generated

16. If you copy the queries and run them, you will have the number of rows of each table:

    
    

    Figure 9. The number of rows per table

17. The tables with more than 100,000 rows are [Sales].[SalesOrderDetail] and [production].[TransactionHistory]

18. Run this query to Analyze just the 2 tables detected in step 17:

    dta -S ServerName -E -D AdventureWorks2014 -if c:\script2\input.sql -F -of c:\script2\scriptrec3.sql -A 0 -fa IDX_IV -s Mysession21 -Tl [AdventureWorks2014].[Sales].[SalesOrderDetail],[AdventureWorks2014].[production].[TransactionHistory]

    As you can see, you need to specify the database name, schema name, and the table name, and separate each table by commas.

Other common arguments

-a – If you want to apply the recommendations directly, you can use this argument, however it is not recommended. It is good to analyze the indexes before applying them. The indexes improve the performance in several cases, but it is a lot of work to maintain them and an excessive number of indexes can decrease the performance.

-B – The recommendations usually need a lot of space. It is recommended to limit the maximum number of megabytes for the recommendations. For example, if you want to limit the recommendations to 500 MB, the argument will be –B 500.

For a complete list of all the arguments, see the references.

Typical errors

A typical error message of the DTA is:

1. Network Error Message:

   A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

   The network path was not found

   Possible problems:

   If you receive this error, verify:

   • That the instance name was written correctly.
   • That the SQL Server Service is started.

   • That you have access to SQL Server.

2. The existing session name:

   Session ‘sessionName’ already exists on the server. Please provide a unique session name.

   Possible problems:

   If the session name already exists, specify another name for the argument –s and specify a new name.

Conclusion

In this chapter, we learned how to use the DTA. The DTA is a command line tool that is used to recommend indexes, partitions and statistics based on queries.

• Author
• Recent Posts

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.

He also helps with translating SQLShack articles to Spanish

View all posts by Daniel Calbimonte

Latest posts by Daniel Calbimonte (see all)

• PostgreSQL tutorial to create a user - November 12, 2023
• PostgreSQL Tutorial for beginners - April 6, 2023
• PSQL stored procedures overview and examples - February 14, 2023