Why do we need to recompile stored procedures?

Another reason to force a stored procedure to recompile is to counteract, when necessary, the "parameter sniffing" behavior of stored procedure compilation. When SQL Server executes stored procedures, any parameter values used by the procedure when it compiles are included as part of generating the query plan.

Besides, what causes stored procedure to recompile?

Factors in Recompilation

Inserting or deleting lots of data (data density in indexes & tables often controls query plans)
Rebuilding indexes (a change to underlying objects)
Creating/dropping temp tables (again, underlying DML changes).
query plan ages out (think not used recently and sql want's to clean up memory use)

Additionally, what happens when a stored procedure is compiled? A stored procedure is compiled code that you can call from within T-SQL statements or from client applications. SQL Server runs the code in the procedure and then returns the results to the calling application. Using stored procedures is efficient for several reasons.

Hereof, what is the use of recompile in SQL Server?

RECOMPILE – specifies that after the query is executed, its query execution plan stored in cache is removed from cache. When the same query is executed again, there will be no existing plan in cache, so the query will have to be recompiled.

What is Sp_recompile?

sp_recompile looks for an object in the current database only. The queries used by stored procedures, or triggers, and user-defined functions are optimized only when they are compiled. SQL Server automatically recompiles stored procedures, triggers, and user-defined functions when it is advantageous to do this.

25 Related Question Answers Found

What does Option recompile do?

OPTION(RECOMPILE) tells the server not to cache the pan for given query. This means that another execution of the same query will require to elaborate a new(maybe different) plan. This is used in the queries with parameters to prevent parameter sniffing issue.

How do you recompile a procedure?

To recompile a stored procedure by using sp_recompile

Select New Query, then copy and paste the following example into the query window and click Execute. This does not execute the procedure but it does mark the procedure to be recompiled so that its query plan is updated the next time that the procedure is executed.

What is with recompile option in SP?

Both OPTION(RECOMPILE) and WITH RECOMPILE will give you execution plans based on the parameters used each time, but only OPTION (RECOMPILE) allows "parameter embedding optimization" where the parameters are replaced with literal constants when its parsing the queries.

What is parameter sniffing?

Parameter Sniffing. When the SQL Server database engine compiles a stored procedure it looks at the parameter values being passed and creates an execution plan based on these parameters. The process of looking at parameter values when compiling a stored procedure is commonly called “parameter sniffing”.

What is recompilation?

: a second or subsequent compilation: as. a : the act or process of compiling something again Aimed at the high-performance computing market, Meiosys' MetaCluster software can move applications between a server or a group of servers and other machines without the need for application modification or recompilation. —

What is SQL compilation?

When the documentation refers to recompiling a stored procedure or the SQL Server Profiler records a compilation event, compilation means the process of compiling the special T-SQL statements and optimizing the SELECT, INSERT, UPDATE, and DELETE statements.

How do I save a stored procedure in SQL Developer?

Open SQL Developer and connect to the Oracle Database. Then left side in Connections pane, expand the schema node in which you want to execute the stored procedure. Then expand the Procedures node and select the stored procedure you want to execute and do the right click on it.

What is Sp_refreshsqlmodule?

Updates the metadata for the specified non-schema-bound stored procedure, user-defined function, view, DML trigger, database-level DDL trigger, or server-level DDL trigger in the current database.

How do I compile a procedure in SQL Developer?

Creating and Compiling a PL/SQL Procedure

Right-click on the Procedures node in the Connections Navigator, to invoke the context menu, and select NEW PROCEDURE.
Enter EMP_LIST as the procedure name.
The procedure is created.
Replace the following PL/SQL:
Compile errors, if any.

How do you fix parameter sniffing?

Workarounds for SQL Server Parameter Sniffing

Create SQL Server Stored Procedures using the WITH RECOMPILE Option.
Use the SQL Server Hint OPTION (RECOMPILE)
Use the SQL Server Hint OPTION (OPTIMIZE FOR)
Use Dummy Variables on SQL Server Stored Procedures.
Disable SQL Server Parameter Sniffing at the Instance Level.

Which database is used by SQL Server Agent for scheduling alerts and jobs and recording operators?

msdb database

How do stored procedures work?

A stored procedure is a group of SQL statements that has been created and stored in the database. A stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data.

Are stored procedures precompiled?

A stored procedure is a precompiled collection of Transact-SQL statements stored under a name and processed as a unit that you can call from within another Transact-SQL statement or from the client applications. Stored procedures can have input and output parameters and can issue an integer return code.

How many types of stored procedures are there in SQL Server?

There are two main types of stored procedure – system stored procedures and user-defined stored procedures. We also have extended stored procedures that can reside as either system or user-defined types.

Where do stored procedures reside?

A stored procedure (sp) is a group of SQL requests, saved into a database. In SSMS, they can be found just near the tables. Actually in terms of software architecture, it's better to stored the T-SQL language into the database, because if a tier changes there would be no need to modify another.

How does stored procedure improve performance?

“Stored procedures are precompiled and cached so the performance is much better.”

Stored Procedures are precompiled so they are faster

The SQL syntax is checked for any errors.
The best plan is selected to execute the SQL (choice to use clustered index, non-clustered etc.).
Finally the SQL is executed.

What does precompiled mean?

precompile - Computer Definition

To do a preliminary conversion before doing the final conversion. The precompile phase sets up the source code, database, etc., in such a way that the final phase is performed faster.