Changing the primary key from non-clustered to clustered on tables causing drop in Join performance

deadliftpro 2018-05-25 21:17:05

Tables in a database were heaps and had non-clustered primary keys. I changed the primary keys to clustered and saw a drop in performance.

This query is joining tables on clustered index keys and taking 10 secs and doing a lot more 'worktable' logical reads than a exactly same query joining the tables with nonclustered primary keys on the same columns which took 5 seconds and a lot less 'worktable' logical reads. Joins are using primary-key/foreign-key columns. Please let me know what is going wrong here that is causing performance to drop.

The Non-clustered primary keys query plan is here

While fiddling around with the query using Clustered index, I changed the join order and made the query performance worse. So the JOIN order does matter and the optimizer can't be left to making the smart decision. The plan for changed JOIN order is here

Hugo Kornelis 2018-05-26 09:31:37
You did not post the execution plan with the nonclustered indexes. That makes it harder to give you a good answer. But here are some generic thoughts and musings.

One generic reason why a clustered index can be slower than an equivalent nonclustered index. Remember, a clustered index includes all columns of the table; a nonclustered only the columns that are indexed or included. So if you have an index over three integer columns in a table that also has twenty wide character columns, a clustered index uses far more bytes per row than that nonclustered index.

This is a double-edged sword. Indexes using just the columns in the nonclustered index will be faster becuase they do less I/O (those twenty character columns are never read). But as soon as more data is needed it canbackfire: now the index can still be found to find matching rows, but an additional lookup is added. Depending on how many rows end up qualifying and requiring the lookup, that can become expensive.

That being said: in your case I think there is more going on. I would typically not expect a change from nonclustered to clustered to make THAT much of a difference, and the additional worktable IO also suggests that there is more to this.

Looking at the execution plan, I see that the most likely source of the IO is the set of four Merge Join operators at the bottom right of the execution plan. The first one (in right to left data-flow order) joins the SAMPLES and ARCHIVE_SAMPLES tables, the second one adds HORIZONS, and after that LAS_RESULTS and OBSERVATIONS are added. The join criteria are rather similar in most cases: agency_code, proj_code, s_id, o_id, h_no, and samp_no. (But samp_no is not used in the second join, and both samp_no and h_no are absent in the last one).

Three of these four Merge Join operators are marked as "many to many". I blog about the problem of many to many merge joins here: https://sqlserverfast.com/blog/hugo/2017/12/many-many-reads-many-many-merge-join/.

Short summary: the optimizer does not trust EITHER of the inputs to be unique on the combination of all join columns. If at least one were unique, it could arrange the tables in proper order to do an efficient one-to-many join, which does not use a worktable at all. As soon as neither inputs has this unique guarantee, it has to resort to many-to-many which DOES use a worktable.

A many-to-many merge join is often caused by an index that should be declared as a UNIQUE index, but isn't. In other words, the user knows that the combination of columns identifies guaranteed unique rows, but doesn't tell SQL Server because they didn't include the UNIQUE keyword in the index creation. In your case, since the index implements the primary key, that cannot be the case – primary key indexes are always considered unique.

Another possible problem is a query error. A many-to-many join may mean that you didn't include all the columns you needed in the join, accidentally introducing duplicated results in the output. That is a bit more likely to be the case in your query, though it would not explain why you didn't have the same issue with nonclustered indexes.

And that brings me to the third and (in my opinion) ost likely explanation. Did you perhaps include extra columns in the index key when you created the primary key indexes? So for instance (and this is pure guesswork), perhaps you HAD a unique nonclustered index on just the columns agency_code and o_id in the SAMPLES table, but now you replaced this with a primary key on agency_code, proj_code, s_id, o_id, h_no, samp_no, and at least one more column. The former guarantees uniqueness on agency_code + o_id, and by extension on any set of columns that includes at least those two. The latter guarantees uniqueness on the full set of listed columns, but since your query does not include that one extra column the optimizer cannot rely on the remaining set of columns being unique.

If I were you, I'd look at the EXACT definition of the indexes you replaced. If a nonclustered index is declared as CREATE UNIQUE INDEX ON (, ) and you want to replace it with a clustered primary key, then you should use ALTER TABLE ADD CONSTRAINT PRIMARY KEY (, ) CLUSTERED. Note that I used the SAME combination of columns. (There may be reasons to change the order but that would really mess up your plan for this query so let's not go there!).

If the original nonclustered index had additional INCLUDE columns, ignore them. A clustered index by definition includes all columns. So if your original index was CREATE UNIQUE INDEX ON (, ) INCLUDE (), you should STILL use the same statement as before to create the primary key, and NOT add col3 to the list. (I assume that this is exactly what has happened in your case – but as mentioned above, without seeing the nonclustered index plan a lot of this is guesswork on VERY limited data).

deadliftpro 2018-05-26 10:52:17
thanks Hugo. The account creation email was being delivered to spam and thus I was having difficulty activating my account. And since my account was not active, I could not add the Non-Clustered index plan(funny that the system allows you to make the first post even without activating your account).

I will go through you answer. And by the way, I am now going to upload the non-clustered index plan as well.

deadliftpro 2018-05-26 11:00:01
i have now uploaded the non-clustered index plan.
Hugo Kornelis 2018-05-26 17:18:32
Thankas for the additional execution plan. I am now looking at both plans side by side and I see a few interesting observations.

Before diving in: it is possible that some of my conclusions are not 100% correct. I usually assume that the plan you see is the best the optimizer could give you, but sometimes the number of options is simply too high and the optimizer ran out of time before looking at all options. If the change from some indexes from nonclustered to clustered is the only change, then this is unlikely. But if you also added a few additional indexes in the process, then it's possible that the optimizer simply didn't see the better plan in time.

For the rest, I am going to assume that this did not happen and that all plan differences provide meaning ful information about your data.

The faster plan uses a different join order. Following the data flow (right to left), it first joins HORIZONS to ARCHIVE_SAMPLES, on equal values in agency_code, proj_code, s_id, o_id, and h_no. This uses a Merge Join, even though the data from HORIZONS is not in the correct order (hence the additional Sort operator). And very important: in this case the Merge Join is marked as one-to-many. So the optimizer knew that there are no duplicates in the data read from HORIZON. This means that the nonclustered index aaaaHORIZONS_PK must have been on only those five columns (or even less). On the other hand, the new clustered index HORIZONS_PK is NOT on only those five columns but on at least one more – that is the only logical reason why you get a many-to-many Merge Join in the slower execution plan.

(Well, there is one other option, which is that you didn't create a PRIMARY KEY constraint but instead used the CREATE INDEX syntax with an index name that makes people think it's an actual primary key – and in that case you perhaps simply forgot to include the UNIQUE keyword.)

After this first join, which is already more efficient than most of the joins in the slower plan because it doesn't use the may-to-many logic, the results are joined to SAMPLES. This uses a many-to-many Merge Join, similar to the slower query, so that should not make much of a difference.

The remaining jojns use Hash Match. That is the same in both plans for two of the joins, but the other two have changed to Merge Joins in the faster plan. The two that were changed were for OBSEVATIONS and LAB_RESULTS. Given the size of these data sets I understand why the optimizer wants to use a Merge Join. In the case of OBSERVATIONS this actually works out quite well(this is the one where the new plan already has a one-to-many join). But for LAB_RESULTS the many-to-many combination may cause a lot of worktable IO.

(I'll continue after supper)

Hugo Kornelis 2018-05-26 18:39:48
So after all the theory above, here are a few concrete suggestions you can hopefully follow up on.

  1. We already know that OBSERVATIONS has what looks to be a unique index on agency_code, proj_code, s_id, o_id in the clustered version of the execution plan. This is good and should not be changed.
  2. I know from the nonclustered plan that the data in HORIZONS allows for a UNIQUE index on agency_code, proj_code, s_id, o_id, h_no. The clustered index does not guarantee that uniqueness. You should change that,
  3. There does not appear to be a UNIQUE index on the column combination agency_code, proj_code, s_id, o_id, h_no, samp_no, in either the SAMPLES or the ARCHIVE_SAMPLES table. Based on the table and column names (and reaffirmed by looking at rowcounts in the execution plans), I would expect that this combination of columns should in fact be declared as a UNIQUE index (but beware! I do not have any insight in your actual data or in your data model; you should make sure that this really makes sense, both on current data and on possible future data, before making any change). If you can indeed change these two indexes to be UNIQUE on that combintion of columns, I expect all Merge Joins to change from many-to-many to one-to-many; and all worktable activity would go away.
  4. I am assuming that table LAB_RESULTS can have multiple rows for each combination of agency_code, proj_code, s_id, o_id, h_no, samp_no. That is okay. If all other tables have the proper UNIQUE index, then this will be the table that is always on the "many" side of the one-to-many Merge Join operators.

I expect the final execution plan to look like this (right-to-left data flow order): Merge Join between LAB_RESULTS on the lower side and either SAMPLES or ARCHIVE_SAMPLES on the top side, going into a Merge Join with the other samples table on the top side, then into yet another Merge Join to HORIZONS on top, then a last Merge Join to OBSERVATIONS on top (with all the Merge Joins being one-to-many). The result of that big join is then joined to SITES and LAB_METHODS using Hash Match, just as in the current versions of the plans.

Hugo Kornelis 2018-05-26 18:45:48
PS1: If you give SITES a UNIQUE index on agency_code, proj_code, s_id, then you might get an even better execution plan as that would replace one of the two remaining Hash match joins with yet another one-to-many Merge Join. The index can be either clustered or nonclustered, but in the latter case make sure to include all columns used from this table – so unless I misread the query you'd need an INCLUDE (map_scale). If you make this a clustered index you don't need an INCLUDE of course.

PS2: If see that you never use any of the columns from the SAMPLES table – except for the joins to SAMPLES_ARCHIVE and LAB_RESULTS. You might consider simply removing this table from the query altogether. This should not affect the results in any way, except if there are any duplicates in the data that I do not expect, or if there are values that exist in all of the other tables but not in SAMPLES.

PS3: The above removal of a table is sometimes already done by SQL Server itself, but only if it knows it is safe to do so. That requires the presence of both uniqueness guarantees (in the form of UNIQUE indexes, PRIMARY KEY constraints, or UNIQUE constraints), and existence guarantees (in the form of FOREIGN KEY constraints, and they have to be trusted). You might want to check that your data model has actual FOREIGN KEY constraints for all the foreign keys that should logically be there, and that these constraints are both enabled and trusted. The optimizer can only do its work if you give it all the information it needs, and constraints are the ultimate form of information!

deadliftpro 2018-05-28 05:55:17
Hi Hugo
Thanks for your reply and for the time you spent analyzing the issue.
Here is my point wise reply(I am quoting you alongside my comments down below):

1)

If the change from some indexes from
nonclustered to clustered is the only
change, then this is unlikely. But if
you also added a few additional
indexes in the process, then it's
possible that the optimizer simply
didn't see the better plan in time.

No, I did not add another index and i can confirm the only change is going from non-clustered to clustered.

2)

This means that the nonclustered index
aaaaHORIZONS_PK must have been on only
those five columns (or even less). On
the other hand, the new clustered
index HORIZONS_PK is NOT on only those
five columns but on at least one more
– that is the only logical reason why you get a many-to-many Merge Join in
the slower execution plan.

No, both clustered and non-clustered indexes are on the same columns

3)

Well, there is one other option, which
is that you didn't create a PRIMARY
KEY constraint but instead used the
CREATE INDEX syntax with an index name
that makes people think it's an actual
primary key – and in that case you
perhaps simply forgot to include the
UNIQUE keyword.

No, I specifically created a primary key and thus a unique clustered index created underneath to support it. Here is the snippet:
ALTER TABLE [dbo].[HORIZONS] ADD CONSTRAINT [HORIZONS_PK] PRIMARY KEY CLUSTERED
(
[agency_code] ASC,
[proj_code] ASC,
[s_id] ASC,
[o_id] ASC,
[h_no] ASC
)

4)

There does not appear to be a UNIQUE
index on the column combination
agency_code, proj_code, s_id, o_id,
h_no, samp_no, in either the SAMPLES
or the ARCHIVE_SAMPLES table

No, for the Samples table the column combination "agency_code, proj_code, s_id, o_id, h_no, samp_no" is unique
and for the Archive_samples table the column combination "agency_code, proj_code, s_id, o_id, h_no, samp_no, jar_no" is unique (note that jar_no is one extra column here)

ALTER TABLE [dbo].[SAMPLES] ADD CONSTRAINT [PK_SAMPLES] PRIMARY KEY CLUSTERED
(
[agency_code] ASC,
[proj_code] ASC,
[s_id] ASC,
[o_id] ASC,
[h_no] ASC,
[samp_no] ASC
)

ALTER TABLE [dbo].[ARCHIVE_SAMPLES] ADD CONSTRAINT [PK_ARCHIVE_SAMPLES] PRIMARY KEY CLUSTERED
(
[agency_code] ASC,
[proj_code] ASC,
[s_id] ASC,
[o_id] ASC,
[h_no] ASC,
[samp_no] ASC,
[jar_no] ASC
)