February 5, 2014 at 10:51 am
Hi,
looking at a whole bunch of select statements with a view to tuning etc etc . For the first one I look at I use DTA and it recommends one index, and then the actual query plan offers a suggestion for a slightly different missing index.
So I look at the second of my candidates for optimisation. This time the execution plan recommends an index for a different table that the DTA.
So why do DTA and execution plans recommend different/varying indexes and which version would be more accurate ? Obviously I can test etc but with over 50 of these to optimise some words of wisdom would be appreciated
thanks simon
February 5, 2014 at 10:58 am
DTA is... um... I'll find a polite word for it in a moment, hang on.
....
DTA is a best effort guess from an app to try to help you figure out good methods for approaching your data from a single point of view. It's really, really bad at it though. It's not horrible for OLAP systems which are typically write once/read 3 billion times, but OLTP systems that implement all the recommendations would bog down.
Your best bet is to analyze your optimization plans, determine what your access methods can be, and then reviewing your existing tables. Sometimes simply including a single column into an existing index is the answer. Sometimes rebuilding the table from scratch with a clustered index that isn't the PK is.
I would recommend that you post your query, the underlying DDL for the tables, and the execution plan here, and the good folks round these parts will help you find your solution.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 5, 2014 at 11:06 am
Evil Kraig F (2/5/2014)
Your best bet is to analyze your optimization plans, determine what your access methods can be, and then reviewing your existing tables. Sometimes simply including a single column into an existing index is the answer. Sometimes rebuilding the table from scratch with a clustered index that isn't the PK is.
Agreed.
DTA is a tool that can be helpful under the direction of an experienced person who can analyze the output, knows the data and usage patterns, and who understands the execution plans on the system.
In the wrong hands, DTA can be instrumental in causing bigger performance problems.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 5, 2014 at 11:48 am
Evil Kraig F (2/5/2014)
DTA is... um... I'll find a polite word for it in a moment, hang on.....
DTA is a best effort guess from an app to try to help you figure out good methods for approaching your data from a single point of view. It's really, really bad at it though. It's not horrible for OLAP systems which are typically write once/read 3 billion times, but OLTP systems that implement all the recommendations would bog down.
And it's better than the missing query DMV/hints in query plans
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply