Got some Query Store going for that broken Database. Does solve some issues, but I guess SQL 2019 with the AI feature is an even better solution for non-SARG-able queries that tend to fall over. (aka crappy database design)
SELECT * FROM TABLE1, TABLE2
WHERE TABLE1.ID = TABLE2.ID
AND TABLE2.COLUMN22 LIKE '%STRING%'
I don't mind pinning a couple of Execution Plans. But it shouldn't be a full-time job.
Note: TABLE1 50 MIO rows and TABLE2 62 MIO rows
The history table contains a whopping 612 Mio rows.
@PaulWhite So works fine locally on 2012 and 2016. Works fine on shared 2012dev instance. One of the CREATE PROCs times out on 2016dev instance (5000 rows of pain mappings - lots of pain in locations all over the body with and without various angina and pain characteristics...)
These are the waits from one proc: creating the proc that inserts 5208 rows individually into the same table (triggers are back in place since the DB was recreated from scratch), and executing the proc.
To me that tells me out of 9:56 of execution, it wasn't waiting for much at all, right? So not IO issues, not other contention.
It's like the CPUs assigned to this VM are just slow.
That stored procedure doesn't exist anymore and people seem to enjoy tagging both random questions and anything related to sp_blitz with it still.
There are only 9 questions using it (aside from the 3 I de-tagged in the past week or so) so there isn't much to lose by removing it.