Index Needed?
Is a spool unavoidable in this situation?
Here is the query:
SELECT [t0].[Id] , [t0].[DefinitionId] , [t0].[Name] , [t0].[Description] , [t0].[IsActive] , [t0].[SourceKey] , [t0].[VersionStart] , [t0].[VersionEnd] , [t0].[IsComplete] , [t0].[AttributeHash] , [t5].[Id] AS [Id3] , [t5].[DimensionValueId] , [t5].[RangeFrom] , [t5].[RangeTo] , ( SELECT COUNT(*) FROM [dbo].[DimensionValueCriterion] AS [t6] WHERE [t6].[DimensionValueId] = [t0].[Id] ) AS [value] , [t4].[test] , [t4].[Id] AS [Id4] , [t4].[ColumnIndex] , [t4].[OrganizationId] , [t4].[Name] AS [Name2] , [t4].[Ordinal] , [t4].[SourceKey] AS [SourceKey2] , [t4].[IsActive] AS [IsActive2] , [t4].[Description] AS [Description2] , [t4].[IsComplete] AS [IsComplete2] , [t4].[DisplayAsAttribute] , [t4].[DefaultValue] , [t4].[Configuration] , [t4].[IsNavigable] , [t4].[test2] , [t4].[Id2] , [t4].[Name2] AS [Name3] , [t4].[Description2] AS [Description3] , [t4].[SourceKey2] AS [SourceKey3] , [t4].[PrimaryUnitOfMeasureId] , [t4].[CalculateBeginningBalance] , [t4].[UseConvertedAmount] , [t4].[ConfigurationData] , [t4].[IsComplete2] AS [IsComplete3] , [t4].[ReportingCurrencyId] FROM [dbo].[DimensionValue] AS [t0] LEFT OUTER JOIN ( SELECT 1 AS [test] , [t1].[Id] , [t1].[ColumnIndex] , [t1].[OrganizationId] , [t1].[Name] , [t1].[Ordinal] , [t1].[SourceKey] , [t1].[IsActive] , [t1].[Description] , [t1].[IsComplete] , [t1].[DisplayAsAttribute] , [t1].[DefaultValue] , [t1].[Configuration] , [t1].[IsNavigable] , [t3].[test] AS [test2] , [t3].[Id] AS [Id2] , [t3].[Name] AS [Name2] , [t3].[Description] AS [Description2] , [t3].[SourceKey] AS [SourceKey2] , [t3].[PrimaryUnitOfMeasureId] , [t3].[CalculateBeginningBalance] , [t3].[UseConvertedAmount] , [t3].[ConfigurationData] , [t3].[IsComplete] AS [IsComplete2] , [t3].[ReportingCurrencyId] FROM [dbo].[DimensionDefinition] AS [t1] LEFT OUTER JOIN ( SELECT 1 AS [test] , [t2].[Id] , [t2].[Name] , [t2].[Description] , [t2].[SourceKey] , [t2].[PrimaryUnitOfMeasureId] , [t2].[CalculateBeginningBalance] , [t2].[UseConvertedAmount] , [t2].[ConfigurationData] , [t2].[IsComplete] , [t2].[ReportingCurrencyId] FROM [dbo].[Organization] AS [t2] ) AS [t3] ON [t3].[Id] = [t1].[OrganizationId] ) AS [t4] ON [t4].[Id] = [t0].[DefinitionId] LEFT OUTER JOIN [dbo].[DimensionValueCriterion] AS [t5] ON [t5].[DimensionValueId] = [t0].[Id] WHERE NOT ( [t0].[VersionEnd] IS NOT NULL ) ORDER BY [t0].[Id] , [t4].[Id] , [t4].[Id2] , [t5].[Id]
Is a spool unavoidable in this situation?
No, but it is there for a reason. The index spool lazily caches the results of the outer join below it:
This takes advantage of the duplicate values of DefinitionId in the DimensionValue table. The query processor can save time and resources by only performing the join once per unique correlated DefinitionId. Without the spool, the whole join would be repeated for every DefinitionId, including all the duplicates.
So, this is a performance optimization for this particular plan shape. Do not be misled by the estimated cost of 55% for the spool. Something will always cost most in a plan, and the costs are in any case based on estimates only, which can turn out to be wrong or inexactly correlated to actual performance for all sorts of reasons.
How effective the spool is at run time depends on the number of duplicate DefinitionId values, the cost of the spool itself, and the cost of performing the join. If the question had provided a post-execution (actual) plan (with runtime counts and rewind/rebind spool information) it would have been easy to judge.
That said, the fact the question has been asked, and the apparent 5.3 second duration suggests performance is not as good as you would like it to be. It is hard to judge precisely without access to the source data, but it may well be the plan chosen by the optimizer in this case turns out not to be the best choice.
Ensuring statistics are representative of the data may help the optimizer find a better plan. A clue that statistics may be out-of-date is contained in the query plan. It shows, for example, that the DimensionValueCriterion table has no rows, which seems unlikely. Update the statistics as appropriate and see if you get a better execution plan.
Failing that, you may want to test the performance impact of forcing only hash or merge joins using an OPTION (HASH JOIN, MERGE JOIN) hint. If this is effective, and judged to be likely to remain a good choice as data changes over time, you may be able to add this hint to the query (without changing the query itself) using a Plan Guide.
The whole query takes about 9 seconds. No one is complaining, I was just trying to get it under 5 seconds.