DTA a great tool to automate indexes
DTA a great tool to automate indexes
Figure 1. The command Prompt 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 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. 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 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 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. 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 Once you run you will have a message similar to this one:
Figure 4. The report message The command line will indicate where the reports were stored. The reports are stored in a txt file:
Figure 5. The report file 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. 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. 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: 1234 select 'select count(*) as count'+[TABLE_NAME]+' from '+[TABLE_SCHEMA]+'.'+[TABLE_NAME]from [INFORMATION_SCHEMA].[TABLES] 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 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 The tables with more than 100,000 rows are [Sales].[SalesOrderDetail] and [production].[TransactionHistory] 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.
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) SQL Partition overview - September 26, 2022 ODBC Drivers in SSIS - September 23, 2022 Getting started with Azure SQL Managed Instance - September 14, 2022
SQLShack
SQL Server training EspañolDTA a great tool to automate indexes
December 4, 2015 by Daniel CalbimonteIntroduction
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: 123 select SERVERPROPERTY('Edition') AS Edition You will also need the AdventureWorks database installed. In our example, the AdventureWorks2014 is being usedGetting started
We are going to drop an existing index of the Person.person table of the AdventureWorks2014 database in order to force some recommendations: 1234 DROP INDEX [IX_Person_LastName_FirstName_MiddleName] ON [Person].[Person]GO 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: 12345678 SELECT BusinessEntityID, FirstName, MiddleName, LastNamefrom Person.Personwhere LastName = 'Allison' SELECT COUNT(*) FROM Person.Person Now open the command prompt (cmd):Figure 1. The command Prompt 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 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. 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 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 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. 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 Once you run you will have a message similar to this one:
Figure 4. The report message The command line will indicate where the reports were stored. The reports are stored in a txt file:
Figure 5. The report file 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. 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. 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: 1234 select 'select count(*) as count'+[TABLE_NAME]+' from '+[TABLE_SCHEMA]+'.'+[TABLE_NAME]from [INFORMATION_SCHEMA].[TABLES] 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 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 The tables with more than 100,000 rows are [Sales].[SalesOrderDetail] and [production].[TransactionHistory] 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: 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. 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 CalbimonteDaniel 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) SQL Partition overview - September 26, 2022 ODBC Drivers in SSIS - September 23, 2022 Getting started with Azure SQL Managed Instance - September 14, 2022