2

Updating my Entity Framework edmx file takes about 10 seconds. However recently it's been taking 3 minutes. sqlsevr.exe is responsible, thrashing the CPU during that time. Running sp_whoisactive shows the single query responsible, a query from Entity Framework to get the database schema.

My computer is otherwise fast (SSD, i5, Windows 7), with few processes running. Nothing else was using the DB then. Running Performance Monitor during that time shows something interesting: Performance Monitor There are 2 cores being used during the edmx update, and when one goes up the other goes down.

I was using SQL Server 2014 Developer. I uninstalled it and installed Sql Server 2014 Express instead. This fixed it for a few days, but the problem came back.

There are other queries that usually are very fast, that now sometimes time out. Most of the time though they run as fast as always.

I've tried rebuilding indexes, looking in eventvwr, and restarting my computer.

The query from EF is:

exec sp_executesql N'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]
WHERE  NOT ((([Extent1].[CatalogName] LIKE @p0) AND ([Extent1].[SchemaName] LIKE @p1) AND ([Extent1].[Name] LIKE @p2)) OR (([Extent1].[CatalogName] LIKE @p3) AND ([Extent1].[SchemaName] LIKE @p4) AND ([Extent1].[Name] LIKE @p5)))',N'@p0 nvarchar(4000),@p1 nvarchar(4000),@p2 nvarchar(4000),@p3 nvarchar(4000),@p4 nvarchar(4000),@p5 nvarchar(4000)',@p0=N'Lease',@p1=N'dbo',@p2=N'Lookups',@p3=N'Lease',@p4=N'dbo',@p5=N'LookupTypes'
Improve this question
11
  • Did you check for blocking?
    – Aaron Bertrand
    Commented Jun 3, 2015 at 20:27
  • @AaronBertrand Yes, by running sp_who2.
    – wezten
    Commented Jun 3, 2015 at 20:38
  • 1
    That doesn't mean there isn't blocking. When the query is running slow, check sys.dm_exec_requests -> wait_type and blocking_session_id. Retrieval of metadata is not really anything you can do anything about - it's usually fast unless there is some sort of Sch-* locking.
    – Aaron Bertrand
    Commented Jun 3, 2015 at 20:38
  • 1
    is optimize for adhoc enabled (set to 1) ?
    – Kin Shah
    Commented Jun 3, 2015 at 20:53
  • 1
    @wezten Why did you restart sql server ? that will trash all your plans + the setting is dynamic. What is the max memory , maxdop setting on the server ? Have you enabled TF 4199 ?
    – Kin Shah
    Commented Jun 3, 2015 at 21:17

1 Answer 1

Reset to default
0

The following helped quite a bit, but not totally. Control Panel -> System -> Advanced sytem settings -> Advanced -> Performance Settings -> Advanced -> Adjust for best performance of Background services.

Performance Settings

Improve this answer

Your Answer

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

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