1

I am migrating some data that's stored "key value" style, from an attributes table that uses an Object ID and an Attribute Type as a Clustered Index (I've also tried as a nonclustered index):

CREATE TABLE [dbo].[#attrs](
    [DataMigrationEventObjectID] [int] NOT NULL,
    [AttributeType] [varchar](128) NOT NULL,
    [AttributeValue] [varchar](255) NULL
) 
CREATE CLUSTERED INDEX pk ON #attrs ([DataMigrationEventObjectID],AttributeType);

I added the attribute value to select values into because the attributes table in the database has lots of other data and I can select it just for this migration event. The query with my test dataset to populate this table inserts ~3k rows and runs in under a second (there are about 50 objects total in my dataset with several attributes each).

The joins to the table in the query look like this, joining on the clustered index:

        INNER JOIN #attrs obj_gvn
        ON obj_gvn.DataMigrationEventObjectID = obj.DataMigrationEventObjectID
        AND obj_gvn.AttributeType = 'GivenName'

With 14 joins to this temp table, the query completes in a few seconds. With 15 joins, the query takes a minute, and with 16+ it is still running half an hour later.

I've checked all the joins for accidental conditions which would lead to returning too many rows, and when it returns in 1 min it only returns the right rows, so I don't think there is an accidental Cartesian join. Setting a MAXDOP value doesn't affect it and the query plan returned when the query runs for a minute doesn't flag any problems.

What have I missed about SQL that causes it to behave in this way with a lot of joins on a clustered index that should in theory be fast, with such a small number of records?


I can't get an Actual Execution Plan because the query doesn't complete and because it uses a Temp table I can't get an Estimated Plan for it. I tried to fudge the Temp Table as a real table in the DB and generate an estimated plan and the plan still hasn't been generated after 2 mins, so it looks like the delay is on the "creating a plan" side

Paste the Plan of a shortened version of the query: brentozar.com/pastetheplan/?id=Hy76dd92i

I have updated statistics for the database just in case and it is still not generating a plan.

I've worked with queries in the past with far more and far more problematic joins where the plan compilation is still instant. I feel the fact it's failing at the "generate plan" step has to mean something.

Updating to the latest CU unfortunately didn't help. sp_whoisactive just shows CPU usage climbing and climbing (screenshot), nothing on other resources that looks problematic.

It is my Dev machine so there is only one process active on SQL which is the query I'm running. There is nothing else so I assume it has to be SQL trying to generate the plan.

I suspect if I deployed this, Production would run it fine but it’s very strange to have a 'lots of joins on a primary key kill performance' issue. I might think about just blowing the dev server away and starting from scratch.

0

2 Answers 2

2

I'm not sure if this will resolve your issue, but have you tried pivoting the #attrs dictionary and then joining on it? I find sometimes taking a different tack will at least get it to work. Something like the below.

;WITH CTE_PVT AS
    (
    SELECT DataMigrationEventObjectID
        , ClientID 
        , ClientExternalID
        , TenantID
        , GivenName
        , OtherName
        , FamilyName
        , PreferredName
        , Title
    FROM [#attrs] AS A
        PIVOT (MAX([AttributeValue]) FOR [AttributeType] IN (ClientID, ClientExternalID, TenantID, GivenName
                                                                , OtherName, FamilyName, PreferredName, Title)) AS pvt 
    )
SELECT dmeo.[DataMigrationEventObjectID]
    , P.ClientID 
    , P.ClientExternalID
    , P.TenantID
    , P.GivenName
    , P.OtherName
    , P.FamilyName
    , P.PreferredName
    , P.Title
FROM DataMigrationEventObject dmeo
    LEFT OUTER JOIN CTE_PVT AS P ON P.DataMigrationEventObjectID = dmeo.DataMigrationEventObjectID
0
6

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.