On Tuesday, 14 April 2015 17:42:37 UTC+1, Mark D Powell  wrote:   
   > A recent Database Engine Tuning Advisor run on a SQL Server 2008 R2 RTM   
   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.   
   > - -   
   > There is a index reorganization job I added to the system that runs every   
   Sunday but statistics update is left to SQL Server.     
   > - -   
   > Does anyone have a good idea of what would cause these recommendations?   
   > - -   
   > -- Mark D Powell --   
      
   To chip in, the primary key doesn't /have/ to be    
   a clustered index; you can do without a clustered   
   index, although I like to use an int (or tinyint)   
   row key: since it's used to build other indexes,   
   the clustered key should be short (and the clustered   
   index created first).  I handle some data where the    
   primary key is varchar(255) or something silly like    
   that - it's a file name.   
      
   A "primary key" may not even exist for data.   
      
   (An integer row key isn't a "primary key" because   
   it isn't meaningful data - unlike the file name    
   column; I could change all the integer values -   
   throughout the database - and no one would care.)    
      
   But, usually, and by default, there is an   
   identifiable appropriate "primary key" which   
   also is a useful clustered index key.   
   Performance rarely is improved much by messing   
   with that - I'd say choose wisely when you    
   are creating a table, but afterwards, presume   
   that you or someone else had a good reason to   
   make the database design choices that you are    
   now living with.  Unless it really looks like   
   they didn't.   
      
   As for the recommendations, otherwise, I say yes   
   to being sceptical.  The server does do this job    
   all day, so you're interrogating its experience   
   of that, which is difficult to convert to a simple   
   concept.  On the other hand, if /you/ did that    
   job all day then you'd probably start to get strange    
   ideas about it.  And, SQL Server would not look   
   good if you took your service offline and ran the   
   tuning advisor for a couple of hours and it didn't   
   have /some/ recommendations.  So it gives you some.   
   But they may not be good ones.   
      
   IIRC they come with estimated performance benefits -   
   also to be taken with a pinch of salt - and, as    
   Erland Sommarskog explained, they relate strictly   
   to the workload that was studied, and not to any    
   other operations that you might do on the database.   
      
   Which is odd, because, once you have run a query and   
   got the results, why would you ever run the same query   
   on the same data again?  ;-)   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)