程序员经验分享-hwhale

系统表怪异

2024-04-10

我有一个 15Gb 数据库和 Sql Server 2014。在 Visual Studio 中我尝试“从数据库更新模型”时，它挂起。 Sql Profiler 显示 VS 启动的 sql，而且速度非常慢。下面是sql和执行计划的部分。在执行计划中，罪魁祸首似乎是 sys.sysschobjs 表，它在许多地方占用了 6%（如图所示）。有趣的是 sql 仅使用这些表

INFORMATION_SCHEMA.TABLES
INFORMATION_SCHEMA.COLUMNS
INFORMATION_SCHEMA.VIEWS
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
INFORMATION_SCHEMA.KEY_COLUMN_USAGE

所有这些都相当小（最大的有 1798 条记录），但查询本身需要 51 分钟。 任何关于如何解决这个问题的想法将不胜感激。

EDIT我怀疑 sys.sysschobjs 索引需要重建。考虑到它是系统表，我应该这样做吗？

摘录自sql：

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 (REALLY LONG PARAMETER LIST INVOLVING MOST TABLE NAMES)

None

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

sqlserver

系统表怪异 的相关文章

  • SQL Server、ISABOUT、加权项

    我试图弄清楚加权项在 SQL SERVER 的 ISABOUT 查询中是如何工作的 这是我目前所在的位置 每个查询返回以下行 查询 1 权重 1 初始排名 SELECT FROM CONTAINSTABLE documentParts ti

  • 导致聚集索引扫描的日期参数

    我有以下查询 DECLARE StartDate DATE 2017 09 22 DECLARE EndDate DATE 2017 09 23 SELECT a col1 a col2 b col1 b col2 b col3 a col

  • 当列的数据类型为 int 时，如何用字符串替换 null

    我有一个包含 3 列的表和如下示例数据 所有列都是数据类型int 我有这个查询 select foodid dayid from Schedule 我要更换dayid用字符串 ifdayid null 为此我尝试了这个查询 select f

  • PDO dblib 未捕获警告

    我已经使用 realestateconz mssql bundle 和免费 TDS 成功使我的 symfony 应用程序连接到 MSSQL 数据库 我的问题是 当我尝试执行存储过程时 如果出现问题 该过程会引发异常 但 PDO 不会报告任何

  • Powershell SQL Server数据库连接和连接超时问题

    我有一个连接到 SQL Server 2012 数据库的 powershell 脚本 该脚本运行 SQL 查询并将结果集放入数据表中 以将格式化的电子邮件发送给相关方 下面是问题所在的代码片段 CBA New Object System D

  • 在 SQL Server 数据库之间传递用户定义的表类型

    我在 SQL Server 的一个数据库中有一个用户定义的表类型 我们称之为DB1 我的类型的定义非常简单 仅包含 2 列 创建我的类型的脚本如下 CREATE TYPE dbo CustomList AS TABLE ID int Dis

  • 插入并发问题-多线程环境

    我有一个问题 即使用完全相同的参数在完全相同的时间调用相同的存储过程 存储过程的目的是获取记录 如果存在 或创建并获取记录 如果不存在 问题是两个线程都在检查记录是否存在并报告错误 然后都插入新记录 在数据库中创建重复记录 我尝试将操作保留

  • 将数据从 MS SQL 导入 MySQL

    我想从 MS SQL Server 导入数据 通过某种正则表达式运行它以过滤掉内容 然后将其导入 MySQL 然后 对于每个查询 我希望显示来自第三个数据库的相关图像 明智地导入和链接 最简单的方法是什么 谢谢 澄清 它是一个 PHP 应用

  • 无法更新 .mdf 数据库，因为该数据库是只读的（Windows 应用程序）

    我使用 C 创建了一个数据库 Windows 应用程序 我的应用程序在 Windows XP 上成功运行 但在 Vista 或 Windows 7 系统上无法正确执行 我的应用程序显示类似以下内容的消息 无法更新 mdf 数据库 因为该数据

  • SQL中如何合并多个表的数据

    我想我的处境很复杂 这是场景 我在 SQL Server 中有 3 个表 注册 学生 课程 仅供参考 没有外键 表的列是 Student 学生号 学生名 Course 课程 ID 课程名称 注册 注册 ID 学生 ID 课程 ID 课程结果

  • 有很多数据库视图可以吗？

    我很少 每月 每季度 使用 Microsoft SQL Server 2005 数据库视图生成数百份 Crystal Reports 报告 在我不读取这些视图的所有时间里 这些视图是否会浪费 CPU 周期和 RAM 因为我很少从视图中读取数

  • 使用 SqlDataReader.IsDBNull 时使用列名

    我已经得到了从 SQL DB 读取数据的代码 我不知道应该如何编辑它 以便我可以使用原始列名称而不是列索引 string query SELECT FROM zajezd WHERE event thisrow AND year klien

  • 将 SQL Server 日期时间转换为较短的日期格式

    我有一个datetimeSQL Server 中的列为我提供了这样的数据10 27 2010 12 57 49 pm我想查询此列 但只需让 SQL Server 返回年月日 例如 2010 10 27或类似的东西 我应该研究哪些功能 我应该

  • TSQL如何在xml列中选择具有技能的员工

    在如下所示的表架构中 CREATE TABLE dbo Employee EmployeeId uniqueidentifier NOT NULL Name nvarchar 50 NOT NULL Location nvarchar 50

  • 安装 SQL Server 2012 错误“系统无法打开指定的设备或文件。”

    我正在尝试在已安装 SQL Server 2008 R2 Express 和 Windows 7 的计算机上安装 SQL Server 2012 Developer 我使用 Microsoft Secure Download Manager

  • asp.net网格分页的SQL查询

    我在用iBatis and SQLServer 使用偏移量和限制进行分页查询的最佳方法是什么 也许我添加该列ROW NUMBER OVER ORDER BY Id AS RowNum 但这只会阻止简单查询的数据访问 在某些情况下 我使用选择

  • 无论如何要解密加密的sql server存储过程吗？

    我有几个 ms sql server 2000 存储过程 很久以前就被前雇员加密了 一切都很好 直到我们需要稍微改变一下 有什么方法可以检索源代码吗 或者重写是唯一的选择 多谢 或者免费 谷歌是你的朋友 http searchsqlserv

  • 从 SQL Server 读取十进制值时出现溢出异常

    我想知道这是一个错误还是我做错了什么 我正在加载值SqlDataReader来自 SQL Server 2008 数据库 但在某些情况下 它无法将 SQL 值转换为 net 值 NET 4 0 我已将其追溯到一个测试用例 它演示了实际问题

  • [SqlException (0x80131904) 的问题：无效的对象名称“dbo.TableName”。]

    我在 google 和 stackoverflow 中查找 但没有找到答案 如何通过 VS 2010 中的连接字符串连接到我的数据库表

  • 在 Sql STUFF 命令中将最后一项的逗号分隔符替换为“and”

    如果我查询的输出是英国 美国 印度 是否可以像英国 美国那样显示查询结果and India 这是我的查询 Select stuff Select Distinct Country as text from tbl Country where

随机推荐

热门标签