title: SQL Server Query Optimizer Execution Plan ""Reason for Early Termination of Statement: TimeOut"
body: <p>Environment: 
  Windows 2008 R2, SQL Server 2008 SP1</p>
  
  <p>Problem: 
  Table inner Join Query with 9 tables gets "Reason for Early Termination of Statement: TimeOut" in Actual Execution Plan. </p>
  
  <p>Wrong Memory Grant is assigned. </p>
  
  <p>Sorting was done in tempdb instead of ram even there is plenty of ram available.</p>
  
  <p>Performance is slow when sorting is done in tempdb.</p>
  
  <p>To Consider: 
  If I Drop, Create, and Drop one of the index from one of the 9 tables, query will get full optimization again and sorting is done in ram with full performance. However, as soon as I modify the query a little bit, it would lose the full optimization again. </p>
  
  <p>Question: 
  Has anyone seen that problem before? </p>
  
  <p>Is there any way to get more information on why the "timeout" happening? </p>
  
  <p>What is the Optimizer really doing? </p>
  
  <p>I would consider making a ramdisk for tempdb if there is no solution how to ratify the query optimizer problem. What are the risks to use ramdisk for tempdb? </p>
  
  <p>Things I have tried already:
  UPDATE STATS ON {ALL 9 Tables} WITH FULLSCAN </p>
  
  <p>INDEX REBUILD works temporarily like the DROP CREATE DROP action I did above.</p>