From: markp28665@gmail.com   
      
   On Tuesday, April 14, 2015 at 2:29:34 PM UTC-4, Erland Sommarskog wrote:   
   > Mark D Powell (markp28665@gmail.com) writes:   
   > > A recent Database Engine Tuning Advisor run on a SQL Server 2008 R2 RTM    
   > >   
   >    
   > RTM? Please install Service Pack 3 at first possible occasion.   
   >    
   > > system has several recommendations that puzzle me because the   
   > > recommendation is for a two column non-clustered index on the same two   
   > > columns that are the clustered PK.  In one case there are only two other   
   > > columns consuming a total of 5 bytes on the row so I just cannot see any   
   > > valid reason for this recommendation.    
   >    
   > One needs to take the recommendations from the Tunig Advisor with a grain   
   > a salt. Also, a common (ab)use of the advisor is to feed it just a single   
   > query. The idea is that you should give it a full workload and it tunes   
   > that workload.   
   >    
   > DETA builds on the missing-index feature in the engine which is very fond   
   > of recommending covering indexes.   
   >    
   > Say that you in your table with your two-column clustered PK, you have the    
   > query like:   
   >    
   >    SELECT pkcol1, MIN(pkcol2)   
   >    FROM   tbl   
   >    GROUP  BY pkcol1   
   >    
   > This query will run faster if you add a non-clustered index on the columns,    
   > since the leaf pages of the index will be smaller than the leaf pages of the    
   > clustered index which have all the other columns.   
   >    
   > Of course, if the other two columns are only five bytes, it is unlikely    
   > that this index would be a significant speed booster.   
   >    
   > It is definitely a good thing to review the suggestions from DETA    
   > critically.   
   >    
   > --    
   > Erland Sommarskog, Stockholm, esquel@sommarskog.se   
      
   Erland, thank you for the reply.  I have always taken the DTA output with a   
   large grain of salt. The missing index feature seems overly fond of include   
   columns and the DTA of indexes in general. I am just trying to understand why   
   if makes some of the    
   recommendations it does so I can justify not applying these specific indexes   
   or identify that the recommendation is in fact valid.   
      
   The SP and indeed version level has been raised to management, but as I am   
   only a part-time resource and other full time events have taken priority we   
   will have to see.   
      
   Thanks again,   
   Mark D Powell   
      
      
      
      
      
      
      
          
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)