Reusing Query Plans

In versions of SQL Server prior to SQL Server 7.0, only query plans for compiled objects, such as stored procedures and triggers, were kept in the procedure cache. Ad hoc query plans were immediately discarded. In SQL Server 7.0 and 2000, ad hoc query plans might remain in cache as well, if the cost to compile them is high and the query is reused frequently.

In SQL Server 2000, when SQL Server begins processing a query, it first determines if the query is an ad hoc query and if it is inexpensive to compile. If so, SQL Server doesn't bother caching the query plan in memory in order to avoid flooding the cache with query plans that probably won't be reused very often and that are cheaper simply to recompile if needed again. ...

Get Microsoft® SQL Server™ 2000 Unleashed, Second Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.