Statistics on system tables and query performance

Recently I was helping a customer design a database with a very large number of database objects – hundreds of schemas, a few thousands of stored procedures, hundreds of partitioned tables, with most tables containing between two hundred and a thousand partitions. Once all these objects were created, I had to write a catalog metadata…

2

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