SQL性能慢

Question

我有一个以下的查询;

 SELECT COUNT(Id) FROM Table

该表包含3300万条记录 - 它在Id上有一个主键，没有其他索引。

查询需要30秒钟。

实际执行计划显示它使用了聚集索引扫描。

我们已经分析了该表，并发现它不是通过此链接中显示的第一个查询片段进行碎片化：http://sqlserverpedia.com/wiki/Index_Maintenance。

有什么想法，为什么这个查询如此缓慢以及如何修复它。

表定义：

 CREATE TABLE [dbo].[DbConversation](
[ConversationID] [int] IDENTITY(1,1) NOT NULL,
[ConversationGroupID] [int] NOT NULL,
[InsideIP] [uniqueidentifier] NOT NULL,
[OutsideIP] [uniqueidentifier] NOT NULL,
[ServerPort] [int] NOT NULL,
[BytesOutbound] [bigint] NOT NULL,
[BytesInbound] [bigint] NOT NULL,
[ServerOutside] [bit] NOT NULL,
[LastFlowTime] [datetime] NOT NULL,
[LastClientPort] [int] NOT NULL,
[Protocol] [tinyint] NOT NULL,
[TypeOfService] [tinyint] NOT NULL,
  CONSTRAINT [PK_Conversation_1] PRIMARY KEY CLUSTERED 
 (
[ConversationID] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 GO

我注意到一个问题，即数据库设置为每次增长1MB。这是一个实时系统，所以我们受限于我们可以尝试的内容-有什么想法吗？

更新：

好的，我们通过在适当的列上添加新的非聚集索引来改善了感兴趣的查询的性能，因此这不再是一个关键问题。 SELECT COUNT 仍然很慢-尝试使用NOLOCK提示-没有区别。

我们都认为这可能与自动增长设置为1MB而不是更大的数字有关，但很惊讶它会产生这种影响。磁盘上的MDF碎片可能是可能的原因吗？

- BonyT

---

问题1：你真的需要精确计数吗？还是只需要估算？ - ypercubeᵀᴹ

这只是我们在观察其他东西的缓慢表现后运行的一个查询。我们非常惊讶它如此缓慢。我们将尝试更新统计数据，但它们已设置为自动更新。 - BonyT

你不能只使用一个常量吗？我的意思是，它有3300万条记录和实际上有33212293条记录之间的差异对你有什么影响吗？ - bevacqua

@BonyT - 使用COUNT来诊断慢速性能并不是很有启发性。您可以打开一个新问题，附上实际问题以及可能的执行计划和/或IO统计信息吗？ - JNK

@BonyT：完全同意@JNK的建议。发布一个新问题，包括实际的慢查询、执行计划、表结构、索引等信息。 - ypercubeᵀᴹ

Answer 1

这是一个经常读取/插入/更新的表吗？您选择时是否存在更新/插入活动？

我猜测延迟是由于竞争引起的。

在我的开发服务器上，我可以在17秒内对189m行运行计数，但没有其他东西打击那个表。

如果您不太担心竞争或绝对精度，可以执行：

exec sp_spaceused 'MyTableName' 它将基于元数据给出计数。

如果您想要更精确的计数，但不一定关心它是否反映并发 DELETE 或 INSERT 活动，则可以使用带有 NOLOCK 提示的当前查询：

SELECT COUNT（id）FROM MyTable WITH（NOLOCK） ，它不会为查询获取行级锁，并且应该运行得更快。

- JNK

---

数据库并不是非常繁忙，争用也不是问题（该语句几乎总是需要30秒来响应，并使用NOLOCK提示也没有任何区别）。 - BonyT

@BonyT - 那么你可能还有其他问题。这个表有多宽？虽然这与COUNT没有直接关系，但它会影响SQL需要拉取多少页来获取计数，在更高的数据量下这很重要。你还有什么其他性能问题吗？ - JNK

已接受，尽管可能永远不知道实际问题是什么，因为在别人的领域，并且他们已经实施了一个我理解的解决方法。 - BonyT

Answer 2

想法：

• 使用SELECT COUNT(*)，这对于“有多少行”是正确的（根据ANSI SQL）。即使ID是PK，因此不可为空，SQL Server也会计算ID。不是行。

• 如果您可以接受近似计数，请使用sys.dm_db_partition_stats。请参阅我的答案：快速计算非常大的表中确切行数的方法？

• 如果您可以接受脏读取，请使用WITH (NOLOCK)

- gbn

---

优化器会有效地使用相同的计划，无论您在括号内放置什么，因此这并不重要。有些人对使用“*”很挑剔，但是这与问题无关。 - BonyT

不，我并不挑剔——有人建议我不要使用“”，因为某个地方有人开始了一个错误的传言，认为使用“”是不好的。我修改了我的问题，不使用“*”来避免这个问题，哈哈。 - BonyT

@BonyT它会使用相同的计划，但会得到不同的结果。在SQL中，聚合函数不包括空值（对COUNT也是如此）。 - Mike M.

这是一个主键 - 它不会为空！ - BonyT

@BonyT：好的，我的链接回答解决了这个谣言 :-) @Mike M：ID是主键（因此不为空）。 @Both：COUNT(*)在ANSI标准中。http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt 查找“Scalar expressions 125”和上面的那一行。当然它可能会被优化 - gbn

Answer 3

use [DatabaseName]

select tbl.name, dd.rows from sysindexes dd
inner join sysobjects tbl on dd.id = tbl.id where dd.indid < 2 and tbl.xtype = 'U'

select sum(dd.rows)from sysindexes dd
inner join sysobjects tbl on dd.id = tbl.id where dd.indid < 2 and tbl.xtype = 'U' 

通过使用这些查询，您可以在0-5秒内获取所有表的计数

根据您的要求使用where子句.....

- Gaurav Agrawal

---

参考网站是：http://planetofcoders.blogspot.com/2011/06/how-to-count-all-rows-in-particular.html - Gaurav Agrawal

仔细阅读帖子，他的关注点不是 select count(id)，而是查询速度慢。 - Rahul

Answer 4

另一个想法：当文件以1MB的部分增长时，它可能在文件系统上被分段。你无法通过SQL看到这一点，但可以使用磁盘碎片整理工具来查看。

- Stefan Steinegger