主页 / server / 问题 / 30349
Accepted
user10444
Asked: 2009-06-24 06:45:02 +0800 CST

缓慢的 INFORMATION_SCHEMA 查询

  • 772

我们有一个 .NET Windows 应用程序，它在登录时运行以下查询以获取有关数据库的一些信息：

SELECT t.TABLE_NAME, ISNULL(pk_ccu.COLUMN_NAME,'') PK, ISNULL(fk_ccu.COLUMN_NAME,'') FK 
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk_tc 
ON pk_tc.TABLE_NAME = t.TABLE_NAME 
AND pk_tc.CONSTRAINT_TYPE = 'PRIMARY KEY' 
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE pk_ccu 
ON pk_ccu.CONSTRAINT_NAME = pk_tc.CONSTRAINT_NAME 
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk_tc 
ON fk_tc.TABLE_NAME = t.TABLE_NAME 
AND fk_tc.CONSTRAINT_TYPE = 'FOREIGN KEY' 
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE fk_ccu 
ON fk_ccu.CONSTRAINT_NAME = fk_tc.CONSTRAINT_NAME

通常这会在几秒钟内运行，但在一台运行 SQL Server 2000 的服务器上，运行需要四分钟以上。我在启用执行计划的情况下运行它，结果非常好，但这部分引起了我的注意（它不会让我发布图像）：

http://img35.imageshack.us/i/plank.png/

然后我更新了执行计划中提到的所有表的统计信息：

update statistics sysobjects
update statistics syscolumns
update statistics systypes
update statistics master..spt_values
update statistics sysreferences

但这没有帮助。索引调整向导也无济于事，因为它不允许我选择系统表。这台服务器上没有其他东西在运行，所以没有其他东西可以减慢它的速度。我还能做些什么来诊断或修复该服务器上的问题？

sql-server performance query

4 个回答

  1. Best Answer

    如果情况允许，左连接因生成大量记录集而臭名昭著。我偶尔会遇到这类问题，但我没有快速简单的答案。我发现玩弄查询，例如通过一次建立一个连接的查询，是找出哪个连接导致问题的最佳方法。

    您是否尝试过从查询中的表中执行 select count(*) 以查看其中一个表的行数是否异常高？

    JR

    更多想法：目前尚不清楚问题是否出在服务器上，或者查询是否是病态的并且会杀死任何服务器。我想您可以将数据库复制到另一台服务器并再次尝试查询。

    如何将查询修改为：

    SELECT t.TABLE_NAME, ISNULL(pk_ccu.COLUMN_NAME,'') PK, ISNULL(fk_ccu.COLUMN_NAME,'') FK 
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN 
( INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk_tc INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE pk_ccu
  ON pk_ccu.CONSTRAINT_NAME = pk_tc.CONSTRAINT_NAME 
)
 ON pk_tc.TABLE_NAME = t.TABLE_NAME AND pk_tc.CONSTRAINT_TYPE = 'PRIMARY KEY' 
LEFT JOIN
( INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk_tc INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE fk_ccu
  ON fk_ccu.CONSTRAINT_NAME = fk_tc.CONSTRAINT_NAME
)
 ON fk_tc.TABLE_NAME = t.TABLE_NAME AND fk_tc.CONSTRAINT_TYPE = 'FOREIGN KEY'

    我认为内部连接应该是安全的，因为 tc 和 ccu 表应该有匹配的记录。以这种方式进行查询是否会提高执行时间？

    • 1

  2. 您是否检查过您是否被阻止？

    既然你已经深入了解了执行计划，我猜你有，但这总是值得一问的。根据您正在运行的隔离级别以及系统上正在运行的其他活动，您可能正在等待锁定。

    表会高度分散吗？

    您可以在 SQL 2000（不是 SQL 2005 或更高版本）上检查系统表的碎片。有关详细信息，请参阅 Kalen Delaney关于系统表碎片的博客条目。（但是，如果表不是很大，那么高碎片级别可能会产生误导。我通常使用 1K 页作为经验法则。）

    SQL 实例是否存在较大的性能问题？

    我真的不确定问题是否是这里的计划 - 从链接看起来你有一个包含一些强大连接的计划，但如果实际上返回的行很少，那么我认为这些连接不会使性能变得糟糕除非你有其他问题。我见过一些计划高估了很多行数并且执行得很好，这是被低估的行数通常会害死我。（如果其他人知道这可能不正确的情况，我很想听听他们的消息！）

    在这一点上，我个人会快速检查一下盒子上的 sql 环境，以确保事情看起来像我预期的那样。我经常发现“为什么这个查询很慢”这些问题导致发现存在更大的性能问题。

    1. sp_configure 选项，例如 maxdop、内存最小/最大、锁定设置
    2. 检查 SQL 错误日志中有关磁盘延迟的任何警告，确保它在启动时能够使用大页面，如果我希望它使用大页面等
    3. 定期安排的 sql 作业是否在正常时间范围内完成？
    4. 检查您收集的任何性能计数器，看看它们是否在正常范围内。
    • 0

  3. 只是一个想法，SQL 2000 中的优化器引擎过去很难为具有 4 个以上内连接的语句找到最优化的查询计划，我认为左连接更难。

    正如您在提供的 quereplan 中所见，它执行了多对多查询。

    如果您需要来自超过 4 个表的数据，SQL 2000 的最佳实践是分解代码。

    /哈坎·温瑟

    • 0

  4. 这可能与您遇到的问题有关

    http://bugs.mysql.com/bug.php?id=19588

    • 0

相关问题