SQL Server: How does the type of an index affect a join's performance?

Question

If I'm am trying to squeeze every last drop of performance out of a query what affect does having these types of index's being used by my joins.

• clustered index.
• non-clustered index.
• clustered or non-clustered index with extra columns that may not be involved in the join.

Will I gain any performance if I go through and create clustered index's that only contain the columns involved in my joins and nothing else?

(I realize I may have to move the clustered index from another index(making that index non-clustered) since it can only have one.)

Answer 1

You only get one clustered index - this is what controls the physical storage of the table on disk / in memory.

Non-clustered indexes repeat the included fields, with pointer to the rows that have that value. Having an index on the columns being used in your joins should improve performance. You can further optimise by using "included columns" in your index - this duplicates the row information directly into the index, which can remove the performance penalty of having to look up the row itself to perform the select.

It is useful to pay attention to the order in which your joins occur - the sequence of columns in your index should match up to this. Remember that the SQL engine may optimise and re-order your query internally - profiling may be helpful.

In most situations, you can just use the Database Engine Tuning Advisor - the recommendations it provides are pretty much spot on.

Answer 2

If you can your best bet is for a non-clustered index that has all the element of your join in it and if possible the field you are selecting.

This will create a spanning index meaning that all the fields SQL requires to perform are on one index.

If possible have an index which has no unnessasery field in it. Every field added makes the an individual index record larger, the smaller each index record the more you get in each Page. The more index items you get in each page the less you have to go to the Disk.

Clustered Index - Will mean the table is layed out in the order specified in the Index, this means that you will get better performance for select * from TABLE where INDEXFIELD = 3. Unless you are selecting lots of large data items this should not be required.

Answer 3

In addition to Gareth Saul's answer a tiny clarification:

Non-clustered indexes repeat the included fields, with pointer to the rows that have that value.

This pointer to the actual data value is the column (or the set of columns) that are in your clustering key.

That's one of the main reasons why you should try and keep the clustering key small and static - small because otherwise you'll waste a lot of space, on disk and in your server's RAM, and static because otherwise, you'll have to update not just your clustering index, but also all your non-clustered indices as well, if your value changes.

This "lookup pointer is the clustering key" feature has been in SQL Server since version 7, as Kim Tripp will explain in great detail here:

What is a clustered index?

In SQL Server 7.0 and higher the internal dependencies on the clustering key CHANGED. (Yes, it's important to know that things CHANGED in 7.0... why? Because there are still some folks out there that don't realize how RADICAL of a change occurred in the internals (wrt to the clustering key) in SQL Server 7.0).

What changed is that the clustering key gets used as the "lookup" value from the nonclustered indexes.

Answer 4

Will I gain any performance if I go through and create clustered index's that only contain the columns involved in my joins and nothing else?

Not as I understand. The point of a clustered index is that it then sorts the data on disk around that index (hence why you can only have the one), so if your join data isn't being sorted by those exact columns as well, I don't think it'd make any difference. Plus by putting data that might change (as opposed to the key) into the clustered index, you make it more likely that things will need rebuilding peridically, slowing the overall database down.

Sorry if this sounds a daft question, but have you tried running your query through the index tuning wizard? Not foolproof by any stretch but I've had some decent improvements from it in the past.