| Creating the right indexes for a database project is not an easy job. To do this, a lot of factors must be considered: The data model of the database The amount and distribution of the data within the tables Which queries are performed against the database How often the queries occur How often the data is updated
To help in designing indexes, SQL Server offers a tool called the Database Engine Tuning Advisor. The Database Engine Tuning Advisor requires a workload file, which can be a text file with the statements to be optimized, or a trace file, which can be produced with SQL Server Profiler. The Database Engine Tuning Advisor then optimizes the database using the SQL Server Query Optimizer and the existing database to suggest changes in physical design structures, like creating, changing, or removing various indexes. Using the Database Engine Tuning Advisor 1. | Start SQL Server Management Studio. Open a New Query window and change the database context to AdventureWorks.
| 2. | Enter the following statements, which you will optimize with the Database Engine Tuning Advisor. The code for this example is included in the sample files as UsingDatabaseEngineTuningAdvisor.sql.
USE AdventureWorks; SELECT o.SalesOrderID, o.OrderDate, od.ProductID FROM dbo.Orders o INNER JOIN dbo.OrderDetails od ON o.SalesOrderID = od.SalesOrderID WHERE o.SalesOrderID = 43659; SELECT o.SalesOrderID, o.OrderDate, od.ProductID FROM dbo.Orders o INNER JOIN dbo.OrderDetails od ON o.SalesOrderID = od.SalesOrderID WHERE o.SalesOrderID BETWEEN 43659 AND 44000;
| 3. | To save this script as a workload file, open the File menu and select Save As. Save the query as dta.sql.
| 4. | From SQL Server Management Studio, select Database Engine Tuning Advisor from the Tools menu. Connect to your SQL Server instance.
| 5. | Choose the file you saved in step 3 as the workload file and choose AdventureWorks as the database to tune as shown below:
 | 6. | Press the Start Analysis button on the toolbar.
| 7. | After the analysis finishes, the window displays the recommendations, as shown here:
 | 8. | Database Engine Tuning Advisor recommends building two indexes. To save the script for generating the indexes, select Save Recommendations from the Actions menu.
| 9. | Close Database Engine Tuning Advisor.
|
As you can see, SQL Server tries to optimize the two queries as well as it can. This is beneficial only if these queries should be optimized without concern for the effects of optimization on other database operations. To optimize all database indexes, it is a good idea to use a SQL Server Profiler trace, which provides the Database Engine Tuning Advisor with a normal workload for the whole database. With this information, Database Engine Tuning Advisor can optimize queries with other workloads on the database. After analyzing a workload, be sure to save and review the recommendations. |
