Filter statistics and recompile

Question

is it a must to execute the query with recompile in order to use filter statistics ?

Answer 1

Hi @sakuraime ,

is it a must to execute the query with recompile in order to use filter statistics ?

No. It is not.
They have no necessary relationship. In most cases, filtering statistics does not change the execution plan, and there is no need to recompile.

But sometimes, in order to ensure that the returned value is correct, or there are performance concerns, SQL Server deliberately does not reuse the execution plan cached in memory and compiles a copy on the spot. This behavior is called recompile. The following are the more common situations where recompilation occurs.

1.When any object (table or view) involved in the instruction or batch has a schema change.

For example, if you add or delete a field, add or delete an index on a table or view, add or delete a constraint (Constraints) on the table, etc., the definition changes, the original execution plan may not be correct , Of course, must be recompiled.

2.Run sp_recompile

After the user has run sp_recompile on a stored procedure or trigger, a recompilation will occur the next time they are run. If the user runs sp_recompile on a table or view, all stored procedures that refer to this table (or view) must be recompiled before the next run.

3.Some actions will clear all execution plans in memory, forcing you to recompile
For example, the following actions will clear all execution plans cached by the entire SQL Server server:

Detach a database.

The database is upgraded, and the execution plan will be cleared on the new server.

The DBCC FREEPROCCACHE statement was run.

The RECONFIGURE statement was run.

The ALTER DATABASE ... MODIFY FILEGROUP statement was run.

Use ALTER DATABASE ... COLLATE statement to modify the character set (Collation) of a database.

The following actions will clear the execution plan of a database cached by the SQL Server server:

DBCC FLUSHPROCINDB statement.

ALTER DATABASE ... MODIFY NAME statement.

ALTER DATABASE ... SET ONLINE statement.

ALTER DATABASE ... SET OFFLINE statement.

ALTER DATABASE ... SET EMERGENCY statement.

DROP DATABASE statement.

When a database is automatically closed.

At the end of the DBCC CHECKDB statement.

4.When the following SET switch values change, the previous execution plans cannot be reused

ANSI_NULL_DFLT_OFF, ANSI_NULL_DFLT_ON, ANSI_NULLS, ANSI_PADDING,ANSI_WARNINGS,ARITHABORT,CONCAT_NULL_YIELDS_NULL, DATEFIRST, DATEFORMAT, FORCEPLAN, LANGUAGE,
NO_BROWSETABLE, NUMERIC_ROUNDABORT, QUOTED_IDENTIFIER
This is because these SET switches will affect the behavior of statement execution and even bring different results. They have changed, and SQL Server will redo the execution plan according to the new settings.

5.When the statistical information on the table or view changes

When the statistical information is manually updated, or when SQL Server finds that a certain statistical information needs to be updated automatically, SQL Server will recompile all the statements involved.

BR,
Mia
If the reply helped, please do “Accept Answer”.

Answer 2

Hi @sakuraime ,

Is the reply helpful?

BR,
Mia
If the reply is helped,please do "Accept Answer".--Mia

Answer 3

Hi @sakuraime ,

Is the reply helpful?

BR,
Mia
If the reply is helped,please do "Accept Answer".--Mia