2

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)

11
  • It returns immediately for me.
    – Hannah Vernon
    Feb 15, 2017 at 16:05
  • 6 seconds for 30k rows on 2016 RTM. But I have seen metadata queries have odd performance issues. Would you mind posting statistics io output, and the total number of rows returned?
    – T.H.
    Feb 15, 2017 at 17:03
  • @MaxVernon, what version of SQL Server, exactly? I'm having the problem on 2016 SP1 CU1
    – Yishai
    Feb 15, 2017 at 17:27
  • @JoeObbish, definitely no blocking.
    – Yishai
    Feb 15, 2017 at 17:27
  • @JoeObbish, CU1?
    – Yishai
    Feb 15, 2017 at 18:01

1 Answer 1

3

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.

1
  • This is the second time I have seen the new carnality estimator mess up in CU1. I'm suspecting there is a problem there, and would not use CU1 if I could avoid it from now on (unfortunately it doesn't have an uninstall, despite what the documentation says). The rebuilding of the indexes didn't help. Forcing the legacy cardinality estimation is fine for this use case. I can turn it on, run the query, and turn it off, as this is only for development purposes. If I need to I'll get a plan guide going. Thank you very much for the help!
    – Yishai
    Feb 16, 2017 at 14:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.