Workaround for very long running query on the schema

Question

Visual Studio Lightswitch 2015 presents this very long running query to the database to get the data structure. I reported this to Microsoft but I'm wondering if the community here has some ideas. The query itself cannot be changed, but perhaps there is some server settable option that might change things. This query runs over 10 minutes in SQL Server 2016 SP1 CU1. In earlier versions of SQL Server (for sure SQL Server 2014, but I'm pretty sure that it was the case when I initially ran SQL Server 2016 RTM) it ran under a minute. I have tested this on two different machines, so it isn't about a machine-specific problem.

This query is slow in the Management Studio, so it isn't a Lightswitch specific problem (other than Lightswitch might have generated a better query):

SELECT 
[UnionAll1].[Ordinal] AS [C1], 
[Extent1].[CatalogName] AS [CatalogName], 
[Extent1].[SchemaName] AS [SchemaName], 
[Extent1].[Name] AS [Name], 
[UnionAll1].[Name] AS [C2], 
[UnionAll1].[IsNullable] AS [C3], 
[UnionAll1].[TypeName] AS [C4], 
[UnionAll1].[MaxLength] AS [C5], 
[UnionAll1].[Precision] AS [C6], 
[UnionAll1].[DateTimePrecision] AS [C7], 
[UnionAll1].[Scale] AS [C8], 
[UnionAll1].[IsIdentity] AS [C9], 
[UnionAll1].[IsStoreGenerated] AS [C10], 
CASE WHEN ([Project5].[C2] IS NULL) THEN cast(0 as bit) ELSE [Project5].[C2] END AS [C11]
FROM   (
        SELECT
        quotename(TABLE_SCHEMA) + quotename(TABLE_NAME) [Id]
        ,   TABLE_CATALOG [CatalogName]
        ,   TABLE_SCHEMA [SchemaName]
        ,   TABLE_NAME    [Name]
        FROM
        INFORMATION_SCHEMA.TABLES
        WHERE
        TABLE_TYPE = 'BASE TABLE'
      ) AS [Extent1]
INNER JOIN  (SELECT 
    [Extent2].[Id] AS [Id], 
    [Extent2].[Name] AS [Name], 
    [Extent2].[Ordinal] AS [Ordinal], 
    [Extent2].[IsNullable] AS [IsNullable], 
    [Extent2].[TypeName] AS [TypeName], 
    [Extent2].[MaxLength] AS [MaxLength], 
    [Extent2].[Precision] AS [Precision], 
    [Extent2].[DateTimePrecision] AS [DateTimePrecision], 
    [Extent2].[Scale] AS [Scale], 
    [Extent2].[IsIdentity] AS [IsIdentity], 
    [Extent2].[IsStoreGenerated] AS [IsStoreGenerated], 
    0 AS [C1], 
    [Extent2].[ParentId] AS [ParentId]
    FROM (
          SELECT
          quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) [Id]
          ,   quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME)                             [ParentId]
          ,   c.COLUMN_NAME   [Name]
          ,   c.ORDINAL_POSITION [Ordinal]
          ,   CAST( CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsNullable]
          ,   CASE
          WHEN c.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
          c.CHARACTER_MAXIMUM_LENGTH = -1 THEN
          c.DATA_TYPE + '(max)'
          ELSE
          c.DATA_TYPE
          END
          as [TypeName]
          ,   c.CHARACTER_MAXIMUM_LENGTH [MaxLength]
          ,   CAST(c.NUMERIC_PRECISION as integer) [Precision]
          ,   CAST(c.DATETIME_PRECISION as integer)[DateTimePrecision]
          ,   CAST(c.NUMERIC_SCALE as integer) [Scale]
          ,   c.COLLATION_CATALOG [CollationCatalog]
          ,   c.COLLATION_SCHEMA [CollationSchema]
          ,   c.COLLATION_NAME [CollationName]
          ,   c.CHARACTER_SET_CATALOG [CharacterSetCatalog]
          ,   c.CHARACTER_SET_SCHEMA [CharacterSetSchema]
          ,   c.CHARACTER_SET_NAME [CharacterSetName]
          ,   CAST(0 as bit) as [IsMultiSet]
          ,   CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity' ) as bit) as [IsIdentity]
          ,   CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed' ) | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as bit) as [IsStoreGenerated]
          , c.COLUMN_DEFAULT as [Default]
          FROM
          INFORMATION_SCHEMA.COLUMNS c
          INNER JOIN
          INFORMATION_SCHEMA.TABLES t ON
          c.TABLE_CATALOG = t.TABLE_CATALOG AND
          c.TABLE_SCHEMA = t.TABLE_SCHEMA   AND
          c.TABLE_NAME = t.TABLE_NAME       AND
          t.TABLE_TYPE = 'BASE TABLE'
      ) AS [Extent2]
UNION ALL
    SELECT 
    [Extent3].[Id] AS [Id], 
    [Extent3].[Name] AS [Name], 
    [Extent3].[Ordinal] AS [Ordinal], 
    [Extent3].[IsNullable] AS [IsNullable], 
    [Extent3].[TypeName] AS [TypeName], 
    [Extent3].[MaxLength] AS [MaxLength], 
    [Extent3].[Precision] AS [Precision], 
    [Extent3].[DateTimePrecision] AS [DateTimePrecision], 
    [Extent3].[Scale] AS [Scale], 
    [Extent3].[IsIdentity] AS [IsIdentity], 
    [Extent3].[IsStoreGenerated] AS [IsStoreGenerated], 
    6 AS [C1], 
    [Extent3].[ParentId] AS [ParentId]
    FROM (
          SELECT
          quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) [Id]
          ,   quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME)                             [ParentId]
          ,   c.COLUMN_NAME   [Name]
          ,   c.ORDINAL_POSITION [Ordinal]
          ,   CAST( CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsNullable]
          ,   CASE
          WHEN c.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
          c.CHARACTER_MAXIMUM_LENGTH = -1 THEN
          c.DATA_TYPE + '(max)'
          ELSE
          c.DATA_TYPE
          END
          as [TypeName]
          ,   c.CHARACTER_MAXIMUM_LENGTH [MaxLength]
          ,   CAST(c.NUMERIC_PRECISION as integer) [Precision]
          ,   CAST(c.DATETIME_PRECISION as integer) as [DateTimePrecision]
          ,   CAST(c.NUMERIC_SCALE as integer) [Scale]
          ,   c.COLLATION_CATALOG [CollationCatalog]
          ,   c.COLLATION_SCHEMA [CollationSchema]
          ,   c.COLLATION_NAME [CollationName]
          ,   c.CHARACTER_SET_CATALOG [CharacterSetCatalog]
          ,   c.CHARACTER_SET_SCHEMA [CharacterSetSchema]
          ,   c.CHARACTER_SET_NAME [CharacterSetName]
          ,   CAST(0 as bit) as [IsMultiSet]
          ,   CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity' ) as bit) as [IsIdentity]
          ,   CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed' ) | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as bit) as [IsStoreGenerated]
          ,   c.COLUMN_DEFAULT [Default]
          FROM
          INFORMATION_SCHEMA.COLUMNS c
          INNER JOIN
          INFORMATION_SCHEMA.VIEWS v ON
          c.TABLE_CATALOG = v.TABLE_CATALOG AND
          c.TABLE_SCHEMA = v.TABLE_SCHEMA AND
          c.TABLE_NAME = v.TABLE_NAME
          WHERE
          NOT (v.TABLE_SCHEMA = 'dbo'
          AND v.TABLE_NAME in('syssegments', 'sysconstraints')
          AND SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar(20)),1,1) = 8)
      ) AS [Extent3]) AS [UnionAll1] ON (0 = [UnionAll1].[C1]) AND ([Extent1].[Id] = [UnionAll1].[ParentId])
LEFT OUTER JOIN  (SELECT 
    [UnionAll2].[Id] AS [C1], 
    cast(1 as bit) AS [C2]
    FROM  (
        SELECT
        quotename(tc.CONSTRAINT_SCHEMA) + quotename(tc.CONSTRAINT_NAME) [Id]
        , quotename(tc.TABLE_SCHEMA) + quotename(tc.TABLE_NAME) [ParentId]
        ,   tc.CONSTRAINT_NAME [Name]
        ,   tc.CONSTRAINT_TYPE [ConstraintType]
        ,   CAST(CASE tc.IS_DEFERRABLE WHEN 'NO' THEN 0 ELSE 1 END as bit) [IsDeferrable]
        ,   CAST(CASE tc.INITIALLY_DEFERRED WHEN 'NO' THEN 0 ELSE 1 END as bit) [IsInitiallyDeferred]
        FROM
        INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
        WHERE tc.TABLE_NAME IS NOT NULL
      ) AS [Extent4]
    INNER JOIN  (SELECT 
        7 AS [C1], 
        [Extent5].[ConstraintId] AS [ConstraintId], 
        [Extent6].[Id] AS [Id]
        FROM  (
        SELECT
        quotename(CONSTRAINT_SCHEMA) + quotename(CONSTRAINT_NAME) [ConstraintId]
        ,   quotename(TABLE_SCHEMA) + quotename(TABLE_NAME) + quotename(COLUMN_NAME) [ColumnId]
        FROM
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE
      ) AS [Extent5]
        INNER JOIN (
          SELECT
          quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) [Id]
          ,   quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME)                             [ParentId]
          ,   c.COLUMN_NAME   [Name]
          ,   c.ORDINAL_POSITION [Ordinal]
          ,   CAST( CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsNullable]
          ,   CASE
          WHEN c.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
          c.CHARACTER_MAXIMUM_LENGTH = -1 THEN
          c.DATA_TYPE + '(max)'
          ELSE
          c.DATA_TYPE
          END
          as [TypeName]
          ,   c.CHARACTER_MAXIMUM_LENGTH [MaxLength]
          ,   CAST(c.NUMERIC_PRECISION as integer) [Precision]
          ,   CAST(c.DATETIME_PRECISION as integer)[DateTimePrecision]
          ,   CAST(c.NUMERIC_SCALE as integer) [Scale]
          ,   c.COLLATION_CATALOG [CollationCatalog]
          ,   c.COLLATION_SCHEMA [CollationSchema]
          ,   c.COLLATION_NAME [CollationName]
          ,   c.CHARACTER_SET_CATALOG [CharacterSetCatalog]
          ,   c.CHARACTER_SET_SCHEMA [CharacterSetSchema]
          ,   c.CHARACTER_SET_NAME [CharacterSetName]
          ,   CAST(0 as bit) as [IsMultiSet]
          ,   CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity' ) as bit) as [IsIdentity]
          ,   CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed' ) | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as bit) as [IsStoreGenerated]
          , c.COLUMN_DEFAULT as [Default]
          FROM
          INFORMATION_SCHEMA.COLUMNS c
          INNER JOIN
          INFORMATION_SCHEMA.TABLES t ON
          c.TABLE_CATALOG = t.TABLE_CATALOG AND
          c.TABLE_SCHEMA = t.TABLE_SCHEMA   AND
          c.TABLE_NAME = t.TABLE_NAME       AND
          t.TABLE_TYPE = 'BASE TABLE'
      ) AS [Extent6] ON [Extent6].[Id] = [Extent5].[ColumnId]
    UNION ALL
        SELECT 
        11 AS [C1], 
        [Extent7].[ConstraintId] AS [ConstraintId], 
        [Extent8].[Id] AS [Id]
        FROM  (
        SELECT
        CAST(NULL as nvarchar(1))     [ConstraintId]
        , CAST(NULL as nvarchar(max)) [ColumnId]  
        WHERE 1=2
      ) AS [Extent7]
        INNER JOIN (
          SELECT
          quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) [Id]
          ,   quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME)                             [ParentId]
          ,   c.COLUMN_NAME   [Name]
          ,   c.ORDINAL_POSITION [Ordinal]
          ,   CAST( CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsNullable]
          ,   CASE
          WHEN c.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
          c.CHARACTER_MAXIMUM_LENGTH = -1 THEN
          c.DATA_TYPE + '(max)'
          ELSE
          c.DATA_TYPE
          END
          as [TypeName]
          ,   c.CHARACTER_MAXIMUM_LENGTH [MaxLength]
          ,   CAST(c.NUMERIC_PRECISION as integer) [Precision]
          ,   CAST(c.DATETIME_PRECISION as integer) as [DateTimePrecision]
          ,   CAST(c.NUMERIC_SCALE as integer) [Scale]
          ,   c.COLLATION_CATALOG [CollationCatalog]
          ,   c.COLLATION_SCHEMA [CollationSchema]
          ,   c.COLLATION_NAME [CollationName]
          ,   c.CHARACTER_SET_CATALOG [CharacterSetCatalog]
          ,   c.CHARACTER_SET_SCHEMA [CharacterSetSchema]
          ,   c.CHARACTER_SET_NAME [CharacterSetName]
          ,   CAST(0 as bit) as [IsMultiSet]
          ,   CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity' ) as bit) as [IsIdentity]
          ,   CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed' ) | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as bit) as [IsStoreGenerated]
          ,   c.COLUMN_DEFAULT [Default]
          FROM
          INFORMATION_SCHEMA.COLUMNS c
          INNER JOIN
          INFORMATION_SCHEMA.VIEWS v ON
          c.TABLE_CATALOG = v.TABLE_CATALOG AND
          c.TABLE_SCHEMA = v.TABLE_SCHEMA AND
          c.TABLE_NAME = v.TABLE_NAME
          WHERE
          NOT (v.TABLE_SCHEMA = 'dbo'
          AND v.TABLE_NAME in('syssegments', 'sysconstraints')
          AND SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar(20)),1,1) = 8)
      ) AS [Extent8] ON [Extent8].[Id] = [Extent7].[ColumnId]) AS [UnionAll2] ON (7 = [UnionAll2].[C1]) AND ([Extent4].[Id] = [UnionAll2].[ConstraintId])
    WHERE [Extent4].[ConstraintType] = N'PRIMARY KEY' ) AS [Project5] ON [UnionAll1].[Id] = [Project5].[C1]

The query ran in 11 minutes 28 seconds, return 853 rows. This is the statistics IO output:

(853 row(s) affected)
Table 'syssingleobjrefs'. Scan count 576859, logical reads 1488094, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysidxstats'. Scan count 77, logical reads 168, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 2826, logical reads 14215384, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysobjvalues'. Scan count 1075, logical reads 3225, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 14391892, logical reads 34654161, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysclsobjs'. Scan count 0, logical reads 78399382, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysiscols'. Scan count 90418, logical reads 231163, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysscalartypes'. Scan count 0, logical reads 1706, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Exact DB version:

Microsoft SQL Server 2016 (SP1-CU1) (KB3208177) - 13.0.4411.0 (X64) Jan 6 2017 14:24:37 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 6.3 (Build 14393: ) (Hypervisor)

Equally slow on:

Microsoft SQL Server 2016 (SP1-CU1) (KB3208177) - 13.0.4411.0 (X64) Jan 6 2017 14:24:37 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 6.3 (Build 14393: ) (Hypervisor)

Answer 1

It might help to update statistics on the system tables that are used by the query. Based on your STATISTICS IO output you would run the following code:

UPDATE STATISTICS sys.syssingleobjrefs WITH FULLSCAN;
UPDATE STATISTICS sys.sysidxstats WITH FULLSCAN;
UPDATE STATISTICS sys.sysschobjs WITH FULLSCAN;
UPDATE STATISTICS sys.sysobjvalues WITH FULLSCAN;
UPDATE STATISTICS sys.syscolpars WITH FULLSCAN;
UPDATE STATISTICS sys.sysclsobjs WITH FULLSCAN;
UPDATE STATISTICS sys.sysiscols WITH FULLSCAN;
UPDATE STATISTICS sys.sysscalartypes WITH FULLSCAN;

"Statistics on system tables and query performance" contains a reference to this technique.

Depending on your database updating statistics may not be sufficient. I created a test database with 10000 simple tables with one column each:

DECLARE 
@j INT = 0,
@create_table VARCHAR(1000);
BEGIN
    WHILE @j < 10000
    BEGIN
        SET @create_table = 'CREATE TABLE X_TABLE_' + CAST(@j AS VARCHAR) + ' (ID INT NULL)';

        EXEC (@create_table);

        SET @j = @j + 1;
    END;
END;

The query that you have should return 10k rows but it still hadn't finished after one minute. It had only returned 651 rows so it was on track to finish in 15 minutes.

One change that made a big difference on my database was enabling the legacy cardinality estimator. The query returned all 10000 rows in one second after adding the following hint:

OPTION (RECOMPILE, USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION') );

As you said you cannot modify the code so that isn't immediately helpful. However, you can enable trace flag 9481 at the session level to get the legacy CE. It's possible to enable trace flag 9481 for a particular user as described in "Wanting your non-sysadmin users to enable certain trace flags without changing your app?". However, I do not know what other effects this would have on your application.

If it turns out that the legacy CE also improves performance in your database a less disruptive option may be to create a plan guide for this query. A plan guide will force a query to have a particular execution plan. You could capture a good enough execution play using the legacy CE and force future query executions to use that same plan even without TF 9481 turned on. You should be aware that many people in the industry do not speak favorably of plan guides.