访问最新记录时 SQL 性能变慢 [英] SQL Performance slow when accessing most recent records

问题描述

我有一个 MSSQL2005 数据库，其中的记录可以追溯到 2004 年，目前在一个特定表中只有不到 1,000,000 条记录.

I have a MSSQL2005 database that has records dating back to 2004, there are currently just under 1,000,000 records in one particular table.

事实是，如果我运行一份报告，将 2009 年数据与 2010 年数据、2008 年与 2009 年、2009 年与 2009 年或今年之前年份的任意组合进行比较，则结果会在 1-5 秒内返回.

Thing is, if I run a report comparing 2009 data against 2010 data, 2008 against 2009, 2009 against 2009 or any combination of years before this year then results are returned in 1-5 seconds.

但是，如果我运行包含 2011 年数据的报告，则该报告需要大约 6 分钟.

If however I run a report that includes 2011 data then the report takes ~6 minutes.

我检查了数据，它看起来与往年相似，并且与所有报告中使用的相同数据进行了交叉引用.

I've checked the data and it looks similar to previous years and is cross-referenced against the same data used in all of the reports.

就好像数据库超出了某个限制；今年的数据变得零散，因此更难获取.我并不是说情况确实如此，但据我所知可能是这样.

It's as if the database has exceeded some limit; that data for this year has become fragmented and therefore harder to access. I'm not saying this is the case but it may be for all I know.

大家有什么建议吗?

肖恩.

更新:
自从发布问题后，我发现 DBCC DBREINDEX table_name 似乎已经解决了问题.

Update:
Since posting the question I found DBCC DBREINDEX table_name which seems to have done the trick.

推荐答案

执行计划是什么样的?如果不同，您可能需要手动更新表上的统计信息，因为新插入的行可能会在统计信息中不成比例地未表示出来，因此它可能会选择一个次优计划.

What do the execution plans look like? If different you might need to manually update statistics on the table as the newly inserted rows are likely to be disproportionately unrepresented in the statistics and it might thus choose a sub optimal plan.

有关此问题的解释，请参阅此博客文章 统计、行估计和升序日期列

See this blog post for an explanation of this issue Statistics, row estimations and the ascending date column

另外检查您的 2011 查询是否因并发插入或更新而遇到阻塞，这些不会影响对历史数据的查询.

Additionally check that your 2011 query isn't encountering blocking due to concurrent inserts or updates that do not affect queries against historic data.

这篇关于访问最新记录时 SQL 性能变慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！