SQL Server execution plan is using PK instead of index
SELECT Project.CalculateSequencialProject, Project.CreateDate, Project.OpeningDate, Project.ForecastClosingDate, Project.RealClosingDate, Project.ProjectStatus, Unit.Name AS UnitName, UTE.Name AS UTEName, Process.Name AS Process FROM Project LEFT JOIN Unit ON Project.Unit_Id = Unit.Id AND Project.Company_Id = Unit.Company_Id LEFT JOIN UTE ON Project.UTE_Id = UTE.Id LEFT JOIN Process ON Project.Process_Id = Process.Id WHERE Project.Company_Id = '????????????????'
These are the two indexes referencing Company_Id column, however this table have more than 15 indexes that don't reference it.
CREATE NONCLUSTERED INDEX [IX_Company_Id] ON [dbo].[Project] ( [Company_Id] ASC ) CREATE NONCLUSTERED INDEX [IX_Project_02] ON [dbo].[Project] ( [ProjectType_Id] ASC, [ProjectStatus] ASC, [Unit_Id] ASC, [Id] ASC, [ProjectDirector_Id] ASC, [UTE_Id] ASC, [Company_Id] ASC, [LostType_Id] ASC, [Pillar_Id] ASC, [ProjectPhase_Id] ASC ) INCLUDE ( [CalculateSequencialProject], [DescriptionTittle], [OpeningDate], [ForecastClosingDate], [RealClosingDate])
It is using the PK instead of my indexes. Why?
Extra question: Every JOIN is causing a Hash Match, is that ok? I mean considering that the final query will have more than 10 JOINs.
So, you might try dropping both indexes, and creating this index instead (this is just a first stab):
CREATE NONCLUSTERED INDEX [IX_Project_02a] ON [dbo].[Project] ( [Company_Id], -- made this the leading key column [Unit_Id], -- moved this up to help facilitate the first join [Process_Id] -- added this to help facilitate the second join -- removed several columns here -- they are not necessary for this query, and if they are -- necessary for other queries, may be better as INCLUDE ) INCLUDE ( [CalculateSequencialProject], [ProjectStatus], -- for this query, does not need to be in the key [CreateDate], [OpeningDate], [ForecastClosingDate], [RealClosingDate] );
(Note that the second index may actually be useful for other queries, so check index_usage_stats, don't just drop it because I said so. The first index, on just Company_Id, is highly unlikely to be useful for any queries.)
A hash match is generally suboptimal; it means that the inputs are not sorted. Better indexes can usually eliminate those.
It shouldn't take that long. You must be using an underpowered (or cloud) server. If you had run the query from within Plan Explorer, it would have included I/O metrics and other useful performance information.
however this table have more than 15 indexes that don't reference it.
This is a red flag that your table may be too wide, and/or that your indexing strategy is not well-planned. Try to normalize your table design and give proper thought to your indexes. You should fix the spelling errors like DescriptionTittle and CalculateSequencialProject while you're at it.
It is using the PK instead of my indexes. Why?
Because there is no index with Company_Id as the leading key that can also provide the other columns needed in the query.
Every JOIN is causing a Hash Match, is that ok? I mean considering that the final query will have more than 10 JOINs.
With the tiny number of rows in your tables at the moment, yes. It is probably optimal. The cause of the slow performance is almost certainly elsewhere – probably a hardware issue.
Writing a query with 10 joins is often a sign of trying to do too much in one query. Instead of writing one monster query (which you will have endless trouble optimizing), break the query into manageable blocks, and use temporary tables (indexed if necessary) to hold small intermediate results.
You will get better quality execution plans, and more predictable performance this way. Your final queries will likely be comprehensible by mere humans as an additional benefit. Maintaining queries usually represents more of the lifetime cost of a database than the initial implementation, so writing simpler queries usually pays off in the long run.
You appear to be using uniqueidentifier as the type for your Id columns. This often indicates a lack of understanding of the tool you're using (Entity Framework?) and what data types make good primary keys.
Your tables contain very few rows. If you intend to scale this, you need to develop and test with a representative data volume and value distribution.
You should get into the habit of prefixing your table references with the schema prefix, perhaps it is dbo.Process, for example.
You should also get into the habit of using aliases for your object references e.g. dbo.Project AS P.
If you really want to test the performance of hash joins versus lookups, you may, for testing purposes, put a WITH (FORCESEEK) hint on the table references in your query.
Ultimately, I am suggesting you employ a decent DBA to assist you at this stage.