SQL Server 2008: Database Engine Tuning Advisor

October 7, 2008 at 1:54 pm (SQL Server 2008, Tools) (, )


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

  1. ecards said,

    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

  2. scarydba said,

    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: