Questions tagged [recompile]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
2 answers
182 views

Aside from explicitly flushing the cache or demanding recompiles, what recompiles a full stored procedure in SQL Server 2019?

I recently finished reading Plan Caching in SQL Server 2008 and I have become confused. It seems that, short of totally flushing the plan cache or explicitly demanding a stored procedure to be ...
J. Mini's user avatar
  • 129
1 vote
1 answer
132 views

Is there any benefit to WITH RECOMPILE or OPTION (RECOMPILE) in a stored procedure that uses temp tables at every stage?

Suppose that I have a stored procedure. It does not call any other stored procedures and is not called by any others, nor is it called concurrently. Every step in this stored procedure either creates ...
J. Mini's user avatar
  • 129
0 votes
1 answer
77 views

Scalar Functions Causing Plan Cache Bloat

Our plan cache is showing a large number of duplicate entries for a scalar function that is called as per the code below: SELECT dbo.fnSomeFunction('12345678') I have done some testing and found that ...
SE1986's user avatar
  • 1,960
2 votes
0 answers
136 views

Oracle 19C Auto Compile fails on Package With SQL_Macro but ALTER PACKAGE ... COMPILE Succeeds

We have a package SCHEMA.MYPACKAGE with a procedure SCHEMA.MYPACKAGE.GETDATA that calls a sql macro SCHEMA.SQLMACROFUNCTION that exists within the same schema but outside the package. The macro is ...
lightwing's user avatar
0 votes
1 answer
87 views

SQL Server 2014 Cardinality Estimator estimate final number of rows after OUTER JOIN is less than number of rows from initial table

I have a [SQL query] for SQL Server 2019. It works fine with option (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')) and very bad without this hint. I found out, that SQL Server 2014 Cardinality ...
Андрей Ерёмин's user avatar
0 votes
1 answer
2k views

Difference between with recompile and Option(recompile)

I've read online that with recompile will recompile the whole procedure whereas Option(recompile) will only the recompile a specific statement that it is used on. If a certain statement in the ...
stargazer77's user avatar
4 votes
1 answer
351 views

Execution plan with a lot of ComputeScalar operations

I executed the same query with and without OPTION (RECOMPILE). When I compare the 2 plans, visually the one main difference I see is that the plan without option recompile shows a lot of ComputeScalar ...
DBK's user avatar
  • 378
8 votes
1 answer
1k views

Why does OPTION RECOMPILE cause a predicate pushdown?

I have a SQL query that is made out of a nested mess of views and table-valued functions going on for at least 4 levels deep (I didn't have the time or patience to go through it all, it's hundreds of ...
Radu Gheorghiu's user avatar
0 votes
2 answers
169 views

Oracle 12cR2 RAC - ORA-06508

Environment: DB Version: Oracle Database 12cR2 EE on Exadata RAC: True Problem: ORA-06508 on procedure. One of our users had a DBA role privilege, we don't want such a big privilege to be defined ...
jrdba123's user avatar
5 votes
2 answers
705 views

Why does SQL Server not perform constant (UNION ALL) branch elimination with OPTION(RECOMPILE) when selecting the result into a scalar variable?

We use some 'aggregate' views to select from multiple tables using a discriminator (note: these views are not partitioned views, because the discriminator is not in the base tables). This normally ...
user2864740's user avatar
2 votes
0 answers
60 views

Does SQL Server really require fully qualified object references to match a statement to a cached execution plan [duplicate]

From SQL Server Docs, section Execution Plan Caching and Reuse: The algorithms to match new SQL statements to existing, unused execution plans in the cache require that all object references be fully ...
Archmede's user avatar
  • 165
0 votes
1 answer
64 views

can't find column stats for newly created table

I am trying to simulate a plan recompilation due to auto update stats which is triggered when threshold(500 + 20 percent modifications) reached. database current settings are: I started with ...
Rauf Asadov's user avatar
  • 1,253
0 votes
1 answer
83 views

Does plan recompilation happens to plan guide?

Does plan recompilation happens for plan guide due to the, for example, auto update stats? P.S Do not take into account adding a hint OPTION (RECOMPILE) in the plan guide
Rauf Asadov's user avatar
  • 1,253
1 vote
2 answers
246 views

Why does this stored proc cause a clustered index scan, but seek when using OPTION RECOMPILE?

I think I may know the answer based on my research, but am looking for confirmation on how/why the engine compiles the plan the way it does with Parameters being passed in: @ID int ,@OtherID INT ...
Data Dill's user avatar
  • 231
1 vote
2 answers
901 views

Is it normal to UPDATE STATISTICS this frequently? [duplicate]

I have a stored procedure that processes data from a source table and is executed many times (about 20,000 executions) throughout the day. I believe it's getting choked up sometimes, due to parameter ...
J.D.'s user avatar
  • 37.2k
3 votes
1 answer
150 views

Parameterized Plan Flushed

I cannot find documentation anywhere on what would trigger this recompilation reason. We are investigating a sudden query performance drop and the only thing that I can think of is that the plan got ...
Marc S's user avatar
  • 33
0 votes
1 answer
3k views

utlrp.sql fails to execute

I just created a docker image of Oracle 11g ee following this tutorial https://vitalflux.com/how-to-install-oracle-11g-ee-on-docker/ and loaded some tables inside. The process worked fine and no ...
Oldfighter's user avatar
1 vote
1 answer
599 views

RECOMPILE function

We had a production level event over the weekend where execution of a function suddenly went from MS to nearly a minute. Unfortunately, SQL wasn't able to execute the ALTER Function and it kept '...
MikeL's user avatar
  • 13
0 votes
2 answers
536 views

Compile Dynamic Sql before Running

I have a table, and want to test if dynamic sql will compile. Example: create Table dbo.Product ( ProductId int, ProductName varchar(255) ) This will succeed: Column Name exists declare ...
user avatar
1 vote
2 answers
96 views

SQL Server Execution Plan on server level

Case: I have application which is doing search from database. That is going very slowly (more than 40 seconds). With trace I caught a query. When I run query in SSMS, it takes 2-3 seconds, but when I ...
Darko Milic's user avatar