Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 265

had to be read from disk first. A value consistently below 90 percent indicates a need to add more
physical memory.
Best Practice: Managing the Data Cache
Managing the data cache is not trivial. Therefore, one of your goals for tuning queries
is making sure the queries are making optimal use of the data cache. Since you can't
directly control the data cache, you'll have to take indirect steps in order to influence the
data cache. This means using 64-bit architecture over 32-bit. Also, as stated in the previ-
ous chapter, designing pages with the highest row density possible will also affect the
data cache. Also consider that you have a huge advantage over SQL Server's ability to
optimize. SQL Server only controls bringing the data pages in and out of the data cache.
It doesn't have the ability to infer future behavior of the cache, but you do. You have the
ability to understand the use of the database over a period of time. Thus, coaxing the
data cache to operate desirably can only be done using a human perspective.
ProcedureCache
By now you should be familiar with query plans. Not surprisingly, those plans are kept in memory.
This memory component is the procedure, or plan, cache. Like the data cache, understanding how the
procedure cache is managed will help you tune and troubleshoot queries.
Internally, SQL Server divides a plan into two parts. The first part is the query plan itself. The query
plan is a read-only data structure used by any number of users. No user context, such as SET options or
variable values, is stored here. The second part is the execution context. Here is where the user's specific
data is kept.
Internal Storage
Procedure cache is known as a cache store . Cache stores use hash tables to manage their content. A single
cache store can have more than one hash table structure. This is handy for supporting different lookup
types. The procedure cache uses this aspect to look up plans by ID name.
In-Memory Plans
When any statement is executed in SQL Server, the procedure cache is searched to find an existing plan
for the same statement. If an existing plan is found, SQL Server will reuse it, thus saving the work of
recompiling the statement. If no existing plan is found then SQL Server will generate one.
Note that in order for this approach to be useful, the cost of searching for an existing plan has to be less
than the cost of generating a plan. Fortunately, the search technique SQL Server employs is extremely
efficient and inexpensive.
Zero Cost Plans
If the cost of generating a plan is very low, it will not be cached at all. These are known as zero cost plans .
The cost of storing and searching for these plans is greater than simply generating the plan every time.