代码之家 › 专栏 › 技术社区 › vgru

SQL查询执行缓慢(对于某些参数值)

sqlprofiler sql-server-2005 database sql-server sql

vgru · 技术社区 · 14 年前

我有一个带有多个表的SQLServer2005数据库。其中一个表用于存储多个设备的时间戳和消息计数器,并具有以下列:

CREATE TABLE [dbo].[Timestamps] (
[Id] [uniqueidentifier] NOT NULL,
[MessageCounter] [bigint] NULL,
[TimeReceived] [bigint] NULL,
[DeviceTime] [bigint] NULL,
[DeviceId] [int] NULL
)

Id 是唯一的主键(Guid.Comb),我在这两者上都有索引 DeviceId 和 MessageCounter 柱。

消息计数器

奇怪的是 4号装置

select top 1 * 
   from "Timestamps"
   where DeviceId = 4
   order by MessageCounter desc

但对于 1号装置

select top 1 * 
   from "Timestamps"
   where DeviceId = 1 /* this is the only line changed */
   order by MessageCounter desc

最奇怪的是设备1 排得更少 比设备4:

select count(*) from "Timestamps" where DeviceId = 4
(returns 1,839,210)

select count(*) from "Timestamps" where DeviceId = 1
(returns 323,276).

[编辑]

从这两个查询的执行计划中可以清楚地看到,设备1(下图)在索引扫描中创建了更多的行:

Execution plans for device 4 (upper) and device 1 (lower) http://img295.imageshack.us/img295/5784/execplans.png

Device 4 Actual Number of Rows: 1

Device 1 Actual Number of Rows: approx. 6,500,000

6500000行是一个非常奇怪的数字,因为我的 select count(*) 查询为设备1返回大约300000行!

6 回复 | 直到 14 年前

Marcelo Cantos 14 年前

(DeviceId, MessageCounter DESC)

另外,请尝试以下查询:

select * 
   from "Timestamps"
   where DeviceId = 1
   and MessageCounter = (SELECT MAX(MessageCounter) FROM "Timestamps" WHERE DeviceID = 1)

只是猜测:性能差异可能是因为 DeviceId = 1 它的页数比 DeviceId = 4 . 通过排序,我怀疑您正在挖掘所有匹配的页面,即使最后只选择了最上面的一行。

OMG Ponies 14 年前

你确定这些数据是最新的吗?使用 UPDATE STATISTICS :

UPDATE STATISTICS dbo.Timestamps

您是如何运行查询的?如果通过存储过程,可能您在 parameter sniffing

BeachBlocker 14 年前

执行计划图表不是很有用,因为它们没有显示所使用的索引。

最有用的信息来自以下查询

select DeviceId, max(MessageCounter) from "Timestamps" group by DeviceId

在这种情况下,SQL server如何执行查询:

服务器从高位到低位读取MessageCounter索引。对于每一行,服务器都在custered索引中嵌套seek以比较设备id。

对于设备2-4,这将很快结束,因为服务器在设备2-4的MessageCounter索引中找到一行。对于设备1,在服务器找到设备1的第一行之前,服务器需要600多万个seek操作。

读取deviceid索引并查找custered索引会更快。这应该在323k搜索后停止。甚至很糟糕。

您应该有一个包含设备id和MessageCounter的索引(正如Marcelo Cantos指出的)。

Martin Smith 14 年前

我想这一定是因为如果你按 MessageCounter 在找到第一个有着 DeviceId=4 DeviceId 有一个更好的传播

我想 设备ID=4 谓词只有在执行计划上的Filter操作符出现时才会起作用。

DeviceId, MessageCounter 会解决这个问题。但是这个设备有问题吗不再记录新数据的旧设备?如果是这样的话,您可以将DeviceId=4记录提取到它们自己的表中,并使用分区视图,这样设备上的查询就不会扫描大量不相关的记录。

另外,选择Guid.Comb作为聚集索引的原因是什么?我假定一个聚集索引在碎片化和避免热点方面具有相似的特征,但更有用。

Peter Schofield 14 年前

我的第一个想法是,这可能是由于参数嗅探造成的—实际上,SQL Server在第一次运行查询时就提出了一个计划,但该查询并不代表典型的工作负载。看到了吗 http://www.sqlshare.com/solve-parameter-sniffing-by-using-local-variables_531.aspx

关于统计的建议很好,但我怀疑您需要查看这两个查询的查询计划。您可以在查询分析器中执行此操作—大约有三个按钮位于Execute按钮的右侧。尝试查看两个查询的计划之间有什么不同。。。

marc_s 14 年前

这些查询是否发送到SQL Server 确切地 就像你贴的一样

select top 1 * 
   from "Timestamps"
   where DeviceId = 4
   order by MessageCounter desc

还是NHibernate使用了参数化查询( where deviceid = @deviceid

这也许可以解释这一点—SQL Server获取DeviceId=4的参数化查询,提出一个适用于该参数值的执行计划,但在下一次执行时,对于DeviceId=1,它会出错,而且第一个查询的执行计划对于第二种情况不再是最优的。

你能试着以相反的顺序执行这两个查询吗??先用设备ID=1,然后用设备ID=4-这会给你同样的结果吗??