SQLServerクエリの断続的なパフォーマンスの問題

何が起こっているのかを見ずに、何が起こっているのかを正確に知ることは困難です-特に失敗しているものでは。「良い」実行の実行プランは少し役立ちますが、悪い実行で何がうまくいかないかを推測しているだけです。

私の最初の推測（私のコメントと同様）は、それが期待するものの見積もりが非常に間違っており、それは非常に悪い計画を作成するということです。

Your TransactionRecord table in particular, with the detail column that is 1000 characters, would could have big issues with an unexpected large number of nested loops.

Indexes

The first thing I would suggest is indexing - particularly to a) only include a subset of the data you need for these, and b) to have them ordered in a useful manner.

I suggest the following two indexes would appear to help

CREATE INDEX IX_ContactRecord_User ON ContactRecord 
    (UserId, ContactSeq) 
    INCLUDE (ApplicationCD, Startdt);

CREATE INDEX IX_TransactionRecord_ContactSeq ON TransactionRecord 
    (ContactSeq, TransactionTypeCd);

These are both 'covering indexes', as well as being sorted in ways that can help. Alternatively, you could replace the first one with a slightly modified version (sorting first on ContactSeq) but I think the above version would be more useful.

CREATE INDEX IX_ContactRecord_User2 ON ContactRecord 
    (ContactSeq) 
    INCLUDE (ApplicationCD, Startdt, UserId);

Also, regarding the index on TransactionRecord - if this is the only query that would be using that index, you could improve it by creating the following index instead

CREATE INDEX IX_TransactionRecord_ContactSeq_Filtered ON TransactionRecord
    (ContactSeq, TransactionTypeCd) 
    WHERE (TransactionTypeCD <> '122');

The above is a filtered index that matches what's specified in the WHERE clause of your statement. The big thing about this is that it has already a) removed the records where the type <> '122', and b) has sorted the records already on ContactSeq so it's then easy to look them up.

By the way - given you asked about adding indexes on Foreign Keys on principle - the use of these really depends on how you read the data. If you are only ever referring to the referenced table (e.g., you have an FK to a status table, and only ever use it to report, in English, the statuses) then an index on the original table's Status_ID wouldn't help. On the other hand, if you want to find all the rows with Status_ID = 4, then it would help.

To help understanding indexes, I strongly recommend Brent Ozar's How to think like an SQL Server Engine - it really helped me to understand how indexes work in practice.

Use a sorted temp table

This may help but is unlikely to be the primary fix. If you pre-load the relevant UserIDs into a temporary table (with a primary key on UserID) then it may help with the relevant JOIN. It may also be easier for you to modify each run rather than have to modify the middle of the query.

CREATE TABLE #Users (UserId VARCHAR(10) PRIMARY KEY);
INSERT INTO #Users (UserID) VALUES
('1234567890'),
('1234567891');

Then replace the middle section of your query with

      where
         ContactSeq in 
         (
            select
               ContactSeq 
            from
               ContactRecord CR
               INNER JOIN #Users U ON CR.UserID = U.UserID 
         )
         and TransactionRecord.TransactionTypeCd not in 
         (
            '122'
         )

Simplify the query

I had a go at simplifying the query, and got it to this:

select  CR.UserId,
        max(CR.StartDt) as LastLoginDate 
from    ContactRecord CR
        INNER JOIN TransactionRecord TR ON CR.ContactSeq = TR.ContactSeq
where   TR.TransactionTypeCd not in ('122')
        AND CR.ApplicationCd not in ('1', '4', '5')
        AND CR.UserId in ('1234567890', '1234567891') -- etc
group by UserId;

or alternatively (with the temp table)

select  CR.UserId,
        max(CR.StartDt) as LastLoginDate 
from    ContactRecord CR
        INNER JOIN #Users U ON CR.UserID = U.UserID 
        INNER JOIN TransactionRecord TR ON CR.ContactSeq = TR.ContactSeq
where   TR.TransactionTypeCd not in ('122')
        AND CR.ApplicationCd not in ('1', '4', '5')
group by UserId;

One advantage of simplifying the query, is that it also helps SQL Server get good estimates; which in turn help it get good execution plans.

もちろん、上記が状況に応じてまったく同じレコードを返すことをテストする必要があります。テストするデータセットがないため、これらの簡略化されたバージョンが元のバージョンと100％一致するかどうかはわかりません。