SQL Server 2008: Database Engine Tuning Advisor

Home / SQL Server 2008 / SQL Server 2008: Database Engine Tuning Advisor

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’

SELECT Production.Product.Name,
Production.Product.ProductNumber,
Production.ProductModel.Name AS ModelName,
Production.ProductInventory.LocationID,
Production.ProductInventory.Shelf,
Production.ProductInventory.Bin,
Production.ProductCostHistory.StartDate,
Production.ProductCostHistory.EndDate,
Production.ProductCostHistory.StandardCost
FROM Production.Product
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.

UPDATE:
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.

2 Comments

  • Does the same thing work properly under SQL Server 2005?

    In other words is this a regression bug or something that was never present?

    Thanks for the heads up.

    ltg

  • scarydba

    It does seem to work in 2005. Someone with the most recent CTP prior to the final version also had it work. It’s probably a regression bug.

OK, fine, but what do you think?