Database Engine Tuning Advisor and the Query Optimizer

Ben Nevarez — Thu, 12 Nov 2009 03:47:00 GMT

Did you know that the Database Engine Tuning Advisor (DTA) uses the Query Optimizer to help you to create indexes, indexed views, and partitions for your databases? The DTA uses the Query Optimizer to estimate the cost of queries so it can select the choices with the lowest estimated cost. But, how can the Query Optimizer estimate the cost of a query using, for example, an index that does not exist yet?

Creating indexes on a DTA session could be very expensive and can create some other performance problems in your database. In addition to that, when the Query Optimizer uses indexes to estimate the cost of a query, it uses only the index statistics; it does not need to access the index data.

So, to avoid creating real indexes during a DTA session, SQL Server has a special kind of indexes called hypothetical indexes. Hypothetical indexes are not real indexes, they only contain statistics and can be created with the undocumented command CREATE INDEX WITH STATISTICS_ONLY. This command only creates the statistics for the index.

You may not be able to see these indexes during a DTA session because they are dropped automatically. But you can see the CREATE INDEX WITH STATISTICS_ONLY and DROP INDEX commands if you run Profiler to see what the DTA is doing. You can also create these indexes manually as I will show you later.

Hypothetical index have been available in previous versions of SQL Server where they were used by the DTA predecessor, the Index Tuning Wizard.

Let us take a quick tour to some of these concepts here. Create a new table on the AdventureWorks database

select *

into dbo.SalesOrderDetail

from sales.SalesOrderDetail

Copy the following query and save it to a file

select * from dbo.SalesOrderDetail

where ProductID = 897

Open a new DTA session. You can optionally run a Profiler session if you want to inspect what the DTA is doing. On workload file select the file containing the SQL statement that you just created. Specify AdventureWorks both for the database to tune and for the database for workload analysis. Click the Start Analysis button.

When the DTA analysis finishes run this query to inspect the contents of the msdb..DTA_reports_query table

select * from msdb..DTA_reports_query

Notice that the table contains some information like the query that was tuned and the current and recommended cost. The current cost, 1.2434, is easy to obtain by directly requesting an estimated execution plan for the query

Since the DTA analysis was completed, the needed hypothetical indexes were already dropped. In the next statement I will create the index recommended by the DTA, but instead of a regular index I will create it as a hypothetical index by adding WITH STATISTICS_ONLY.

create clustered index cix_ProductID on dbo.SalesOrderDetail (ProductID)

with statistics_only

You can validate that a hypothetical index and statistics were created by running this (notice that the index is defined as hypothetical on the index_description field)

sp_helpindex 'dbo.SalesOrderDetail'

select * from sys.stats

where object_id = object_id('dbo.SalesOrderDetail')

However, at the moment I am not aware of a way outside the DTA to ask the Query Optimizer to consider these hypothetical indexes on an estimated execution plan. So I am not able to see where the previous recommended cost is coming from.

Remove the hypothetical index by running this

drop index dbo.SalesOrderDetail.cix_ProductID

Implement the DTA recommendation this time as a regular clustered index

create clustered index cix_ProductID on dbo.SalesOrderDetail (ProductID)

After implementing the recommendation and running the query, the clustered index is in fact being used by the Query Optimizer and this time the estimated cost I got was 0.0033652, very close to the recommended cost listed before on the msdb..DTA_reports_query.

Finally, drop the dbo.SalesOrderDetail table you just created.

Benjamin Nevarez : Hypothetical Indexes, Query Optimizer

Database Engine Tuning Advisor and the Query Optimizer