Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [recompile]

27 questions
15
votes
1 answer

What could cause a SQL Server non-cached temporary table to NOT trigger a recompile when a large amount of rows have changed?

I have observed (and reproduced) the following scenario with SQL Server 2022. The pattern in use code is executed via sp_executesql (no stored procedure is involved) The first query selects data into a temporary table A DDL statement then creates a…
Kendra Little
  • 938
  • 6
  • 13
9
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 lines of code at each level). I've been trying to…
Radu Gheorghiu
  • 983
  • 2
  • 13
  • 27
6
votes
1 answer

How to Determine if a Stored Procedure is Being Recompiled on Every Execution in SQL Server 2012

What query can I use to determine if a stored procedure is being recompiled every time it is executed in SQL Server 2012? I suspect that some of our stored procedures might be getting recompiled, but I'm not sure how to check. Thanks!
lifeisajourney
  • 751
  • 1
  • 8
  • 20
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 works well when using option(recompile), as the…
user2864740
  • 205
  • 1
  • 8
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 operators and the other one doesn't have…
DBK
  • 378
  • 1
  • 8
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 recompiled for the parameterized query when…
Marc S
  • 45
  • 5
2
votes
1 answer

What kinds of operations could cause a "Temp table changed" recompilation?

Frequent recompilation happens in one SP with 'Temp table changed' cause. What kinds of operations on temp table would trigger this? There are no alter and create index operations on temp table, just DMLs like 'Statistics changed' as below. Thanks.
Jason Liu
  • 43
  • 6
2
votes
1 answer

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 a local temp table (usually by SELECT INTO) or…
J. Mini
  • 1,161
  • 8
  • 32
2
votes
0 answers

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 called within a select statement and qualified by…
lightwing
  • 31
  • 6
2
votes
0 answers

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

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 qualified. For example, the first of these…
Archmede
  • 165
  • 6
1
vote
0 answers

Rolling blocking on Compiling a SP

We are experiencing rolling blocking when a specific stored procedure runs. This SP runs multiple times a minute, and does heavily use temp tables. It doesn't block all the time, but during peak times its prevalent. blocking lasts for 30 seconds,…
DBA Greg14
  • 265
  • 3
  • 18
1
vote
2 answers

Do temp tables cause Recompilations in SQL Server?

we have a database on SQL Server 2019 with compatibility set to 110 (SQL Server 2012). We have a stored procedure that is being recompiled frequently, and I noticed we are using many temp tables. We create a temp table, insert data, and then add an…
lifeisajourney
  • 751
  • 1
  • 8
  • 20
1
vote
1 answer

SP getting recompiled with "Statistics changed" reason even after disabling auto_stats for all tables involved

I was troubleshooting a performance issue and I need an SP to stop getting recompiled because of auto update stats. I don't want to disable Auto stats update on the entire database so I thought I could disable it on all the tables involved using…
DMDM
  • 1,750
  • 2
  • 16
  • 36
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 SELECT b.Column1 ,b.Column2 …
Data Dill
  • 255
  • 1
  • 11
1
vote
2 answers

Is it normal to UPDATE STATISTICS this frequently?

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 sniffing and I've been updating statistics on the…
J.D.
  • 40,776
  • 12
  • 62
  • 141
1
2