In general
Compilation time for a query with a large number of joins can be very variable.
One reason for this is there are N! ways of ordering the inner joins of N tables. For small N, this isn't an issue. For larger N it can be a problem, even though the optimizer doesn't attempt an exhaustive search of the possible plan space.
You might have noticed that the execution plan you provided doesn't access tables in the same order you wrote them. For example, the table DataMigrationEventObject (aliased as 'obj') is accessed first. There isn't an obvious single index available, so the optimizer creates an index intersection (using two nonclustered indexes) and a Key Lookup (fetching column TargetObjectKey1) to physically implement that small part of your query.
The optimizer isn't limited to considering different join orders either. It might explore different join algorithms (hash, merge, nested loop, apply), different indexing strategies, placement and type of non-join operators, parallelism and so on.
The optimizer has heuristics to avoid spending too much time finding a better plan if the best one it has found so far is reasonable. Given a reasonably straightforward query, a decent database logical design, and a suitable physical implementation (e.g. data types and indexes) the optimizer will most often find a good low-cost plan quickly. This seems to have been your experience in the past.
This arrangement can still go wrong in multiple ways. If the initial join order chosen by the optimizer is a long way from reasonably low-cost, a lot of transformation work will need to be done to get to the finish line. If the database doesn't have good supporting indexes, the optimizer can spend a lot of time trying to find a decent strategy.
If row count and value distribution information can't be derived accurately (not limited to out-of-date base table statistics) plan fragment candidates may be costed incorrectly. This can easily lead to heuristic thresholds being breached, and the optimizer spending an inordinate amount of effort exploring, implementing, and costing alternatives.
When this happens, sometimes the only solution is to express the data requirement using different syntax, a different algorithm (as in the pivot suggestion) or breaking the operation down into simpler separate steps with small and well-indexed temporary table(s) as holding areas.
In general, it seems most likely the problem in your case is a specific data distribution that leads to inaccurate early costing and excessive optimizer effort as a result.
Specifically
Your query and database have multiple issues that make them vulnerable to excessive plan compilation time. I'm not going to list all of them because a complete schema was not provided, and the query text found in the supplied execution plan is truncated at a crucial point. The answer would be far too long even if all necessary information were provided. Some points:
- The indexing on the base tables is not ideal (see comments about the index intersection and lookup above)
- The clustered index labelled pk (for primary key?) on the
#attrs temporary table is not marked UNIQUE. Keys are unique by definition. Not providing this crucial guarantee to the optimizer means it cannot know that an equality predicate on the object id and type will return at most one row. There are other consequences as well, too many to list, but as one example a merge join would have to be of the many-to-many variety instead of one-to-many. These things matter for exploration and costing.
- The query uses nested join syntax around the 'int_att' alias. This may or may not be avoidable, but it makes the initial logical tree generated as input to the optimizer more complex. Unnecessary complexity can easily lead to suboptimal processing.
- The
#attrs table has a duplicate index named 'clus'. This was alluded to in the question, but it's an example of a non-obvious indexing choice presented to the optimizer for no gain. Make the clustered index a proper UNIQUE key instead.
- The query to load the
#attrs table has a duplicate predicate DataMigrationEventID = @MigrationEventID. This is harmless, but attention to detail is important when dealing with computers (they're very literal).
- The database is stuck in compatibility level 130 (SQL Server 2016). Upgrading to the latest 2019 CU is all very well, but compatibility level overrides many improvements. If you don't already have optimizer fixes enabled for the database, you should think about turning that on as well.
- There is no benefit (only cost) to creating the
#theClients temporary table using SELECT INTO then immediately returning all results from that table. Temporary tables are a powerful tuning tool when used correctly. This is not an example of such usage.
Repro
For anyone wanting to generate a plan locally, I was able to infer the following from the question and execution plan. There may be some omissions and inaccuracies. Naturally there are no accurate statistics, just a raw table cardinality:
Tables
CREATE TABLE dbo.DataMigrationEventObject
(
DataMigrationEventObjectID integer NOT NULL PRIMARY KEY,
DataMigrationEventID integer NOT NULL,
DataMigrationObjectType varchar(255) NOT NULL,
TargetObjectKey1 integer NOT NULL,
TargetObjectKey2 integer NOT NULL,
TargetObjectKey3 integer NOT NULL,
IsCompleted bit NOT NULL,
IsDeleted bit NOT NULL,
INDEX DataMigrationEventID (DataMigrationEventID),
INDEX DataMigrationObjectType (DataMigrationObjectType)
);
UPDATE STATISTICS dbo.DataMigrationEventObject WITH ROWCOUNT = 8413, PAGECOUNT = 841;
CREATE TABLE dbo.DataMigrationEventObjectAttribute
(
DataMigrationEventObjectID integer NOT NULL PRIMARY KEY,
DataMigrationEventID integer NOT NULL,
AttributeType varchar(128) NOT NULL,
AttributeValue sql_variant NOT NULL,
IsDeleted bit NOT NULL,
);
UPDATE STATISTICS dbo.DataMigrationEventObjectAttribute WITH ROWCOUNT = 56900, PAGECOUNT = 56900;
CREATE TABLE dbo.#attrs
(
DataMigrationEventObjectID integer NOT NULL,
AttributeType varchar(128) NOT NULL,
AttributeValue varchar(255) NULL
);
CREATE CLUSTERED INDEX pk ON #attrs (DataMigrationEventObjectID, AttributeType);
DECLARE @MigrationEventID integer = 23;
INSERT INTO #attrs
SELECT
dmeo.DataMigrationEventObjectID,
a.AttributeType,
CAST(a.AttributeValue as varchar(255)) AttributeValue
FROM DataMigrationEventObject dmeo
JOIN DataMigrationEventObjectAttribute a
ON a.DataMigrationEventObjectID = dmeo.DataMigrationEventObjectID
WHERE
dmeo.DataMigrationEventID = @MigrationEventID
AND dmeo.DataMigrationObjectType IN ('BpClient','xxxxxxxClientMapping')
AND dmeo.DataMigrationEventID = @MigrationEventID;
CREATE INDEX clus ON #attrs (DataMigrationEventObjectID, AttributeType);
UPDATE STATISTICS #attrs WITH ROWCOUNT = 2173, PAGECOUNT = 21;
Query
DECLARE @MigrationEventID integer = 23;
SELECT
dmeo.DataMigrationEventObjectID xxObjectID,
a_clientid.AttributeValue ClientID,
a_tnt.AttributeValue ClientExternalID,
a_tnt.AttributeValue TenantID,
int_att.AttributeValue ClientInternalID,
obj_gvn.AttributeValue GivenName,
obj_oth.AttributeValue OtherName,
obj_fam.AttributeValue FamilyName,
obj_pref.AttributeValue PreferredName,
obj_title.AttributeValue Title,
obj_dob.AttributeValue DateOfBirth,
obj_email.AttributeValue Email,
obj_hphone.AttributeValue HomePhone,
obj_wphone.AttributeValue WorkPhone,
obj_add1.AttributeValue Address1,
obj_add2.AttributeValue Address2,
obj_sub.AttributeValue Suburb,
obj_state.AttributeValue StateID,
obj_post.AttributeValue Postcode,
obj_sex.AttributeValue SexID
/*,
obj_del.AttributeValue IsDeleted,
obj_inact.AttributeValue IsInactive,
obj_pref_corr.AttributeValue PreferredCorrespondenceMethodID,
obj_eth.AttributeValue EthnicityID
obj_em_title.AttributeValue EmergencyContactTitle,
obj_em_surn.AttributeValue EmergencyContactSurname,
obj_em_gvn.AttributeValue EmergencyContactGivenName,
obj_em_add.AttributeValue EmergencyContactAddress,
obj_em_sub.AttributeValue EmergencyContactSuburb,
obj_em_post.AttributeValue EmergencyContactPostcode,
obj_em_phone.AttributeValue EmergencyContactPhone,
obj_em_phone2.AttributeValue EmergencyContactPhone2,
obj_em_rel.AttributeValue EmergencyContactRelationship,
obj_nok_title.AttributeValue NextOfKinContactTitle,
obj_nok_surn.AttributeValue NextOfKinContactSurname,
obj_nok_gvn.AttributeValue NextOfKinContactGivenName,
obj_nok_add.AttributeValue NextOfKinContactAddress,
obj_nok_sub.AttributeValue NextOfKinContactSuburb,
obj_nok_post.AttributeValue NextOfKinContactPostcode,
obj_nok_phone.AttributeValue NextOfKinContactPhone,
obj_nok_phone2.AttributeValue NextOfKinContactPhone2,
obj_nok_rel.AttributeValue NextOfKinContactRelationship*/
INTO #theClients
FROM DataMigrationEventObject dmeo
INNER JOIN #attrs a_clientid
ON a_clientid.DataMigrationEventObjectID = dmeo.DataMigrationEventObjectID
AND a_clientid.AttributeType = 'ClientID'
INNER JOIN #attrs a_clientextid
ON a_clientextid.DataMigrationEventObjectID = dmeo.DataMigrationEventObjectID
AND a_clientextid.AttributeType = 'ClientExternalID'
INNER JOIN #attrs a_tnt
ON a_tnt.DataMigrationEventObjectID = dmeo.DataMigrationEventObjectID
AND a_tnt.AttributeType = 'TenantID'
INNER JOIN (
#attrs int_att
INNER JOIN DataMigrationEventObject obj
ON obj.DataMigrationEventObjectID = int_att.DataMigrationEventObjectID
AND obj.DataMigrationObjectType = 'BpClient'
)
ON int_att.AttributeType = 'InternalID'
AND obj.DataMigrationEventID = dmeo.DataMigrationEventID
AND obj.TargetObjectKey1 = cast(a_clientextid.AttributeValue as int)
INNER JOIN #attrs obj_gvn
ON obj_gvn.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
AND obj_gvn.AttributeType = 'GivenName'
INNER JOIN #attrs obj_oth
ON obj_oth.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
AND obj_oth.AttributeType = 'OtherName'
INNER JOIN #attrs obj_fam
ON obj_fam.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
AND obj_fam.AttributeType = 'FamilyName'
INNER JOIN #attrs obj_pref
ON obj_pref.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
AND obj_pref.AttributeType = 'PreferredName'
INNER JOIN #attrs obj_title
ON obj_title.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
AND obj_title.AttributeType = 'Title'
INNER JOIN #attrs obj_dob
ON obj_dob.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
AND obj_dob.AttributeType = 'DateOfBirth'
INNER JOIN #attrs obj_email
ON obj_email.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
AND obj_email.AttributeType = 'Email'
INNER JOIN #attrs obj_hphone
ON obj_hphone.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
AND obj_hphone.AttributeType = 'HomePhone'
INNER JOIN #attrs obj_wphone
ON obj_wphone.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
AND obj_wphone.AttributeType = 'WorkPhone'
INNER JOIN #attrs obj_add1
ON obj_add1.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
AND obj_add1.AttributeType = 'Address1'
INNER JOIN #attrs obj_add2
ON obj_add2.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
AND obj_add2.AttributeType = 'Address2'
INNER JOIN #attrs obj_sub
ON obj_sub.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
AND obj_sub.AttributeType = 'Suburb'
INNER JOIN #attrs obj_state
ON obj_state.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
AND obj_state.AttributeType = 'StateID'
INNER JOIN #attrs obj_post
ON obj_post.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
AND obj_post.AttributeType = 'Postcode'
INNER JOIN #attrs obj_sex
ON obj_sex.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
AND obj_sex.AttributeType = 'SexID'
--INNER JOIN #attrs obj_del
--ON obj_del.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
--AND obj_del.AttributeType = 'IsDeleted'
--INNER JOIN #attrs obj_inact
--ON obj_inact.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
--AND obj_inact.AttributeType = 'IsInactive'
--INNER JOIN #attrs obj_pref_corr
--ON obj_pref_corr.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
--AND obj_pref_corr.AttributeType = 'PreferredCorrespondenceMethodID'
--INNER JOIN #attrs obj_eth
--ON obj_eth.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
--AND obj_eth.AttributeType = 'EthnicityID'
WHERE
dmeo.DataMigrationEventID = @MigrationEventID
AND dmeo.IsDeleted = 0
AND dmeo.DataMigrationObjectType = 'xxxxxxxClientMapping';
That demo does not reproduce excessive compilation time on my SQL Server 2019 instance under compatibility level 130 but that's not unexpected given the lack of accurate statistics and likely different hardware and instance configuration (primarily memory) all of which affects plan choice.
A longer compilation time (and a pretty crazy plan) can be reproduced by using the original cardinality model. Add the following hint to the final query:
OPTION (MAXDOP 1, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))
I have used MAXDOP 1 since your instance is configured that way.
