like to summarize few
points about all these things.
A. Stored Procedure
Recompilation
When creating a stored procedure, we can specify WITH RECOMPILE option in it.
But such stored procedure will never benefit from the cached execution plan as
each time it is executed; it forces the cached execution plan to invalidate or
flush and create new plan based upon the parameters passed, if any, to it. I do
not see any such big benefits of this option. But one may find this useful when
such stored procedure will return results or execute only selective part of the
stored procedure body based upon supplied input parameters. For example,
statements within If-block or Select-Case block based upon input parameters.
But I still feel one should
go after the new feature of SQL Server 2008 that helps recompile statement
level queries rather than whole stored procedure. But this option will be quite
useful as such recompilation is dependent upon input data. Let's say if you are
executing the stored procedure by supplying input parameter like ‘FirstName' or
‘LastName' or ‘DateOfBirth' at a time, then statement-level query recompilation
option is better. To use this method, one has to specify that SQL-statement
within stored procedure with RECOMPILE query hint.
For example,
CREATE PROC dbo.uspExample
@x_input AS INT,
@y_input AS INT
AS
If @x_input = 1
BEGIN
SELECT x, y, z
FROM dbo.tblXYZ
WHERE xColumn >= @x_input
OPTION(RECOMPILE);
–See here the query hint
End
If @y_input = 2
BEGIN
SELECT x, y, z
FROM dbo.tblXYZ
WHERE yColumn >= @y_input
END
GO
Another tool that we can
use is sp_recompile
system stored procedure. This procedure forces recompile of user defined stored
procedure next time it is run.
Let's look at its syntax
first.
Sp_recompile
‘@somedependent_object';
Here,
‘@somedependent_object' can be either table or view or another stored procedure
or even trigger. If this ‘@somedependent_object' is table name, then sql server
will compile all the stored procedures that references this table. If this
object name is some stored procedure, then this stored procedure will recompile
next time it is run.
This option is good when the table properties are changed, and this table is in
use of many other stored procedures. Instead of recompiling each and every such
depending stored procedure, a simple sp_recompile will do enough, and with no
server restart!
But there are many other
scenarios when stored procedure recompilation can happen automatically. If
server is running out of memory, the execution cache will get flushed. If the
stored procedure has some session specific SET modifiers (like LOCK TIMEOUT,
DATEFIRST, ANSI_WARNINGS, etc), then also stored procedure can recompile.
Situations like when we mix
DDL and DML statements together inside stored procedure may also cause stored
procedure recompilation. For example, most of us create temporary tables inside
stored procedure. Then, do DML operations based upon values in these temporary
tables. Even this type of stored procedure is forced to recompile to create new
plan according to such new temporary tables. For this type of stored procedure,
one should opt for statement-level recompilation by using RECOMPILE hint in
that DML statement following DDL statements.
But what is alternative to
temporary tables here? We can use table variables instead! Or, if we cannot do
without temporary tables, then we should write such DDL statements in the
beginning of stored procedure body itself so that multiple recompilations are
not happening for a single call to this stored procedure.
So stored procedure
recompilation can be harmful and useful both depending upon situations. One has
to think twice whether recompilation is required or not.
B. Stored Procedure
Name
We should always
create a stored procedure with a full naming convention. Schema name should
always prefix the stored procedure name. Schema name will help sql server name
resolution easily when it is called. This helps sql server in which schema to
query the stored procedure.
C. Table Indexes
Tables should have proper indexes and compiled time to time as indexes may be
weird off after some time due to huge data insertion or deletion.