1

I created the perfect index for a query1 that normally runs for more than a day. However, the new index is not picked by the optimizer, I suspect because it just re-uses the old plan.

I do not have DB level access, can I somehow make it pick a new plan from transaction ST04 (or DBACOCKPIT or DB02)?

1) with some outside help, that is not available any more

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
András
  • 1,326
  • 4
  • 16
  • 26
  • 3
    As a SAP user, you can't. If SAP used stored procedures (which it doesn't) then a DBA could `exec sp_recompile 'SomeStoredProcedureName'` which would cause a new plan to be generated next time that proc runs. The only alternatives are: let SQL server vacate the plan due to other memory pressure requirements; or `dbcc freeproccache` which dumps all plans from memory; or trip over the power cord. – AlwaysLearning Mar 29 '20 at 10:50
  • You might be able to use Query Store to force the desired plan. https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15 – David Browne - Microsoft Mar 29 '20 at 14:00

1 Answers1

0

DB20-> rebuilding statistic of table make rebuild execution plan in next call time.

Stas
  • 49
  • 7