BAQ 100x slower in Production than Dev/test (Vote for this Idea for an easier fix in the future)

[JasonMcD (Jason McDermott)]

PSA for future readers, check off Persist in Query as Jose’s screenshot that I borrowed below shows. Otherwise it goes away after you close the window.

If it is anything like the QueryOldCompanySecurity setting, don’t even reopen the Execution Settings window, or if you do, click cancel. (See linked post below.)

FOR XML PATH versus STRING_AGG

I found a resolution for this issue. You should still move to STRING_AGG as soon as you get on SQL 2017 or above. Edit the query with the issue, go to Actions\Execution Settings. In the dialog that pops up you can add a new setting called QueryOldCompanySecurity with any value (I used “True”). Make sure to set the Persist in Query flag. Hit OK, and save the query. I had a tiny bit of trouble with this UI, just don’t go back into the Query Execution Settings UI and click OK (you can click CANCEL…