Introduction

The indexes in many cases are great solutions to solve performance problems. For some problems, they are magical and very cheap solutions. In this chapter, we will show a demo of the SQL Server Tuning Advisor, which is a tool that comes with the SQL Server Installation. We will also use the SQL Server Profiler to generate the information for the Tuning Advisor.

The Tuning Advisor

The Tuning Advisor is a tool used to recommend and apply indexes, statistics and partitions in our SQL Server, based on the information from a sql script, from a xml file or a Profiler trace file. This tool analyzes queries and recommends indexes, statistics and partitions for our tables and views.

SQL Server Profiler

In order to record daily database activity, you can use the SQL Server Profiler tool. The SQL Server Profiler will store the T-SQL queries that will be used for the Tuning Advisor to start.

The SQL Server Profiler is a great tool to monitor long queries and monitor the performance of the daily database activities. It is also used to monitor database activities for security purposes.

You can use SQL Profiler to monitor Relational Databases (our scenario) or Multidimensional databases (SSAS).

Requirements

• The Tuning Advisor, which is included in all the SQL Server Editions except in the Express editions
• The SQL Server Profiler, which is included in all the SQL Server Editions except the Web and 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 AdventureWorks 2014 is being used
  

Getting started

In SQL Server Profiler, we are going to record the Database Activity in a trace file. This file will be later used by the Tuning Advisor to give recommendations.

1. In order to start, open SQL Server Profiler:

   
   

   Figure 1. The SQL Server Profiler

2. To trace the information, go to File>New Trace:

   
   

   Figure 2. Create a New Trace

3. Enter the SQL Server authentication information and press connect:




Figure 3. The SQL Server credentials

4. The General section allow us to specify the Trace name and we can specify the template. The template allows us to specify the type of the activity to monitor. In this case, we want to record information for the tuning wizard. For this purpose, the Tuning template can be used. Optionally, you can create your own template, but this is out of the scope of this article:

   
   

   Figure 4. The Tuning template

5. You can store the trace in a trace file or store in a SQL Server table. Storing in a file is faster, but storing in a table is useful to create advanced queries for tracking purposes. In this sample, we will store in a file:

   
   

   Figure 5. The trace file

6. Once you have the trace properties defined, run the trace

7. In order to generate activity, open SQL Server Management Studio (SSMS):

   
   

   Figure 6. The SSMS and the AdventureWorks

8. We are going to work with the Person.Person table:

   
   

   Figure 7. The Person.Person table

9. To simulate the lack of indexes, we are going to remove an existing index of the table Person.Person:

   1 2 3 4

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

10. Now, run these queries in order to generate some information:

    1 2 3 4 5 6 7 8

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

11. This is a simulation to learn how to work with the tuning advisor. Usually, you will run the profiler and the users will work on the daily tasks and the Profiler will be loading the queries with real activities and at the end of the day, the trace will be stopped (you can specify the time to stop the trace in Profiler). In the SQL Server Profiler, stop the trace:

    
    

    Figure 8. Stopping the trace

12. If you check TextData you will notice that a lot of internal queries were generated when you used the SQL Server Profiler. The Profiler stores all the activity and you can filter some information to reduce the workload:

    
    

    Figure 9. The trace information

13. If we check the trace information, we will see the query run on step 10. The duration of the query execution is 1888 ms:

    
    

    Figure 10. The query run

14. Now that we have the information collected in a trace file, it is time to open the Database Engine Tuning Advisor:

    
    

    Figure 11. The Tuning Advisor

15. By default, the session name is the user name and the date:

    
    

    Figure 12. The Advisor Session

16. You can upload information from a .trc file from Profiler (our scenario), from a sql script, from a xml file, from a table and from the Plan Cache. You can also choose where to do the analysis. In this example, the workload will be done in the master database. Finally, we will select the database to tune. We used the AdventureWorks database:

    
    

    Figure 13. The session options

17. The tuning options tab will help you to configure some options like the stop time of the analysis. If your workload is huge, analyzing all the information may take several hours. That is why you can limit the time to analyze the information.

    You can also limit your analysis to just Index, just index views or limit the analysis to non-clustered indexes. All these options will reduce the analysis time. You have also options to analyze the partitioning strategy and the Physical design structure to keep in the database. You can decide which structures to keep.

    
    

    Figure 14. The tuning options

18. Once you have all the configurations ready, you can start the analysis:

    
    

    Figure 15. Starting the analysis

19. Once the Analysis is done, you will be able to see the Recommendations. In this example, the estimated improvement with the changes will be 98 %. It shows the Database, the object Name (in this case the Person.Person table). The recommendations are to create statistics and to create an index:

    
    

    Figure 16. The Tuning Advisor recommendations

20. In the reports tab, there are several reports. The Statement cost report will show the T-SQL statements and the percentage of improvement when we apply the changes:

    
    

    Figure 17. The statement cost report

21. The event frequency report shows how frequently the queries are used. This is very important because an excessive number of indexes can decrease the performance. You cannot blindly apply the recommendations, you need to analyze the reports first and choose wisely, the queries most frequently used. You should prioritize frequently used queries:

    
    

    Figure 18. The Event frequency report

22. The statement detail report shows the current cost and the cost of the query applying the recommendations. The lowest cost, the best performance

    
    

    Figure 19. The Statement detail report

23. In addition, there are more reports available. Use them wisely to select the best recommendations:

    
    

    Figure 20. The Tuning Advisor reports

24. In the Actions menu, you can apply the recommendation, which will create the indexes, statistics or any other recommendation. The Save Recommendations will save the recommendations in a .sql script. The Evaluation option evaluates the recommendations:

    
    

    Figure 21. The Tuning Advisor actions

25. In this example, we will save the recommendations in a sql script:

    
    

    Figure 22. The script with the recommendations

26. Open the script created on step 25 and execute the sentences. The script will create 1 index and 1 statistic:

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

    use [AdventureWorks2014] go   SET ANSI_PADDING ON   go   CREATE NONCLUSTERED INDEX [_dta_index_Person_7_1765581328__K7_1_5_6] ON [Person].[Person] ( [LastName] ASC ) INCLUDE ( [BusinessEntityID], [FirstName], [MiddleName]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY] go   CREATE STATISTICS [_dta_stat_1765581328_3] ON [Person].[Person]([NameStyle]) go

27. Now, run again the query of the step 10, but activate the Actual Execution Plan icon. The execution plan will show if the new index created is being used:

    
    

    Figure 23. Activating the actual execution plan

28. As you can see, the Index created in the step 26 is being used by the query:

    
    

    Figure 24. The Execution Plan

Conclusion

As you can see, the Tuning Advisor is a nice and simple tool. You just need to run the profiler store the trace in a file or a table or just pass a sql script with the queries to analyze or simply you can analyze the plan cache.

The recommendations done by the Tuning Advisor should be carefully analyzed. You should analyze the indexes and check the reports before implementing them.

Read the next chapter and learn how to use the command line tool called DTA.

• 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

Related posts:

1. DTA, a great tool to automate indexes
2. An overview of the SQL Server Profiler
3. SQL Server indexes – series intro
4. Tracing and tuning queries using SQL Server indexes
5. How to create indexes on SQL Server computed columns