Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1908

Re: Engine Tuning Advisor recommending non-clustered index on clustered PK columns

Show all headers | View raw

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







    

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Engine Tuning Advisor recommending non-clustered index on clustered PK columns Mark D Powell <markp28665@gmail.com> - 2015-04-14 09:42 -0700
  Re: Engine Tuning Advisor recommending non-clustered index on clustered PK columns Erland Sommarskog <esquel@sommarskog.se> - 2015-04-14 20:29 +0200
    Re: Engine Tuning Advisor recommending non-clustered index on clustered PK columns Mark D Powell <markp28665@gmail.com> - 2015-04-15 09:59 -0700
  Re: Engine Tuning Advisor recommending non-clustered index on clustered PK columns rja.carnegie@gmail.com - 2015-04-15 20:40 -0700

csiph-web