title: SQL Server Query intermittent performance Issue
body: <p>Recently we have run into performance issues with a particular query on SQL Server (2016). The problem I'm seeing is that the performance issues are incredibly inconsistent and I'm not sure how to improve this.</p>
  <p>The table details:</p>
  <pre><code>CREATE TABLE ContactRecord 
  (
    ContactSeq BIGINT NOT NULL 
  , ApplicationCd VARCHAR(2) NOT NULL 
  , StartDt DATETIME2 NOT NULL 
  , EndDt DATETIME2 
  , EndStateCd VARCHAR(3) 
  , UserId VARCHAR(10) 
  , UserTypeCd VARCHAR(2) 
  , LineId VARCHAR(3) 
  , CallingLineId VARCHAR(20) 
  , DialledLineId VARCHAR(20) 
  , ChannelCd VARCHAR(2) 
  , SubChannelCd VARCHAR(2) 
  , ServicingAgentCd VARCHAR(7) 
  , EucCopyTimestamp VARCHAR(30) 
  , PRIMARY KEY (ContactSeq)
  , FOREIGN KEY (ApplicationCd) REFERENCES ApplicationType(ApplicationCd)
  , FOREIGN KEY (EndStateCd) REFERENCES EndStateType(EndStateCd)
  , FOREIGN KEY (UserTypeCd) REFERENCES UserType(UserTypeCd)
  )
  
  CREATE TABLE TransactionRecord 
  (
    TransactionSeq BIGINT NOT NULL 
  , ContactSeq BIGINT NOT NULL 
  , TransactionTypeCd VARCHAR(3) NOT NULL 
  , TransactionDt DATETIME2 NOT NULL 
  , PolicyId VARCHAR(10) 
  , ProductId VARCHAR(7) 
  , EucCopyTimestamp VARCHAR(30) 
  , Detail VARCHAR(1000) 
  , PRIMARY KEY (TransactionSeq)
  , FOREIGN KEY (ContactSeq) REFERENCES ContactRecord(ContactSeq)
  , FOREIGN KEY (TransactionTypeCd) REFERENCES TransactionType(TransactionTypeCd)
  )
  </code></pre>
  <p>Current record counts:</p>
  <ul>
  <li><code>ContactRecord</code> 20million</li>
  <li><code>TransactionRecord</code> 90million</li>
  </ul>
  <p>My query is:</p>
  <pre><code>select
     UserId,
     max(StartDt) as LastLoginDate 
  from
     ContactRecord 
  where
     ContactSeq in 
     (
        select
           ContactSeq 
        from
           TransactionRecord 
        where
           ContactSeq in 
           (
              select
                 ContactSeq 
              from
                 ContactRecord 
              where
                 UserId in 
                 (
                    '1234567890',
                    '1234567891' -- Etc.
                 )
           )
           and TransactionRecord.TransactionTypeCd not in 
           (
              '122'
           )
     )
     and ApplicationCd not in 
     (
        '1',
        '4',
        '5'
     )
  group by
     UserId;
  </code></pre>
  <p>Now the query isn't great and could be improved using joins, however it does fundamentally work.
  The problem I'm having is that our data job takes an input of roughly 7100 userIds. These are then broken up into groups of 500. For each 500 these are used in the <code>IN</code> clause in this query. The first 14 executions of this query with 500 items in the <code>IN</code> clause execute fine. Results are returned in roughly 15-20 seconds for each.</p>
  <p>The issue is with the remaining 100 give or take for the last execution of this query. It never seems to complete. It just hangs. In our data job it is timing out after 10 minutes. I have no idea why. I'm not an expert with SQL Server so I'm not really sure how to debug this. I have executed each sub query independently and then replaced the contents of the sub query with the returned data. Doing this for each sub query works fine.</p>
  <p>Any help is really appreciated here as I'm at a loss to how this works so consistently with larger amounts of parameters but just doesn't work with only a fraction.</p>
  <p><strong>EDIT</strong></p>
  <p>I've got three example of execution plans here. Please note that each of these were executed on a test server and all executed almost instantly as there is very little data on this test equivalent.</p>
  <p>This is the execution plan for 500 arguments which executes fine in production, returning in roughly 15-20 seconds:
  <a href="https://i.stack.imgur.com/yN0yy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/yN0yy.png" alt="Execution plan for 500 arguments" /></a></p>
  <p>This is the execution plan for 119 arguments which is timing out in our data job after 10 minutes:
  <a href="https://i.stack.imgur.com/bypN1.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bypN1.png" alt="Execution plan for 119 arguments" /></a></p>
  <p>This is the execution plan for 5 arguments which executes fine. This query is not explicitly being executed in the data job but just for comparison:
  <a href="https://i.stack.imgur.com/YeO5U.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/YeO5U.png" alt="Execution plan for 5 arguments" /></a></p>
  <p>In all instances SSMS has given the following warning:</p>
  <pre><code>/*
  Missing Index Details from SQLQuery2.sql
  The Query Processor estimates that implementing the following index could improve the query cost by 26.3459%.
  */
  
  /*
  USE [CloasIvr]
  GO
  CREATE NONCLUSTERED INDEX [&lt;Name of Missing Index, sysname,&gt;]
  ON [dbo].[TransactionRecord] ([TransactionTypeCd])
  INCLUDE ([ContactSeq])
  GO
  */
  </code></pre>
  <p>Is this the root cause to this problem?</p>