Query performance, scalar UDFs, and predicate pushdown

Recently I had to troubleshoot a query that performed much slower than expected. The solution to that seemed sufficiently interesting to warrant a write-up, and the root cause of the problem reinforced a well-known best practice of avoiding scalar user-defined functions in set-based queries. The original query looked like this:SELECT  ‘Literal1’,         u.Col2FROM dbo.Table1 AS uINNER JOIN dbo.Table2 AS l ON u.Col2 = l.Col2                WHERE   l.Col3 = ‘Literal2’        AND        NOT EXISTS  (                    SELECT  1                     FROM dbo.Table2 AS l2                     WHERE   u.Col2 = l2.Col2                            AND                            Col3 = ‘Literal1’                    )        AND        dbo.UDF1(u.Col1) IS NOT NULL; Table1 had about 3 million…

11