This morning I had a performance issue on a piece of code that worked long ago on a different server, and they were trying to put it in place today. It was SLOW. Like, 10 minutes slow. With the added bonus that it's done through a web app, so it never finishes, it just always times out. After dealing with various approaches, I finally tried using the old Cardinality Estimator, and it went from 10 minutes to 3 seconds. But the query is inside the application, it doesn't call a stored procedure. Which means the devs changing it is Non-Trivial. So I went to an updated version of an old trick - query store hints (which used to be Plan Guides)
In theory you can probably get enough info from the query store/plan cache, so you can find the exact query
What I did:
- ran a profiler trace for that user/database. You could do this in Azure Data Studio for Azure SQL Database - I got "lazy", and since it was on a Managed Instance, used Profiler. Don't judge me. :)
- found the exact statement that their code ran. Easiest way to get this (it needs to be EXACT down to the white space) is to right-click on the cell in profiler, extract events, save to a file, then copy/paste out.
- found the AUDIT LOGIN from when it ran
- launched SSMS
- in one window, posted all of the SET QUOTED_IDENTIFIER (etc) statements from the AUDIT LOGIN, newline, "go", newline, then copy/paste from that second step. NO trailing anything. Ran it. Waited the 10 minutes for it to finish. You need to do it this way because the application default settings are not the same as the SSMS defaults (for more info, read Erland's seminal work "Slow in the Application, fast in SSMS?" https://www.sommarskog.se/query-plan-mysteries.html#defaultsettings), specifically 2.5.
- Expand that database in SSMS.
- Query Store->Top Resource Consuming Queries
- Mouse over and find the query in the left-side. Get the query_id (not the plan id). For me, it was 9605.
- run this in that database: EXEC sys.sp_query_store_set_hints @query_id= 9605, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';
- have people test it and make sure it works.
- later, go into Query Store->Tracked Queries, put in the number above (for me, 9605), and see if it's getting called, and how long it takes. Oddly, it doesn't show up in "Queries with forced plans". : ( Unsure how to find queries that we've tuned this way, maybe Managing Query Store Hints - Simple Talk (red-gate.com) ?).
