What does compiling a stored procedure do?

What does compiling a stored procedure do?

A compilation is the process when a stored procedure’s query execution plan is optimized, based on the current database and database objects state. This query execution plan is then stored in cache and can be quickly accessed. When a query is executed, it’s sent to the parser first.

How do I create a SQL Server plan Guide?

OBJECT plan guide

1. CREATE PROCEDURE Sales.
2. sp_create_plan_guide @name = N’Guide1′, @stmt = N’SELECT *FROM Sales.
3. SELECT TOP 1 * FROM Sales.
4. sp_create_plan_guide @name = N’Guide2′, @stmt = N’SELECT TOP 1 * FROM Sales.
5. exec sp_executesql N’SELECT * FROM Sales.

What causes SQL Recompilations?

There are multiple reasons why a recompilation can occur. A recompile of an execution plan could be due to database level configuration changes, schema changes, index changes, etc. dm_os_performance_counters SQL Server DMV and filter on counter_name for the database. We can also use Extended Events and Profiler.

Does update statistics cause recompile?

If I were to ask you a question “Does statistics update cause recompile for a query referencing the table?”, what would your answer be? In most cases, the answer is YES! In other words, a query won’t recompile even you have updated statistics for the tables being accessed.

How do you compile a procedure?

Compile one or more procedures, using one of these methods:

1. Use the ALTER PROCEDURE or ALTER PACKAGE command to recompile the procedure or the entire package.
2. Drop the procedure and create it again.
3. Use CREATE OR REPLACE to recompile the procedure.

How do I know if my plan guide is used?

A quick and reliable way to see that your query is using the plan guide is to request a pre-execution (estimated) plan. These properties do not appear in post-execution plans, nor in prepared cached plans.

Why does recompilation occur in a compiled plan?

Recompilation occurs because of following reason: On schema change of objects. Adding or dropping column to/from a table or view On change of the SET options: When a compiled plan is created, the SQL server also stores the environmental setting of a connection (SET option) with it.

What to do when recompilation event is found in SQL Server?

Some of the options you can use are: KEEP PLAN – The keep plan query hint (i.e. OPTION (KEEP PLAN)) changes recompilation thresholds for temporary tables. There is no guarantee to keep the statement in the plan cache when a recompilation event is found while statistics update.

When does SQL Server use stale plan recompilation?

For each table & index, the SQL server maintains a modification counter and if the counter values exceed the defined threshold, the previously created compiled plan is considered stale plan and a new plan is created. Detecting recompilations: The below query retrieves the top 10 statements for which the recompilation count is maximum.

How does SQL Server decide when to use a compiled plan?

On statistics change of tables: Every time the SQL Server uses an already cached compiled plan, it checks the optimality before using it. SQL Server decides whether the plan is optimal for current amount of data in underlying tables.