SQL 2005 I have a table with 100m records with a DATE column. Within this table I have1 weeks worth of data from a single object. I query by date range to return the last hour for this single object. SQL Profiler shows me the query takes 75 secs and shows15000 reads. I query again using the same parameters but extend the date range to 1 week. SQL Profiler shows me the query takes 1 sec with exactly the same about of reads i.e 15000. I understand SQL server caches query plans but did not realise i could change the parameters of the query i.e data range and still use this cache. Can anyone explain what my testing is telling me ? Do I have any control over the time query plans are cached ? Thanks for any information at all.
See inline... -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Scott" wrote in message news:ulGtMA7JJHA.1012@TK2MSFTNGP04.phx.gbl... > SQL 2005 > I have a table with 100m records with a DATE column. Nope. There's not date atatype in 2005 (it was introduced in 2008). If you ae on 2005, I assume you are using datetime (which is not the same thing as date). > Within this table I have1 weeks worth of data from a single object. > I query by date range to return the last hour for this single object. > SQL Profiler shows me the query takes 75 secs and shows15000 reads. > I query again using the same parameters but extend the date range to 1 week. > SQL Profiler shows me the query takes 1 sec with exactly the same about of reads i.e 15000. So the plan was produced by the first execution based on what you queries for and the same plan was probably re-used for the second execution. That is a possibility, but we have no way of saying that for sure (not enough information). If that was the case and you look at *estimated* number of rows, then all this is expected and normal behavior of query plan caching. > > I understand SQL server caches query plans but did not realise i could change the parameters of > the query i.e data range and still use this cache. The plan cache is how to execute the query, not the result. > > Can anyone explain what my testing is telling me ? > > Do I have any control over the time query plans are cached ? > > Thanks for any information at all. > > >
