Azure SQL Database Managed Instance. Current Compatibility Level is set to 140 to match on-prem SQL Server 2017 test and development server. I just read this article on Scalar UDF Inlining and was curious if setting Query Optimizer Fixes to ON would enable this feature? I have read several more articles on the topic but none of them address this question.
Does setting Query Optimizer Fixes to ON enable Scalar UDF Inlining on a database with compatibility level 140
azure-sql-managed-instancefunctions
Related Solutions
The situation that you described can happen when there's some kind of monitoring enabled (trace, extended event session, some third party tool), which does some kind of logging or work per UDF execution (or even per statement inside the UDF).
If the UDF is executed many times in a query there can be a very large amount of overhead to do that monitoring. If the monitoring is only occurring on one server then you would see a large performance difference between them.
You can replace your TVF with a view (or keep the TVF, but use the view for your performance-critical sproc):
CREATE VIEW CAOT_AllApplicationStatuses AS
SELECT
fkApplication,
ev.pkApplicationEvent AS pkApplicationEvent,
ev.CreateDate AS EventCreatedDate,
CONVERT(VARCHAR(12), evt.fkApplicationStatus) As 'KEY',
evt.EventDescription AS 'DESCRIPTION',
evt.EventCode AS 'CODE',
CONVERT(VARCHAR(20), DATEDIFF(d, ev.createdate, GETDATE())) AS 'DAYS',
apps.StatusDescription AS 'STATUS',
apps.StatusCode AS 'STATUSCODE'
FROM
dbo.CAOT_ApplicationEvent (NOLOCK) ev
INNER JOIN dbo.CAOT_EventType (NOLOCK) evt ON ev.fkEventType = evt.pkEventType
INNER JOIN dbo.CAOT_ApplicationStatus (NOLOCK) apps ON evt.fkApplicationStatus = apps.pkApplicationStatus
WHERE
NOT EXISTS
(
SELECT * FROM dbo.CAOT_ApplicationEvent AS LaterEvent WHERE EV.pkApplication = LaterEvent.pkApplication AND LaterEvent.pkApplication.CreateDate > EV.CreateDate
)
ORDER BY
ev.CreateDate DESC, ev.pkApplicationEvent DESC
This is simply the content of the TVF's main SELECT
query, with the WHERE
clause from the second SELECT
incorporated as a NOT EXISTS
. I'm trusting that all records in CAOT_ApplicationEvent
have records in CAOT_EventType
and CAOT_ApplicationStatus
; if that's not the case, you'll need to add those joins in the NOT EXISTS
query.
Just using a view rather than a TVF may help, as the parser will incorporate the view into the final query, and discard unused parts; those calls to CONVERT()
, for example, are likely to be relatively expensive, but they appear to be unused. However, the complex predicates in your top-level sproc may necessitate a table scan. Let's give this a shot and see if it needs more work!
Best Answer
No, 'query optimizer fixes' only affects optimizer bug fixes that may result in unexpected query plan changes. It does not enable new features.
The only way to enable scalar function inlining is to set the database compatibility level to 150: