SQL Server - when to use stored procedures, a big question and think about plan cache?

Every now and then I see this big question from SQL Server users that when to use a stored procedure?

Well there is a second part to this question as well, think about caching of such stored procedures There is no such database management software that can guarentee you flawless query execution over a period of time. Performance problems caused by misuse or mismanagement of plan cache, or inappropriate recompilation, can manifest themselves as simply a decrease in throughput or an increase in query response time. Problems with caching can also show up as out-of-memory errors or connection time-out errors, which can be caused by all sorts of different conditions, this is a common error you will see when a paticilar query is executed many times. A wealth of information available in SQL Server books online and other blog websites etc. few I would like to present here from my experience.

Coming back to original question on when to use, there are few guidelines that I would like to propose when you are deciding whether to use stored procedures or one of the other mechanisms:

• Stored procedures These objects should be used when multiple connections are executing batches in which the parameters are known. They are also useful when you need to have control over when a block of code is to be recompiled.

Since SQL Server 2005 version, many administrative and informational activities can be performed by using system stored procedures. The system stored procedures are grouped into the categories shown in the following table (from BOL):

Category	Description
Active Directory Stored Procedures	Used to register instances of SQL Server and SQL Server databases in Microsoft Windows 2000 Active Directory.
Catalog Stored Procedures	Used to implement ODBC data dictionary functions and isolate ODBC applications from changes to underlying system tables.
Cursor Stored Procedures	Used to implements cursor variable functionality.
Database Engine Stored Procedures	Used for general maintenance of the SQL Server Database Engine.
Database Mail and SQL Mail Stored Procedures	Used to perform e-mail operations from within an instance of SQL Server.
Database Maintenance Plan Stored Procedures	Used to set up core maintenance tasks that are required to manage database performance.
Distributed Queries Stored Procedures	Used to implement and manage Distributed Queries.
Full-Text Search Stored Procedures	Used to implement and query full-text indexes.
Log Shipping Stored Procedures	Used to configure, modify, and monitor log shipping configurations.
Automation Stored Procedures	Enable standard Automation objects to be used within a standard Transact-SQL batch.
Notification Services Stored Procedures	Used to manage SQL Server 2005 Notification Services.
Replication Stored Procedures	Used to manage replication.
Security Stored Procedures	Used to manage security.
SQL Server Profiler Stored Procedures	Used by SQL Server Profiler to monitor performance and activity.
SQL Server Agent Stored Procedures	Used by SQL Server Agent to manage scheduled and event-driven activities.
Web Task Stored Procedures	Used for creating Web pages.
XML Stored Procedures	Used for XML text management.
General Extended Stored Procedures	Provide an interface from an instance of SQL Server to external programs for various maintenance activities.

• Adhoc caching This option is beneficial only in limited scenarios. It is not dependable enough for you to design an application expecting this behavior to correctly control reuse of appropriate plans.

Consider the same procedure being called dozens or hundreds of times. Remember that SQL Server DB engine optimizer will cache the adhoc shell query that includes the actual parameter for each individual call to the procedure, even though there may be only one cached plan for the procedure itself. As SQL Server starts experiencing memory pressure, the work to insert the entry for each individual call to the procedure can begin to cause excessive waits resulting in a drop in throughput or even out-of-memory errors.

Also other kind of caching issues such as, with prepared queries, you actually specify the parameter datatype, so it’s easier to make sure you are always using the same type. When SQL Server parameterizes, it makes its own decisions as to datatype. If you look at the parameterized form of your queries of type Prepared, you’ll see the datatype that SQL Server assumed.

• Autoparameterization This option can be useful for applications that cannot be easily modified. However, it is preferable when you initially design your applications that you use methods that explicitly allow you to declare what your parameters are and what are their datatypes.

If the recompile is caused by a change in a SET option, the SQL Trace text data for TSQL statements immediately preceding the recompile event can indicate which SET option changed. It’s best to change SET options when a connection is first made, and avoid changing them after you have started submitting statements on that connection, or inside a store procedure.

In this scenario I have seen that Optimizer hints can also be used to force SQL Server to come up with a new plan in those cases in which it might be using an existing plan. Although there are dozens of hints that you can use in your Transact-SQL code to affect the plan that SQL Server comes up with, we will cover about PLAN GUIDES on seperate post or look within Kalen Delaney's blog.

•  Execution of those Stored Procedures then look at the two suggestions below:
• The sp_executesql procedure This procedure can be useful when the same batch might be used multiple times and when the parameters are known.
• The prepare and execute method These methods are useful when multiple users are executing batches in which the parameters are known, or when a single user will definitely use the same batch multiple times.

Mixing both parameterization & execution, if you try to force using sp_executesql or Prepare/Execute, all the statements in the batch must be parameterized for the plan to be reusable. If a batch has some parameterized statements and some using constants, each execution of the batch with different constants will be considered distinct, and there will be no value to the parameterization in only part of the batch. Finally as more queries are run, the amount of memory used for data page caching should increase along with the amount of memory used for plan cache.  

A hint of how you can monitor such activity there are two main tools for detecting excessive compiles and recompiles. Keep in mind that compiling and recompiling are not the same thing. Recompiling is done when an existing module or statement is determined to be no longer valid or no longer optimal. All recompiles are considered compiles, but not vice versa. For example, when there is no plan in cache, or when executing a procedure using the WITH RECOMPILE option, or executing a procedure that was created WITH RECOMPILE, SQL Server considers this a compile but not a recompile. You can use either System Monitor (PERFMON) or SQL Trace (PROFILER) to detect compilations and recompilations.

More to come....