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

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

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

Show all headers | View raw

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

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