Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Execution Plan vs DTA missing index recommendations Expand / Collapse
Author
Message
Posted Wednesday, February 5, 2014 10:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 16, 2014 5:10 AM
Points: 13, Visits: 77
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



Post #1538295
Posted Wednesday, February 5, 2014 10:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:04 PM
Points: 6,134, Visits: 7,182
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
Post #1538301
Posted Wednesday, February 5, 2014 11:06 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:55 PM
Points: 21,204, Visits: 14,889
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1538304
Posted Wednesday, February 5, 2014 11:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:18 PM
Points: 42,437, Visits: 35,492
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 2008, MVP
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

Post #1538317
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse