SQL Server 2008 R2 Replicated database not using an index


  • Hi,

    We recently identified a performance issue with a long running query (usually around 9 seconds using an existing, different index) which is improved with the addition of a new non-clustered index.

    The new index has recently been applied to the primary database and performance improved to around 1-2 seconds.  This index has also been applied to our replicated database (which has exactly the same schema, data and indexes for the table as the primary database in this case) but it is not using this new index, rather the existing, different index.

    All indexes on our replicated database are applied manually rather than automatically.

    We know it's using the existing, different index from looking at actual execution plans.

    We've updated statistics for the table but still no improvement.

    When we initially applied the index to the primary database table we ran an sp_recompile on the table, but this was a step we'd added due to previous slow running stored procs after an upgrade.  That is the only difference we can think of between the steps we've taken in creating the index and can't see why that would make a difference if we updated the statistics for the table anyway.

    Can anyone offer any other checks or steps we could take to get queries to use the index (without using hints)?



    Friday, April 26, 2013 4:07 PM


All replies

  • Hi Chris,

    Yes, forcing a recompilation might help here.

    Brandon Williams (blog | linkedin)

    Friday, April 26, 2013 4:31 PM
  • Hi Brandon,

    Thanks for the quick response but can you give me any more of an explanation as to why?  I find my understanding of SQL Server starts to fall apart at this level as I seem to get contradicting views on what should have already happened and why.

    When the index was applied, the statistics should have been updated.  We thought because it was slow that this hadn't happened so we updated statistics without any luck.  I think even if it did work after an sp_recompile we would be pleased the problem was solved but would feel we didn't really understand why.

    In a way it's a shame we ran sp_recompile on the table on the primary database as a matter of routine, but in some ways that was itself a step that we've become accustomed to, due to SQL Server seemingly wanting to do it's own thing and not automatically updating execution plans for procs after a dependent table has been changed.



    Friday, April 26, 2013 4:48 PM
  • From what I understand, there are times when proc recompilation must be forced.  For example, adding an index that a procedure might benefit from does not cause automatic recompilation because the table itself did not change.  It wouldn't automatically be compiled until the first time it is executed after a SQL Server restart.  This is covered in Recompile a Stored Procedure.  In this case, forcing a recompile will optimize the query plan if the newly added index will potentially benefit the procedure.

    Brandon Williams (blog | linkedin)

    Friday, April 26, 2013 4:55 PM
  • Unfortunately that didn't work.  It isn't a stored proc that is taking 9 seconds on one database and 2 seconds on another, but a piece of sql generated from EF but the "sp_recompile tablename" was the only difference between deploying the two systems.  This is a bit of a headache as there is no reason for this performance difference.
    Monday, April 29, 2013 9:24 AM
  • Hi Chris,

    You said the query use the existing index, not use the new created non-clustered index. The performance is slow. Right?

    SQL Server Query Optimizer uses the cost to make the decision on selecting the most efficient execution plan. When any query is executed, the SQL Server Query Optimizer prepares several alternative execution plans to execute the query. I think SQL Server would balance it and not always choose the best plan.

    If you need to force the query to use the index, I think hint is OK.


    Maggie Luo
    TechNet Community Support

    Monday, May 06, 2013 9:12 AM
  • You may be running into parameter sniffing. You may need to add the with recomplile option to your stored procedure.

    looking for a book on SQL Server 2008 Administration? looking for a book on SQL Server 2008 Full-Text Search?

    Monday, May 06, 2013 4:03 PM