title: How to keep the following TSQL query from running my server at 100%?
body: <p>The following queries run in an sproc targeting the ItemData table in SQL Server 2008R2:</p>
  
  <pre><code>SELECT TOP(500) ItemListID, GeoCity, GeoState, GeoDisplay, Title, Link, Description, CleanDescription, OptimizedDescription, PubDateParsed, ImageBytes, DateAdded  FROM  (      SELECT TOP(500) ItemListID, GeoCity, GeoState, GeoDisplay, Title, Link, Description, CleanDescription, OptimizedDescription, PubDateParsed, ImageBytes, DateAdded, ROW_NUMBER()             
         OVER( ORDER BY ItemListID DESC )
         AS RowNumber  
         FROM ItemData  
         WHERE CONTAINS(Title, @FTSSearchTerm  ) -- ' + @OriginalSearchTerm + '"')   
         AND  ( WebsiteID=1 AND 
                                       (@GeoCity = '-1' OR GeoCity = @GeoCity)  AND
                                       (@GeoState = '-1' OR GeoState = @GeoState) )
                     ) ItemData   WHERE RowNumber &gt;= ( @PageNum - 1) * @PageSize + 1   AND RowNumber &lt;= @PageNum * @PageSize ORDER BY ItemListID DESC 
  
          SELECT @NumberOfResultsReturned = @@ROWCOUNT         
  
          SELECT @ActualNumberOfResults = COUNT(*) FROM ItemData WHERE CONTAINS(Title, @FTSSearchTerm  ) -- ' + @OriginalSearchTerm + '"')    AND  ( WebsiteID=1 AND       (@GeoCity = '-1' OR GeoCity = @GeoCity)  AND    (@GeoState = '-1' OR GeoState = @GeoState) )
  </code></pre>
  
  <p>Depending on the data the query uses either <code>CONTAINS</code> or <code>FREETEXT</code>.    </p>
  
  <p>With load this query runs very slow and peeks the server at 100%.</p>
  
  <p>I have set the following indexes:</p>
  
  <p><img src="https://i.stack.imgur.com/jJgfp.png" alt="enter image description here"></p>
  
  <p>What do I need to do so these queries stop running so hot?</p>
  
  <p>Thanks.</p>
  
  <p><strong>-- UPDATE --</strong></p>
  
  <p>The table has one clustered index which only consists of ItemListID, and FTS on Title and Description.</p>
  
  <p>I have added a non-clustered index (incorrectly named in the Identity name) as follows:</p>
  
  <p><img src="https://i.stack.imgur.com/a18nO.png" alt="Non-clustered indexes"> </p>