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

Best Practices: Monitoring the Procedure Cache
An important indicator of the performance of the procedure cache is the Procedure
Cache Hit Ratio performance counter. It indicates the percentage of execution plan
pages found in memory as opposed to disk.
Just as with the data cache, SQL Server can expend a great amount of resources
managing the procedure cache. Fortunately, more direct control over this is available
than with the data cache. Following are a few techniques you can employ to get the
most out of the procedure cache.
Parameterize queries in applications: If an application repeatedly executes the same
queries and only literal values change from one execution to the next, then rewrite the
code to use parameters with the query. For example, suppose an application executed
the following statements:
Select * from Person.Contact where ModifiedDate = '2007-05-25' and LastName =
'Jones'
Select * from Person.Contact where ModifiedDate = '2007-04-01' and LastName =
'Smith'
This technique can lead to multiple query plans generated for what is essentially the
same query. However, if you parameterize the query:
Select * from Person.Contact where ModifiedDate = @ChangeDate and LastName =
@Lname
You can now simply create parameters in the application code. Be sure to include all
pertinent information about the parameters, such as datatype and length. Once that's
done the code can repeatedly bind new values into the parameters and execute the
command. The result will be only one query plan is generated. One thing to note here
is that this applies to all queries in a group, or batch. If the example included multiple
queries in a batch, then all the queries would need to be parameterized. See the section
''Plan Caching Issues'' for more information concerning parameterized queries.
Use fully qualified names: This is helpful because it negates the need to look up the
default schema for the current user. It also means that a plan can be used by multiple
users. So, a query such as:
Select * from ErrorLog
Should be rewritten as:
Select * from dbo.ErrorLog
Use remote procedure calls (RPC) events over language events: The easiest way to
explain this is by giving a description of what these are, using ADO.NET. An ADO.NET
command object whose CommandType is table or text will generate a language event
call to the database. However, if the CommandType is procedure then the call is
generated as an RPC call to the database.
An RPC call promotes more plan reuse by explicitly parameterizing a query. Also,
calling a stored procedure using RPC can save execution time and CPU resource
( Continued )