SQL Server Performance Forum – Threads Archive

A strange phenomena

I create a new DB just as same as the previous one. I use this new DB for my business (new data inserting to this new DB), after running for a while (about 1 hour), I import my history data from the old DB into the new one.
When all this been finished, I find a phenomena very schange. When I query data for last 1 days, it’s rather slow; while query data for rencent 30 days, it is quick as before. When querying data 2 days before for any period (1 day or n days), it is quick as before.
I don’t know why. Can any one give me an answer. Thanks. I use cluster index at date column and a replication at the tables queried on.

i am just guessing that it could be your statistics do not reflect the most recent entries follow the directions below to investigate further
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=19595

Is Auto update statistics enabled? Check the following article…
Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
MohammedU.
Moderator
SQL-Server-Performance.com

Is your query inserting historical data ordered by dat column? Have you rebuilt clustered index after insert? Does index on date use ascending order?

Even though auto update statistics is enabled, it is better to run intermittent update stats to ensure the performance is gained as explained by Joe above. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

]]>