If you have a number of optional parameters in a stored procedure (that are disabled with boolean logic) that will dramatically change the way the query works depending on the parameter it is best to add (WITH COMPILE) to the stored procedure, to ensure that the best query plan is always used. The first time a stored procedure is executed, the parameters you have passed will determine the way it is executed. If you first run the stored procedure against an empty database, used some duff test fields, or you have a report based stored procedure that works in a different way depending on parameters the first call may not be the ideal query plan. By forcing the query to be recompiled for these optional varied you will massively boost performance. This feature should be used sparingly, but there are often places where it needs to be essential (Some reporting stored procedures/stored procedures used to search)