As of May 31, 2023, we have updated our Code of Conduct.

Questions tagged [recompile]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer

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

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

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
  • 368
8 votes
1 answer

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

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

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

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

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,233
0 votes
1 answer

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,233
0 votes
0 answers

Pull out top stored procs with high recompile rate

Is there any other easy way other than running profiler that I can pull out the worst offending stored procs with high recompiles? Any helpful DMVs?
Cheerful DBA's user avatar
1 vote
2 answers

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
  • 193
1 vote
2 answers

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
  • 31.9k
3 votes
1 answer

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

utlrp.sql fails to execute

I just created a docker image of Oracle 11g ee following this tutorial and loaded some tables inside. The process worked fine and no ...
Oldfighter's user avatar
1 vote
1 answer

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

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

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