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.
Best Answer
After discussion, the issue here is that the optimizer in SQL Server/SQL Azure currently "sniffs" current parameter values when compiling the query. The query plan is then cached and used on future executions of the query. The plan that is generated may be more optimal for the originally sniffed value, but if other values are more commonly used at execution time (or, more broadly, if the impact on the rest of the query is different statistically such as one value being uncommon when sniffed vs. more common cases being sniffed), then this can be a source of runtime variance in a data application. There are a few knobs to control this at different scopes, but using the option OPTIMIZE FOR UNKNOWN query hint will cause the optimizer to not sniff. This will generate a query plan that is more generic (but still usually reasonably fast).
The original question suggested that clearing out the query store "fixed" the problem, but this was not actually the real cause. It so happens that clearing the query store will force recompiles for all the query plans it was tracking on the next execution of each query. That just allowed the optimizer to sniff a different value and, at least for the customer's experience, it happened to be better in that experiment. Anything that forces a recompile would lead to a similar result. You could do DBCC FREEPROCCACHE (empties the plan cache), reboot your server, force/unforce the plan for the query in query store, any metadata change to the database or tables involved in the query, etc.