SQL performance is slow when accessing the most recent records

Question

I have a database MSSQL2005, which has records related to 2004, currently in one particular table there are a little less than 1,000,000 records.

The fact is that if I run a report comparing the data of 2009 with the data of 2010, 2008 versus 2009, 2009 versus 2009, or any combination of years before this year, the results will be returned in 1-5 seconds.

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

I checked the data and is similar to previous years and cross-references the same data as in all reports.

It is as if the database has exceeded a certain limit; that the data for this year has become fragmented and therefore harder to access. I do not say that it is, but it can be for everything that I know.

Anyone have any suggestions?

Shaun.

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

Answer 1

What do the execution plans look like? If it’s different for you, you may need to manually update the statistics in the table, since recently inserted rows can be disproportionately unrepresented in the statistics, and therefore he can choose the optimal plan.

See this blog post for an explanation of this issue. Statistics, row ratings, and ascending column

Also, make sure your 2011 query does not encounter blocking due to simultaneous inserts or updates that do not affect historical data queries.