I don’t think this thing works. I posted a bug report in Connect. If you can replicate my problem, described below, please go here and confirm the issue.
Get a copy of AdventureWorks2008. Pick any of the bigger tables. In my case I went with the Sales.SalesOrderHeader table. Write a query, a simple one or a complex one, that has performance problems that can be easily fixed by the right query. Here’s a simple example:
SELECT soh.Freight, soh.SalesOrderNumber
FROM Sales.SalesOrderHeader AS soh
WHERE soh.SalesOrderNumber LIKE ‘SO’ + CAST(6 AS VARCHAR) + ‘%’
AND soh.Freight > 50
If you run this query and get an execution plan, it immediately flags a missing index. The plan itself is a clustered index scan and a filter operation. Take the index it suggests and build it:
CREATE NONCLUSTERED INDEX [IX_Test]
ON [Sales].[SalesOrderHeader] ([SalesOrderNumber],[Freight])
Run the query again. This time, because this is a covering index, it has a single index seek operation and it runs in one second instead of three. Now drop the index. Right click on the query and select “Analyze query in Database Engine Tuning Advisor.” Start the tuning session. After a few seconds, it returns with no recommendations. None.
I tried other queries. None of them are getting flagged. Here are a few examples
select CarrierTrackingNumber, ProductID, OrderQty, UnitPrice from Sales.SalesOrderDetail where CarrierTrackingNumber = ‘8639-4639-AA’
Production.ProductModel.Name AS ModelName,
INNER JOIN Production.ProductCostHistory ON
Production.Product.ProductID = Production.ProductCostHistory.ProductID
INNER JOIN Production.ProductInventory ON
Production.Product.ProductID = Production.ProductInventory.ProductID
INNER JOIN Production.ProductModel ON
Production.Product.ProductModelID = Production.ProductModel.ProductModelID
WHERE Production.ProductInventory.LocationID IN (60)
ORDER BY Production.Product.NAME
If anyone can make it work, please post the sample code in the comments.
I forgot to mention that I’ve posed the problem on a couple of different discussion lists. SQL Server Central (home away from home) provided a pretty interesting discussion, including the fact that my sample query, when run against the last CTP and not the RTM actually did provide a recommendation. It was the wrong one, but it provided something instead of just sitting there.
I also posted it to the Microsoft forum, SQL Server Katmai Manageability and Tools. No responses there as of this posting.