I have a Azure SQL database (compatibility level = 150). I'm running into a performance problem executing a database query.
So after I've rebuilt all my indexes, the query runs fast. When testing, with different parameters, all of a sudden it slows down. The same query with the same parameters is fast in the beginning, and slow after I do around 50 tests.
Now when I purge the Query store data via the UI or via SQL:
ALTER DATABASE [mydb] SET QUERY_STORE CLEAR;
the performance is back.
I've tried all settings on the Query Store, the problem remains, only deleting the data collected gives me back the initial performance.
Ok, so executing
DBCC FREEPROCCACHE WITH NO_INFOMSGS; had the same performance impact as clearing the query store.
The query is built form my data model in Power BI (direct query). It can be fast for all possible parameter combinations. I tested all of these when the cache was recently cleared.
Power BI sends the query to Azure SQL db with a syntax for parameterized queries. I just becomes slower and slower.
So is it the right solution,
optimize for unknown, knowing that it can be fast for that same parameters?
Underneath the that is called from the app, I have 3 table-valued functions. I changed the 3 off them with
OPTION (OPTIMIZE FOR UNKNOWN), update the statistics and rebuild all indexes. I must say the performance stays, which is good, although it's still a bit black magic for me.