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

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

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

Show all headers | View raw

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?  ;-)

Back to comp.databases.ms-sqlserver | Previous | Next — Previous 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