我们有一个 .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
但这没有帮助。索引调优向导也没有帮助,因为它不允许我选择系统表。此服务器上没有其他任何运行,因此没有其他任何东西可以减慢它的速度。我还能做什么来诊断或修复该服务器上的问题?
答案1
如果情况允许,左连接会生成大量记录集,这是出了名的。我偶尔会遇到这种问题,但我没有快速简便的答案。我发现,反复试验查询(例如,通过一次构建一个连接来查询)是找出哪个连接导致问题的最佳方法。
您是否尝试过从查询中的表中执行 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 表应该有匹配的记录。以这种方式执行查询是否会缩短执行时间?
答案2
您是否检查过自己是否被屏蔽了?
既然您已经深入了解了执行计划,我猜您已经了解了,但总是值得一问。根据您运行的隔离级别以及系统上运行的其他活动,您可能正在等待锁定。
表格是否可能高度分散?
您可以检查 SQL 2000(而不是 SQL 2005 或更高版本)上的系统表碎片。请参阅 Kalen Delaney 的博客条目有关系统表碎片的更多信息,请参阅此处。(但是,如果表不是很大,那么高碎片级别可能会产生误导。我通常使用 1K 页作为经验法则。)
SQL 实例是否存在较大的性能问题?
我真的不确定问题是否出在这里的计划上——从链接来看,您的计划似乎包含一些强大的连接,但如果实际上返回的行很少,那么我认为这些连接不会使性能变差,除非您有其他问题。我见过一些计划,它们高估了行数,但表现很好,低估的行数通常会让我崩溃。(如果其他人知道这可能是不正确的情况,我很乐意听听他们!)
此时,我个人会快速检查一下机器上的 SQL 环境,以确保一切如我所料。我经常发现这些问题“为什么这个查询很慢”会导致发现存在更大的性能问题。
- sp_configure 选项,例如 maxdop、内存最小/最大、锁设置
- 检查 SQL 错误日志中是否有关于磁盘延迟的警告,确保启动时它能够使用大页面(如果我期望它使用大页面的话),等等
- 定期安排的 SQL 作业是否在正常时间范围内完成?
- 检查您收集的任何性能计数器,看它们是否在正常范围内。
答案3
只是一个想法,SQL 2000 中的优化器引擎曾经很难为具有超过 4 个内连接的语句找到最优化的查询计划,并且我认为对于左连接来说甚至更难。
正如您在提供的 quereplan 中看到的,它执行了多对多查询。
如果您需要来自 4 个以上表的数据,SQL 2000 的最佳做法是分解代码。
/哈坎·温瑟
答案4
这可能与你遇到的问题有关