Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!eu.feeder.erje.net!news2.arglkargh.de!news.swapon.de!eternal-september.org!feeder.eternal-september.org!mx02.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver Subject: Re: Engine Tuning Advisor recommending non-clustered index on clustered PK columns Date: Tue, 14 Apr 2015 20:29:30 +0200 Organization: Erland Sommarskog Lines: 39 Message-ID: References: <5b308c23-aca5-4c1f-89c6-3e578aaf2fd1@googlegroups.com> Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 8bit Injection-Info: mx02.eternal-september.org; posting-host="9e36d1f50e699bbc20b5e03d2072b95e"; logging-data="15812"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+Xp5F5/IFe5AUxRJw7b50i" User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32) Cancel-Lock: sha1:DM8tmS+2zCHoEUcMShww94p/1X4= Xref: csiph.com comp.databases.ms-sqlserver:1907 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