From: esquel@sommarskog.se   
      
   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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)