对特定数据库的慢查询 [英] slow queries for a particular database
问题描述
在这里跟我说伙计，我不太了解MS SQL的表现。
想知道是否有人可以指出我正确的方向。我有1
特定数据库，查询速度很慢。为了测试这个理论，我在一些有很多行的表上运行SELECT COUNT（*）
（2-4百万行）。示例：一个大约350万行的表，
计数需要10分钟。最初运行。如果我只计算索引字段
SELECT COUNT（ID），则大约需要2分钟。我有
其他类似的数据库，相同的表，数据一样多，
相同的指标/约束等等，它更快（1到2）
秒运行）。我已经将缓慢查询的数据库移动到另一台服务器，
并且它仍然很慢（单处理器服务器）。其他数据库在
服务器就好了。
任何帮助/提示都表示赞赏。
谢谢，
Bear with me here folks, I don''t know much MS SQL performance.
Wondering if someone can point me in the right direction. I have 1
particular database where queries are slow. To test this theory, I am
running a SELECT COUNT(*) on some of the tables that have many rows
(2-4 million rows). Example: A table with approx 3.5 million rows, a
count takes over 10 mins. to run initially. If I just do a count on
the indexed field, SELECT COUNT(ID), it takes about 2 minutes. I have
other similiar databases, same tables, with just as much data, with
the same indicies/constraints, etc., and it is much quicker (1 to 2
secs. to run). I''ve moved the db with slow queries to another server,
and it is still slow there (single processor server). Other db''s on
that server are just fine.
Any help/hints are appreciated.
Thanks,
推荐答案
过时的统计信息可能会导致此类性能问题。更新统计数据
并查看是否有帮助：
http://msdn.microsoft.com/en-us/library/ms187348.aspx
http://msdn.microsoft.com/en-us/library/ms173804.aspx
http：//www.sql-server-performance.co...istics_p1 .aspx
HTH，
Plamen Ratchev
http://www.SQLStudio.com
Outdated statistics can cause such performance issues. Update the statistics
and see if that helps:
http://msdn.microsoft.com/en-us/library/ms187348.aspx
http://msdn.microsoft.com/en-us/library/ms173804.aspx
http://www.sql-server-performance.co...istics_p1.aspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com
6月30日， 4：37 * pm，Plamen Ratchev < Pla ... @ SQLStudio.comwrote：
On Jun 30, 4:37*pm, "Plamen Ratchev" <Pla...@SQLStudio.comwrote:
过时的统计信息可能会导致此类性能问题。更新统计数据
并查看是否有帮助： http://msdn.microsoft.com/en -us / libr ... istics_p1.aspx
HTH，
Plamen Ratchevhttp：//www.SQLStudio.com
Outdated statistics can cause such performance issues. Update the statistics
and see if that helps:http://msdn.microsoft.com/en-us/libr...istics_p1.aspx
HTH,
Plamen Ratchevhttp://www.SQLStudio.com
有一个维护计划来更新这个
db的统计数据。我删除了，重置了它，并手动执行。已完成
成功约。 7分钟，但计数仍然花了很长时间
。
There was a maintenance plan to update the statistics already for this
db. I deleted, and reset it up, and executed manually. Completed
successfully in approx. 7 mins., but a count is still taking an
extremely long time.
你能在一张桌子上尝试SELECT COUNT（*）有一个狭窄的非聚集索引
，看看完成需要多长时间？如果
表中没有非聚集索引，则必须扫描所有数据页以获取计数。
HTH，
Plamen Ratchev
http://www.SQLStudio.com
Can you try SELECT COUNT(*) on a table that has a narrow non-clustered index
and see how long it takes to complete? If there is no non-clustered index on
the table all data pages have to be scanned to get the count.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
这篇关于对特定数据库的慢查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
