Questions tagged [recompile]
The recompile tag has no usage guidance.
15
questions
4
votes
1
answer
97
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 ...
- 368
8
votes
1
answer
664
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 ...
- 943
0
votes
2
answers
68
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 ...
- 29
5
votes
2
answers
250
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 ...
- 195
2
votes
0
answers
57
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 ...
- 151
0
votes
1
answer
33
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 ...
- 1,163
0
votes
1
answer
55
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
- 1,163
0
votes
0
answers
42
views
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?
1
vote
2
answers
112
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
...
- 81
1
vote
2
answers
261
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 ...
- 24.3k
3
votes
1
answer
112
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 ...
- 33
0
votes
1
answer
2k
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 ...
1
vote
1
answer
176
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 '...
- 13
0
votes
2
answers
355
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 ...
user162241
1
vote
2
answers
89
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 ...
- 454