SQL Server database Performance Issue

Question

I have a query which randomly starts using a different index instead of the index it is supposed to use and the performance of the query goes bad as it produces a bad execution plan and all following executions are bad. The query generates from the application by a user action on the application. The query generated is always same with the same sql hash. Previously when this issue used to happen, I used to remove the bad plan from the cache and using query store, I used to force the good execution plan and the query used to go back to normal performance. But now, this solution is not working anymore. The query is consistently picking the wrong index and hence producing the wrong execution plan. The indexes are not fragmented and the statistics are also updated. Please see the below query:

SELECT test_.ROWID, 
   test_.* 
FROM ******.test_table test_ 
WHERE test_.STOFCY_0 = @P1 
AND test_.VCRTYP_0 = @P2 
AND test_.VCRNUM_0 = @P3 
AND test_.VCRLIN_0 = @P4 
AND test_.REGFLG_0 <> @P5 
AND test_.QTYSTU_0 < @P6 
ORDER BY test_.STOFCY_0,
   test_.UPDCOD_0,
   test_.ITMREF_0,
   test_.IPTDAT_0 Desc,
   test_.MVTSEQ_0,
   test_.MVTIND_0 Option (FAST 1)

The query is supposed to return 1 row always.

I need help in fixing this issue as I have tried the following and nothing seems to work to fix this permanently:

• I have tried to rebuild indexes and update statistics.
• Remove bad execution plan and force the good execution plan using query store. This works for a few minutes but after that the query picks the bad plan again.
• It seems that the SQL optimizer is estimating wrong number of rows.
• I don't have control on changing the query as this is generated from the application
• As long as the query is able to use the index I want it to use, the query works fine.
• Earlier the attached index only had the columns of the OrderBy clause. I added the columns of the WHERE clause as well but that also doesn't seem to force the query to always use this index.

Index DDL:

    CREATE UNIQUE NONCLUSTERED INDEX [test_table_index] ON [test_table]
    (
        [STOFCY_0] ASC,
        [VCRTYP_0] ASC,
        [VCRNUM_0] ASC,
        [VCRLIN_0] ASC,
        [REGFLG_0] ASC,
        [QTYSTU_0] ASC,
        [UPDCOD_0] ASC,
        [ITMREF_0] ASC,
        [IPTDAT_0] DESC,
        [MVTSEQ_0] ASC,
        [MVTIND_0] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Answer 1

I'm not sure your "good" plan is that good, it still has an RID lookup. Which in turn also implies you don't have a clustered index, another bad sign.

I think you should make this index clustered, rather than non-clustered. This means that the query can be entirely satisfied using the index, and no lookup or sort is needed at all.

You should also move the two inequality columns REGFLG_0 and QTYSTU_0 to the end of the index order, so that the sort will work correctly.

DROP INDEX STOJOU_STJ0 ON STOJOU;

CREATE UNIQUE CLUSTERED INDEX STOJOU_STJ0
ON STOJOU_STJ0
(
    STOFCY_0 ASC,
    VCRTYP_0 ASC,
    VCRNUM_0 ASC,
    VCRLIN_0 ASC,
    UPDCOD_0 ASC,
    ITMREF_0 ASC,
    IPTDAT_0 DESC,
    MVTSEQ_0 ASC,
    MVTIND_0 ASC,
    REGFLG_0 ASC,
    QTYSTU_0 ASC
);

I must say, the design is a bit suspicious. Do you really need so many columns to get a unique row? And your column names are awful, you should use more descriptive ones.