Just seeking an expert/practical advise from DBA point of view where one of our application DB running on SQL 2014 after migration had old DB compatibility level i.e 100.(SQL2008)
From DEV point of view all the testing has been done and they dont see much diff and want to move to prod based on their testing.
In our testing ,For certain process where we see slowness like in SP's we found the part of statement that was slow and added query traceon hint , something like below keeping compat to 120, which helps keeping performance stable
SELECT [AddressID],
[AddressLine1],
[AddressLine2]
FROM Person.[Address]
WHERE [StateProvinceID] = 9 AND
[City] = 'Burbank'
OPTION (QUERYTRACEON 9481);
GO
Now my question is when we change compat to 120 in prod, i am expecting there might be more stored procedures which are expected to have performance impact. Now for the ones which do --
My concern is , how can i use that hint at SP level? Because troubleshooting in prod to find issue at statement level will take much time and considering this as prod, impact might increase.
Is there a better way as this being SQL2014SP3 that i might not be aware of in case we want to adjust the settings for certain SP's showing performance degradation without changing compat for entire database back to from where we came?