SQL Server 2005 Slow Query with Joins and Order By

I have a slow query in SQL Server 2005. It has 3 inner joins (on tables with a few tens of thousand rows to one with a million rows), and an order by clause.

All the join keys are uuid columns, but there is only ever an index on one of the tables (each table has a primary key as a type uniqueidentifier, and another table will have a column acting as a foreign key with this same value to join on, but it has no index on it).

I assume adding an index on the acting-as-foreign-key columns will help this immensely.

What are my other options to get the most out of optimizing this query?

Note: my db seems to have a CPU bottleneck, would it seem reasonable开发者_开发知识库 to think that this query (which is run often) could cause it? The db is only about 2gb and I have 4gb ram, so I doubt there is much I/O issues. Will the order by eat up CPU?

You need to index your join keys! If you join on multiple fields, add covering indexes that cover all those fields (in the same order they are in the query). That will probably take care of 90% of your performance issues.

It's possible to have a CPU bottleneck on a query like that since the server has to do table scans for all your joins.

If your joins are inner joins you can create an indexed view. This will give you runtime performance as if there were no joins and no sorting at all. It does not get any faster than that (at the expense of disk space). If your joins are outer joins there is a workaround: Insert a dummy row that serves as a "null" kind of value and switch to inner joins.

As JNK pointed out, it is still important to index your foreign key columns. It is unlikely, though possible, that you can get away with not indexing them at all.