October 6, 2008 at 8:22 pm
I'm trying to create any queries that the Database Engine Tuning Advisor can recognize as being problematic. I'm using the AdventureWorks2008 database. I've got bookmark lookups and clustered index scans and the silly tool can't recognize any performance enhancements. Either I'm not working it right or this thing is a stinker (I'm inclined toward the latter, but I've made to many silly mistakes in the past to dismiss the former).
Does anyone have experience working with this tool? Can you suggest a query or queries that will make this thing recognize the need for a new index?
I've tried capturing individual queries, sets of queries run repeatedly, series of queries run with constantly changing parameters (learned a bit of PowerShell to do that one). None of them are being recognized by this silly tool as problem queries, but I know that they're stinkers. I'm running LIKE statements against unindexed varchar fields. An index would clean them up quickly.
A little help, my head is getting sore and wall is looking awfully bloody.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 6, 2008 at 10:30 pm
I know for SQL 2008, if you enable Execution Plan in SSMS, it'll show MISSING INDEX HINTS
Although the caveat is that the index is tuned, only for that particular query, so you may have built an index that's not applicable for all other queries
October 7, 2008 at 1:35 am
Is this for a demo or presentation or similar?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2008 at 4:04 am
Similar.
I know a number of other ways to fix the queries myself. I need to see the bloody DTA do the work.
I tracked down a bit of sample code that supposedly worked in 2005 and while it produced a heinous plan filled with table & index scans, the DTA didn't recommend a single viable index. I've got something mucked up or this tool is a total pile (again, I'm leaning toward the latter).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 7, 2008 at 4:19 am
This is interesting.
I ran a very simple workload in 2008's DTA against the AdventureWorks database. When I ran it to my 2008 server, I got no results. When I ran it against the 2005 server (same database structure) I got a recommended index with an 83% improvement.
File a bug on Connect?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2008 at 5:26 am
Yeah, that might be the place to go.
I found a clean place on the wall. I'm going to do a few more bashes first.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 7, 2008 at 5:53 am
OK. A little more information. According to BOL, the DTA won't provide recommendations unless:
The table has been selected to tune (it is)
The table has more than 10 pages of data (I'm using SalesOrderHeader which has 688 pages)
DTA doesn't have time to tune the query (I've turned off the time limit so it can tune it forever)
The query references tables that are already at the index limit (it isn't)
The query performs DML against the tables, which would increase performance (although I can argue this point, but I'm just running a select)
Maximum columns are reached (nope)
Disk space is too small (nope)
In other words, it should tune the query.
I can't find any other reports in Connect of the DTA just not working.
More testing & research...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 7, 2008 at 6:00 am
Oh, since I violated the rules with the first post, let me show my query:
SELECT soh.Freight, soh.SalesOrderNumber
FROM Sales.SalesOrderHeader AS soh
WHERE soh.SalesOrderNumber LIKE 'SO' + CAST(6 AS VARCHAR) + '%'
AND soh.Freight > 50
Just running this and getting an execution plan results in a suggestion for an index:
Missing Index (Impact 93.0544): CREATE NONCLUSTERED INDEX... ON Sales.SalesOrderHeader (SalesOrderNumber, Freight)
But the DTA can't see it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 7, 2008 at 7:02 am
The query I was trying is even simpler:
select CarrierTrackingNumber, ProductID, OrderQty, UnitPrice from Sales.SalesOrderDetail where CarrierTrackingNumber = '8639-4639-AA'
Missing indexes picks up that (CarrierTrackingNumber) INCLUDE (ProductID, OrderQty, UnitPrice) would be extremely useful, but DTA doesn't.
See if you can reproduce on a second server. If you can, log it on Connect. It's adventureworks, so it should be easy for them to reproduce.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2008 at 9:17 am
Nope. No recommendation from that query either. I haven't found a query yet that the silly thing will recommend and index for.
I'll post something on Connect now.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 7, 2008 at 9:33 am
It's on Connect. If you want to confirm it or track it, go here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 7, 2008 at 10:16 am
Ok, sadly funny. I am still running the last pre-release (don't ask) prior to RTM and thought I would give that a try to see how that did. Not much better as you can see from the results;
CREATE NONCLUSTERED INDEX [_dta_index_SalesOrderHeader_10_722101613__K23_1] ON [Sales].[SalesOrderHeader]
(
[Freight] ASC
)
INCLUDE ( [SalesOrderID]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
I guess on a good note, it came up with something. It did actually come up a statistic recommendation that is pretty close;
CREATE STATISTICS [_dta_stat_722101613_23_8] ON [Sales].[SalesOrderHeader]([Freight], [SalesOrderNumber])
The really funny thing is that SSMS provides what appears to be the proper index as you already stated;
USE [AdventureWorks]
GO
CREATE NONCLUSTERED INDEX [ ]
ON [Sales].[SalesOrderHeader] ([SalesOrderNumber],[Freight])
GO
I will go up to the connect site after I try this on an actual RTM installation and validate your submission.
Thanks.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 7, 2008 at 10:22 am
That's wild, but as you say, at least you got a result. I'd be happy with a result, even a sub-standard one. Actually, I'd be happier with a sub-standard result. I'm not terribly enamored of the tool in the first place.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 7, 2008 at 10:26 am
Okay, off topic but I need to ask. Where did you purchase your copy of SQL Server 2008 Developer Edition, and how long did it take for you to get it?
Thanks.
😎
October 7, 2008 at 10:51 am
I don't know. I got it from our server admin guys. They handle all the software versions, etc.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply