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
FROM    Sales.SalesOrderHeader AS soh
WHERE   DueDate BETWEEN '1/1/1970' AND '1/1/1971'
        AND Status > 200

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 – 10/5/2008. The database and tables don't need to be edited. The first tab, General, will look like Figure 5-6 when you're done.

Figure 5.6. Query tuning general settings

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. For the purposes of the example, I'm going to let the Database Engine Tuning Advisor run for 15 minutes, but a more realistic approach here would be to let it run for an hour. 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.

Figure 5.7. Tuning Options tab adjusted

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 it to evaluate this query. The initial recommendations were not good. 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.

Figure 5.8. Query tuning initial recommendations

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 meaningful recommendation, 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. Running the Database Engine Tuning Advisor again, it finishes in less than a minute and displays the recommendations shown in Figure 5-9.

Figure 5.9. Query tuning recommendations

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 two columns referenced in the query. As outlined in Chapter 4, a covering index is one of the most performant methods of indexing. The Database Engine Tuning Advisor was able to recognize that creating an index with all the columns referenced by the query, a covering index, would perform best.

Once you've received a recommendation, you should take a look at the proposed T-SQL command. Finally, you'll want to apply the recommendation. Select Actions

Figure 5.10. Apply Recommendations dialog box

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).

Figure 5.11. A successful tuning session applied

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.

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 was covered in Chapter 3.) 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 three different loads by running queries in a loop from the sqlps.exe command prompt. This is the PowerShell command prompt with the SQL Server configuration settings. It gets installed with SQL Server.

These are the scripts run (queryload.ps1):

$val = 0; while ($val -lt 50) {$val++; Invoke-Sqlcmd
-Server "YourServerName" -Database "AdventureWorks2008"
-InputFile "pathqueryload.sql"}
$val = 0; while ($val -lt 20000){$val++; Invoke-sqlcmd
-Server "YourServerName" -Database "AdventureWorks2008"
-Query "EXEC dbo.uspGetEmployeeManagers @BusinessEntityID = $val"}
$val = 0; while ($val -lt 20000){$val++; Invoke-sqlcmd
-Server "YourServerName" -Database "AdventureWorks2008"
-Query "EXEC dbo.uspGetManagerEmployees @BusinessEntityID = $val"}

The queryload.sql file is also available in the download file. It's just a collection of queries with various parameters to provide a simulated load.

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. Set the appropriate tuning options, and start the analysis. This time, it will take more than a minute to run (see Figure 5-12).

Figure 5.12. Database tuning engine in progress

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.

Figure 5.13. No recommendations

After running all the queries through the Database Engine Tuning Advisor, the best recommendation it could currently come up with was to drop a couple of indexed views. That doesn't mean there aren't other possible improvements; it just means that the advisor is not always able to recognize all the possible improvements.