I have a query that has appropriate indexes and is shown in the query plan with an estimated subtree cost of circa 1.5. The plan shows an Index Seek, followed by Key Lookup - which is fine for a query expected to return 1 row from a set of between 5 and 20 rows (i.e. the Index Seek should find between 5 and 20 rows, and after 5 - 20 Key Lookups, we should return 1 row).
我有一个具有适当索引的查询，并显示在查询计划中，估计的子树成本约为1.5。该计划显示了一个Index Seup，然后是Key Lookup - 这对于一个预期从一行5到20行返回1行的查询很好（即Index Seek应该找到5到20行之间，以及5-20之后关键查找，我们应该返回1行）。
When run interactively, the query returns almost immediately. However, DB traces this morning show runtimes from live (a web app) that vary wildly; typically the query is taking < 100 DB Reads, and effectively 0 runtime... but we are getting a few runs that consume > 170,000 DB Reads, and runtime up to 60s (greater than our timeout value).
以交互方式运行时，查询几乎立即返回。但是，今天早上的DB跟踪显示了来自live（一个web应用程序）的运行时间变化很大;通常，查询正在进行<100 DB Reads，并且有效地运行0 ...但是我们正在进行一些消耗> 170,000 DB Reads的运行，并且运行时间高达60s（大于我们的超时值）。
What could explain this variation in disk reads? I have tried comparing queries interactively and using Actual Execution plans from two parallel runs with filter values taken from fast and slow runs, but interactively these show effectively no difference in the plan used.
什么可以解释磁盘读取的这种变化？我尝试以交互方式比较查询，并使用来自两次并行运行的实际执行计划，其中过滤值来自快速和慢速运行，但是交互式地显示这些使用的计划没有区别。
I also tried to identify other queries that could be locking this one, but I am not sure that would impact the DB Reads so much... and in any event this query tended to be the worst for runtime in my trace logs.
我还试图找出可以锁定这个查询的其他查询，但我不确定这会如何影响数据库读取......并且无论如何这个查询在跟踪日志中往往是运行时最差的。
Update: Here's a sample of the plan produced when the query is run interactively:
更新：以下是交互式运行查询时生成的计划示例：
Please ignore the 'missing index' text. It is true that changes to the current indexes could allow a faster query with fewer lookups, but that is not the issue here (there are already appropriate indexes). This is an Actual Execution Plan, where we see figures like Actual Number of Rows. For example, on the Index Seek, the Actual number of rows is 16, and the I/O cost is 0.003. The I/O cost is the same on the Key Lookup.
请忽略“缺失索引”文本。确实，对当前索引的更改可以允许更快的查询和更少的查找，但这不是问题（已经有适当的索引）。这是一个实际执行计划，我们可以看到实际行数等数字。例如，在Index Seek上，实际行数为16，I / O成本为0.003。 Key Lookup上的I / O成本相同。
Update 2: The results from the trace for this query are:
更新2：此查询的跟踪结果为：
exec sp_executesql N'select [...column list removed...] from ApplicationStatus where ApplicationGUID = @ApplicationGUID and ApplicationStatusCode = @ApplicationStatusCode;',N'@ApplicationGUID uniqueidentifier,@ApplicationStatusCode bigint',@ApplicationGUID='ECEC33BC-3984-4DA4-A445-C43639BF7853',@ApplicationStatusCode=10
The query is constructed using the Gentle.Framework SqlBuilder class, which builds parameterised queries like this:
该查询是使用Gentle.Framework SqlBuilder类构建的，该类构建参数化查询，如下所示：
SqlBuilder sb = new SqlBuilder(StatementType.Select, typeof(ApplicationStatus));
sb.AddConstraint(Operator.Equals, "ApplicationGUID", guid);
sb.AddConstraint(Operator.Equals, "ApplicationStatusCode", 10);
SqlStatement stmt = sb.GetStatement(true);
IList apps = ObjectFactory.GetCollection(typeof(ApplicationStatus), stmt.Execute());
2 个解决方案
#1
1
Could the data be being removed from the cache? That may be an explanation why with a hot cache (data already in memory), the reads recorded are very low....and then when the data is no longer in RAM, the reads would increase as it has to read it off disk again.
数据可以从缓存中删除吗？这可能是为什么使用热缓存（数据已经在内存中），记录的读取非常低......然后当数据不再在RAM中时，读取会增加，因为它必须从磁盘读取它再次。
Just one idea to get things moving.
只有一个想法让事情发生变化。
#2
1
Run profiler to see if statistics are being updated around the same time. Or simply to see what else is going.
运行探查器以查看统计信息是否在同一时间更新。或者只是看看还有什么。
Also, please add the SQL query as well the client code.
另外，请添加SQL查询以及客户端代码。
Thoughts:
思考：
- It sounds like your "5-20" rows could be far more than that
- 听起来你的“5-20”行可能远不止于此
- With a bad plan/parameter sniffing you'd get consistently bad performance
- 如果一个糟糕的计划/参数嗅探你会得到一贯糟糕的表现
- How may writes happen on this table: enough to update statistics?
- 如何写这个表：足以更新统计数据？
- Is there some datatype issue? (eg concatenating parameters and introducing a datatype conversion)
- 是否存在一些数据类型问题？ （例如，连接参数并引入数据类型转换）
#1
1
Could the data be being removed from the cache? That may be an explanation why with a hot cache (data already in memory), the reads recorded are very low....and then when the data is no longer in RAM, the reads would increase as it has to read it off disk again.
数据可以从缓存中删除吗？这可能是为什么使用热缓存（数据已经在内存中），记录的读取非常低......然后当数据不再在RAM中时，读取会增加，因为它必须从磁盘读取它再次。
Just one idea to get things moving.
只有一个想法让事情发生变化。
#2
1
Run profiler to see if statistics are being updated around the same time. Or simply to see what else is going.
运行探查器以查看统计信息是否在同一时间更新。或者只是看看还有什么。
Also, please add the SQL query as well the client code.
另外，请添加SQL查询以及客户端代码。
Thoughts:
思考：
- It sounds like your "5-20" rows could be far more than that
- 听起来你的“5-20”行可能远不止于此
- With a bad plan/parameter sniffing you'd get consistently bad performance
- 如果一个糟糕的计划/参数嗅探你会得到一贯糟糕的表现
- How may writes happen on this table: enough to update statistics?
- 如何写这个表：足以更新统计数据？
- Is there some datatype issue? (eg concatenating parameters and introducing a datatype conversion)
- 是否存在一些数据类型问题？ （例如，连接参数并引入数据类型转换）