All geek questions in one place

What factors can cause overwriting of a stored procedure on SQL Server?

What factors should I know about this, can lead to recompilation of the redundant stored procedure?

Sample code that will cause recompilation of the stored procedure will be useful. The goal would be to avoid recompiling, if possible, which should improve performance.

Dynamic SQL paths and variables leading to different outputs (both by data type and by number of columns), it looks like they can be a problem. Are assumptions correct? Are there any other examples.

Edit: I found another example. Creating a temporary table in the flow control statement will result in recompilation.

+6
source share
2 answers

There are several ways to ensure that the stored procedure is recompiled:

  • using WITH RECOMPILE ,
  • make a dynamic dynamic procedure (think exec() )
  • marking proc to recompile with sp_recompile .
  • changing the schema on which the cached query plan is based
  • DBCC FREEPROCCACHE call DBCC FREEPROCCACHE
  • At the query level, a single statement in proc can be recompiled with the RECOMPILE query hint (SQL 2008).

Recompilation Factors

Besides the difficult factors listed above, what causes recompilation of the stored procedure? Well, a lot of things. Some of them are interwoven with the list above, but I want to re-submit them b / c, this may not be obvious.

  • Insert or delete large amounts of data (data density in indexes and tables often controls query plans)
  • Index recovery (changing base objects)
  • Create / cancel temporary tables (again, DML changes).
  • query plan age (think not used recently, and sql wants to clear memory)

This is not a complete list. The query optimizer evolves and improves no matter how long you use SQL Server. But here are some resources that may be useful:

BUT MORE - MORE!

With that said, the presumption in your question is that recompiling is always bad for performance. In fact, often recompiling is good.

So when do you want to recompile it? Consider one example of proc that searches by last name. The stored procedures make a ' sniffing ' parameter , which is a blessing (if it works for you) and a scourge (if it works against you). First go through someone looking for Zebr% for zerbrowski. The last name index understands that this is very specific and will return, say, 3 rows from a million - therefore, one execution plan is built. With proc processing for a low row result, the next search is performed for S% . Well, S is your most common name and corresponds to 93.543 lines from 1 million.

+17
source

Some SET options can lead to recompilation of the stored procedure or even to recompilation in one execution!

Some of these options may not even be inside the SP

 --this will cause recompilation SET concat_null_yields_null ON; EXEC spMyProc;

Some parameters that cause recompilation inside SP:

ARITHABORT

ANSI_NULLS

QUOTED_IDENTIFIER

Fortunately, this does not cause recompilation: SET NOCOUNT ON;

+1
source

Source: https://habr.com/ru/post/907161/

More articles:


All Articles