SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Execution Plan vs DTA missing index recommendations


Execution Plan vs DTA missing index recommendations

Author
Message
simon_s
simon_s
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 Visits: 244
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
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20461 Visits: 7660
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.


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66289 Visits: 18570
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

GilaMonster
GilaMonster
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223025 Visits: 46294
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search