title: SQL "find missing index" script suggests missing index that already exists? Query in top 10 CPU list
body: <p>One query is in the top of the most cpu intensive list, but it's simplicity (see example below) contradicts this. However the "find missing index" script suggests creating an index for the table... but this exact (column order and include) index already exists. </p>
  
  <pre><code>SELECT COUNT(Id) 
    FROM dbo.ProductOrder
   WHERE userId = @userId
     AND status = @status
  </code></pre>
  
  <p>The "find missing index" script suggest an index on userId and status.</p>
  
  <p>We noticed the missing index suggest a couple of weeks ago and found a post (somewhere) that this would be a bug in SQL Server R2 SP1 (the version we use).</p>
  
  <p>But now the query is (and stays) in the top 10... I'm not sure anymore.</p>
  
  <p>Things we tried:</p>
  
  <ul>
  <li>Adding the Id to the include list (not suggested)</li>
  <li>NOLOCK / READ UNCOMMITED transaction isolation level</li>
  <li>sp_recompile on the table</li>
  </ul>
  
  <p>We have a tight maintenance schedule ensuring fragmentation is kept to a minimum.</p>
  
  <p>What could be happing here?</p>
  
  <p>Edit: I mentioned execution plan when I started the post, but the execution plans don't actually mention the index (anymore). The "find missing index" script does.</p>
  
  <p>Edit 2: Index definition</p>
  
  <pre><code>CREATE NONCLUSTERED INDEX [ProductOrder_UserStatus_Nidx] ON [dbo].[ProductOrder]
  (
      [userId] ASC,
      [status] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
  GO
  </code></pre>
  
  <p>Edit 3: Missing Index output</p>
  
  <pre><code>statement: dbo.ProductOrder
  improvement_measure: 187243,941382055
  create_index_statement: CREATE INDEX [missing_index_4_3_ProductOrder] ON [dbo].[ProductOrder] ([userId], [status])
  group_handle: 4
  unique_compiles: 10
  user_seeks: 51161
  user_scans: 0
  last_user_seek: 2014-06-22 10:06:12.390
  last_user_scan: NULL
  avg_total_user_cost: 8,77252167199463
  avg_user_impact: 41,72
  system_seeks: 0
  system_scans: 0
  last_system_seek: NULL
  last_system_scan: NULL
  avg_total_system_cost: 0
  avg_system_impact: 0
  database_id: 8
  object_id: 2014122416
  </code></pre>
  
  <p><strong>Update: New script found right query</strong></p>
  
  <p>We we're looking at the wrong query. A new script found the one that actually produces the missing index statement in the query plan:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[ProductOrder_GetByUserAndStatus]
    @userId INT, 
    @status INT,
    @deliveryStatus INT
  AS
  
   SELECT U.id
        , U.email
        , U.[language]
        , U.username
        , U.firstname + ' ' + ISNULL(u.middlename, '') + ' ' + u.lastname AS fullname
        , pp.[product]
        , pp.[status]
        , pp.[deliveryStatus]
     FROM [dbo].[ProductOrder] PO
     JOIN [dbo].[User] U ON U.[id] = PO.[userId] 
      AND PO.[pool] = @userId 
        AND PO.[status] = @status
      AND PO.[deliveryStatus] = ISNULL(@deliveryStatus, PO.[deliveryStatus])
  
  RETURN 0
  </code></pre>
  
  <p>Maybe SQL server is lost because of the last line in the where clause that implements the optional filter parameter? </p>
  
  <p>I've tried creating updating the index to include the deliveryStatus in the column list as well as the INCLUDE statement (in two seperate updates, running statistics an recompile); the first solution got a performance degradation (missing index remained in plan) and the second showed no difference.</p>
  
  <p>Close this issue as a bug in SQL server?</p>