查询较新记录时，使用存储过程查询速度较慢

本文关键字：查询 存储过程 速度 新记录 | 更新日期: 2023-09-27 18:29:23

对不起下面的文字墙。。。

我有一个奇怪的问题。我有一个相当大的表，其中存储了过去几天Exchange2007中的邮件跟踪日志信息。记录数以百万计（约10-120万），每30分钟我会通过PowerShell计划任务从所有传输服务器批量插入一次新日志。

每天晚上我都会执行一次维护任务，清除超过一天的日志，这样桌子就不会太大，尽管如果可以的话，我想把日志保存更长时间。

该表名为MessageTracking，它有一个主键，即IDENTITY int列，[MessagelogID]，每条记录递增1。

[date-time]asc的[date-time][strong>列上有一个非聚集索引。

"发件人"answers"收件人"字段上有一个全文索引。

用户可以通过我在C#Asp.net中编写的前端网页来搜索该表。该页面允许使用4个搜索字段进行非常基本的搜索：

  • 开始日期时间
  • 结束日期时间
  • 发件人
  • 收件人

这会将查询传递给一个存储过程，该过程实际上会将记录拉回来。存储过程被称为GetMessageTracking。

现在谈谈我的奇怪问题。此查询在0秒内返回结果，速度如闪电：

USE [DATABASENAME]
GO
DECLARE @return_value int
EXEC    @return_value = [dbo].[GetMessageTracking]
        @maximumRows = 20,
        @startRowIndex = 0,
        @sortExpression = N'[date-time]',
        @SearchStartDate = N'2012-03-27 13:51',
        @SearchEndDate = N'2012-03-27 20:09',
        @SearchSender = N'user@domain.com',
        @SearchRecipient = N'Default'
SELECT  'Return Value' = @return_value
GO

如果我将@SearchStartDate参数更改为一小时后，即N'2012-03-27 14:51'，那么它在很长一段时间内都不会完成。

我只能假设它的日期时间索引有重大问题，因为全文目录通常是空闲的。我面临的挑战之一是，我实际上每小时插入数千条记录，索引（IX_DateTime）很快就会变得支离破碎，但我想不出什么好方法来阻止这种情况的发生。

所以我的问题实际上有两个方面：

1） 在搜索较新的记录时，查询需要一段时间，如何查看导致此问题的原因？

2） 当有大量插入时，有什么索引技巧吗？

我想可能是执行计划导致了这种奇怪的行为，但不，这似乎没问题。我尝试将WITH RECOMPILE选项添加到查询中，但没有任何区别。我也清除了执行计划缓存，但没有任何效果。

很确定我的问题完全是由于我的索引。

谢谢！

查询较新记录时，使用存储过程查询速度较慢

使用重新编译是一个很好的第一步。因此，我们可以排除参数嗅探的可能性，如果问题。

请张贴执行计划的屏幕截图。我想这样问题就会变得很清楚。我猜想这是一个统计问题。查询优化器认为不存在DateTime>=N'2012-03-27 14:51'的行，并因此选择了错误的计划。请尝试运行sp_updatestats。

至于你的第二个问题：将索引填充设置为50-80之间的某个值。

由于INSERT和DELETE的数量很大，表上可能存在碎片索引。以下脚本（取自SQL Authority）将为您提供有关碎片的信息

select object_name(i.object_id) as table_name,
       indexstats.avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(db_id(), null, null, null, 'detailed') indexstats
inner join sys.indexes i 
   on i.object_id = indexstats.object_id and i.index_id = indexstats.index_id
where indexstats.avg_fragmentation_in_percent > 20

如果发现大量碎片，可以重建或重新组织索引。SQL权威机构的另一篇文章提供了以下信息。

索引重建：此过程删除现有索引并重新创建索引。

alter index all on schema.table_name rebuild

索引重新组织：此过程物理地重新组织索引的叶节点。

alter index all on schema.table_name reorganize

我认为您已经发现了自己的问题——索引的碎片化和您正在进行的大量编写。

您可以通过调整表和索引增长所需的填充（磁盘空间）来帮助缓解这个问题。

相关文章：