How can I prompt SQL server to use more suitable index in query?



  • I have a query that somtimes runs longer than the application calling it allows. I've traced it down a clustered index seek on rather larger table. I've created a new index, that better supports this query, and it works great (one to one comparisons with hints to change index used).

    Problem is that when the application executes the query, SQL server is still using executing plans with the (slow) clustered index. It doesn't do this when I execute the exact same code in Management Studio.

    How can I get SQL server to use the better index? The query is generated by a third party component, so index hints are not an option. There is no forced execution plan for this query

    @@version gives me "Microsoft SQL Azure (RTM) - 12.0.2000.8", if that matters.

    Following comments, I've tried my hand at a plan guide. From "Tracked Queries" I get the query in question on the form "())"

    EXEC sp_create_plan_guide   
    @name = N'Guide1',   
    @stmt = N'',
    @type = N'SQL',  
    @module_or_batch = NULL,   
    @params = N'',   
    @hints = N'OPTION (TABLE HINT(, INDEX (myindex)))';  
    

    But to no avail. It's still using the same old execution plan.



  • I wolud suggest you to try this:

    • identify why sql server is using the wrong index; maybe there is something to fix instead of forcing a plan
    • if it is not possible you could force the query plan through the query store with sp_query_store_force_plan

    https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-query-store-force-plan-transact-sql?view=sql-server-ver15




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2