Posted by Erland Sommarskog on 11/25/07 09:20

Don Li (tatata9999@gmail.com) writes:
> On the first run, the query takes about 4200ms to finish;
> after some research on index optimization, I provided some index hint,
> then the query runs at
> about 3000ms. (That was yesterday).
> Just now I realized that MS SQL Server 2000 is quite "intelligent", I
> think it saves search terms
> into cache because the second time search of the a same term is much
> much faster. Now, a couple of questions:

SQL Server caches:

1) Execution plans.
2) Data.

It does not cache invidiual search terms if that is what you have in
mind. If you submit one query, and then tweak that just a little bit,
you will have no benefit of that the plan for the first query is in
cache.

However, you will have benefit of that first query dragged data into
the cache. And this is something you need to watch out, as it may
distort your measurement of execution time. If you need to press the
execution time to 1000 ms, you must first determine whether that is
1000 ms with the data in cache, or with the data on disk.

If you think it's safe to assume that the table will almost always be
in cache run the same query several times, to make sure that nothing is
read from disk, and discard the first measurement.

If you what to measure worst case, issue DBCC DROPCLEANBUFFERS between
each run to flush the cache. (But don't do this on a production server!)

> b) what other techniques out there to speed up the above described
> query? Bring it down to 1000ms would be most desirable.

Without knowledge about the tables and the query I cannot but decline.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]