EXECUTE… WITH RECOMPILE
Jun 2, 2002What exactly does WITH RECOMPILE do? I read that it will create a new plan. I guess the next question is, what is the purpose behind that?
Can someone explain to me?
Thanks
ADVERTISEMENT
No Recompile After Sp_recompile Or WITH RECOMPILE Option
Aug 30, 2007I know that all the documentation always tells you that sp_recompile will force a stored procedure to recompile the next time it is executed. However, I am not seeing the recompiles in a SQL Trace, when capturing SP: Recompile events. I have tried this on many different database servers, using sp_recompile and also the WITH RECOMPILE option when creating the proc.
Can anyone explain this?
7.0 Recompile.
Sep 13, 2000We have lots of stored procedures containing temporary tables. In SQL 6.5 every thing was great. But in 7.0, it's doing a lot of recompile while executing. Tried trace flag 8720, didn't work..
Basically I am talking abt this problem :
http://support.microsoft.com/support/kb/articles/Q224/5/87.ASP
Let me know if any ideas/Remedies ??? How did any of you tackled this behaviour.
Option (RECOMPILE)
Oct 25, 2007Hi,
What is equivalent to OPTION (RECOMPILE) in SQl Server 2000.
Create table #Employee
(
EmpId int IDENTITY,EmpName varchar(30)
)
insert into #Employee(EmpName )
select EmpName from AllEmployees
OPTION (RECOMPILE)
Reload Or Recompile
Oct 30, 2000Does someone know whether it is better to drop and reload or sp_recompile a stored procedure to get a new, recompiled execution plan? I have another DBA telling me it is better to drop and reload the stored procedure rather than use sp_recompile. I would think that sp_recompile would be the preferred method.
View 1 Replies View RelatedRECOMPILE / REFRESH UDF?
Dec 19, 2007Is there a way (command or stored procedure) to RECOMPILE or REFRESH a USER DEFINED FUNCTION? I can recompile SPs with sp_recompile and refresh views with sp_refreshView, but I could not find any way to refresh User-defined functions (some of them are like views, with parameters).
Environment: SQL 2005 SP2.
Thanks !
Sp:Cachemiss And No Sp:recompile
Jul 20, 2005Hello:The installation details:W2K SP4, SQL Server 2000 Ent with 1GB RAM. It is a Bi-P3.When I run the Profiler to trace Stored Procedure performance, I get abunch of SP:CacheMiss for couple of stored procedure I invoke quiteoften in a web app.But I do not see SP:Recompile.Here are my questions:i) If the plan is not in the Cache, why am I not see SP: Recompile.Where else can it be tugged.ii) What are the other counters I need to monitor to see if I need morememory.Thanks in advance for any leads on this.Regards:
View 4 Replies View RelatedUsing Recompile On Stored Procedures
Aug 10, 2000If my data structure never changes, just the data itself, is
there a need to use the "with recompil option" on stored
procedures? Isn't there a performance hit having it in
the stored procedure?
Thanks
Performance Question: Recompile
Dec 29, 1999What's the performance hit for using 'WITH RECOMPILE' in a stored procedure? I'm not a serious DBA, nor do I pretend to be one, but I'm writing a sp_ to be used with both insert and updates. I'm using a variable that defines the operation (IF @operation = 'Update'...) which will be passed at run-time from ColdFusion. Do I need to use the 'WITH RECOMPILE' clause to keep the sp_ kosher with respect to the operation being performed? And what's the damage in resources?
Thanks,
John E.
Recompile Stored Procedure?
Jun 15, 2000How to recompile a stored procedure automatically in SQL server 6.5?
Thanks in advance
Stella
Recompile Stored Procedure
Jun 15, 2000Does the SQL Server 6.5 system recompile all stored procedures automatically when the server is restarted?
Thanks.
Stella
With Recompile In Stored Procedure
Aug 20, 2004Hi,
currently i am working on performance tuning on some stored procedure and found that most of the stored procedure include with recompile on top of it.
i try to remove it and now it improve a lot on speed tuning. However, for those stored procedure which is using dynamic sql, is it a must to include recompile in our stored procedure?
How To Recompile / Refresh UDFs ?
Mar 6, 2007Hi!I need to refresh an entire database.I can recompile SPs with sp_recompile (or DBCC FLUSHPROCINDB), andrefresh views with sp_refreshView, but I cannot find any way torefresh my user-defined functions (some of them are like views, withparameters).Any help appreciated :) !Ben
View 5 Replies View RelatedAuto Recompile In Sql Server
Jul 20, 2005Hi,I have a question in SQL Server 2K, I use SQL Profile to trace, andfind Stored Procedure was auto recompiled, like this row in thetrace:SP:Recompile151680762004-02-27 16:01:11.610How can I stop the auto recompile.ThanksHarold
View 7 Replies View RelatedRecompile Of Stored Procedures - URGENT
Apr 11, 2001We are developing a production/management solution for the photo finishing sector. We need a performance of 1 order priced per second.
If we run the procedure once we dont have a dramatical performance loss due to recompilation of the stored procs.
If we have about 3 consecutive sessions we find the performance loss to be at a rate of about 200 - 500 %.
We can't afford this. On the site of msdn we found some reasons why sql server needs to recompile, but since the structure of our db can't be changed in such a manner that this would resolve the problem we need an alternative.
All help is greatly appriciated.
Recompile All Stor Proc And Udf And Views
Oct 1, 2003Hi ,
what would be correct way to recompile
all user objects ?
Developers working by using alter create and rename on udf and stored procedure, dependecicies window in EM does not repesent correct info.
If I want to see up to date all dependedcies of every user defined object in db
Thank you
Alex
Stored Procedure Timing Out Until Recompile
May 17, 2007Hey all,
We have a problem with one of our MS SQL 2000 databases and some stored procedures.
I'm not sure exactly what the problem is, but these are the symptons....
The stored procedure runs without problems for a period of time. Abruptly, without warning it begins to time out when called from our web application.
Calling it through the query analyzer it runs within a second.
Forcing the stored procedure to recompile allows the web application to start calling it again without it timing out.
We have a DTS package that runs over night and imports a number of records (not sure on the exact numbers, but definately enough to make a difference to indexes) so this could be part of the problem although when I force a recompile I do not do any update stats or anything else.
I wrote a test script to call the stored procedure when it was timing out to ensure it wasn't a web application problem and the procedure continued to time out until the forced recompile. So I don't think the problem is there.
The stored procedure returns multiple results sets and when it starts timing out it is while it is returning the second results sets.
The code for the second results set is...
Select avg(round(p.PricingValue, 5)) as Average, stdev(round(p.PricingValue, 5)) as StdDev, min(p.CaptureDate) as FromDate, max(p.CaptureDate) as ToDate
From Pricing p
Inner Join Security s
On p.SecurityID = s.SecurityID
Left Outer Join Issuer i
On s.IssuerID = i.IssuerID
WHERE p.PricingTypeID = @PricingType
And p.TenorTypeID = @TenorType
And p.CaptureDate Between @DateFrom And @DateTo
AND p.SecurityID IN ( SELECT SecurityId FROM UserResult ur WHERE ur.UserResultSelected = 1 AND ur.UserID = @userID )
Does anyone have any idea what might be going on here?
Regards
Shaun
Can We Recompile View Instead Of Drop And Create
Jan 31, 2008Hi,
I increased one of my base tables column which is referenced in view
I noticed sql server didn't recognized this change and its still showing old field size in the view.
I can simply drop and create it again. But wanted to know if there is any way (command/sp) to recompile the view which will be easy to deploy in production as patch.
Thanks
- D
OPTION(RECOMPILE) And Execution Plan
Jan 23, 2008
Hi,
I use recompile option in SQL query to dynamic pass variable to optimizer.
I verify explain plan with SET STATISTICS PROFILE ON
and optimizer chose nested lookup ,ok. But if use Display Estimated Execution Plan (CTR+L) I€™ve get merge join. It€™s very confusing, some suggestion €¦?
Use AdventureWorks
go
declare @StartOrderDate datetime
set @StartOrderDate = '20040731'
SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderId
AND h.OrderDate >= @StartOrderDate
OPTION(RECOMPILE);
SQL2005SP2
djedgar
How To Force Immediate Recompile Of Triggers And Detect Errors?
Mar 23, 2006I need a way to programmatically (via JDBC) find out which triggers for a table may not compile properly, so that I can disable the bad triggers.
I can do this fine in Oracle but cannot figure out if there's a way to do this in SqlServer. (In Oracle I'd just "alter trigger... compile" and select from user_errors.)
I know how to find the triggers that exist on a table, and I know how to enable/disable individual triggers. I know about sp_recompile, but all that does is flag the trigger for recompile at the next execution.
I need to verify whether the trigger is valid without having to actually invoke it. For example, if there's a bad Update trigger, I don't want to actually execute an update on the table.
One example of what I'm dealing with is this... We have Table A and Table B. There is an update trigger on Table B that references column A.col1. Then we alter Table A to drop col1. Later we have to update Table B. At this point the update will fail because of the bad trigger. I want to find and disable the trigger before executing the update on Table B. If there are other triggers on Table B that are valid, I want to leave them alone.
Views Containing SELECT * Do Not Recompile When Table Is Modified
Mar 17, 2008If I have a view such as: SELECT T.* FROM T
When I add a column to table T the view is not updated to reflect that change.
Furthermore, if there are other columns after the * in the view (for example SELECT T.*, GETDATE() as "My Date" FROM T) the last columns will contain incorrect data.
Is there a work around for this? An "auto-recompile when tables are modified" kind of option?
Thanks
Nick
PS: This is the script I used for testing:
create table tt (
test1 int primary key,
test2 int)
go
insert into tt (test1, test2) values (1,2)
go
create view vw_tt as select *, getdate() as "My Date" from tt
go
select * from vw_tt
go
create view vw_tt2 as select * from tt
go
alter table tt add test3 int
go
select * from vw_tt
select * from vw_tt2
select * from tt
drop table tt
drop view vw_tt
drop view vw_tt2
SQL Server 2008 :: Need To Recompile Stored Procedure After A Table Alter?
Feb 5, 2015Version 2008 R2
The stored procedure has the dependency on the table that was altered.
Transact SQL :: Placement Of Option (Recompile) In Dynamic For XML Select Statement?
Apr 18, 2015I can't seem to place the "option (recompile)" in any valid position so that the following procedure executes without a syntax error .
USE [PO]
GO
/****** Object: StoredProcedure [dbo].[npSSUserLoad] Script Date: 4/18/2015 3:57:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ...
-- Generated code - DO NOT MODIFY
-- From Object Schema: 'C:XXXXXX.NetPOPOModel\_ObjectSchema
-- To regenerate this procedure use the 'Open With' option on file _ObjectSchema and select POCodeGen.exe
Declare @SqlCmd nvarchar(max)
Declare @ParamDefinitions nvarchar(1024)
Set @ParamDefinitions = N'@UserId int,NTUser varchar(30), @XmlResult XML OUTPUT'
Set @SqlCmd = N'Set @XmlResult =
(
Select
[UserId] [a],
[UserName] [b],
[code]....
Stored Procedure Doesn't Recompile After Replication Updates Underlying Tables
Mar 21, 2007We have on demand snapshot replication set up between 2 servers. When the subscriber applies the snapshot, our stored procedures start executing very slowly. Updating statistics and rebuilding indexes does not resolve the problem, however; executing sp_recompile on the affected stored procedures does fix the problem. Is this a known issue with replication? Is there a better workaround than manually recompiling stored procedures after every snapshot?
View 3 Replies View RelatedSSIS Warnings On Build 3159: Precompiled Script Failed To Load. Attempting To Recompile.
Aug 20, 2007All:
I've implemented a variation of the Custom Logging provided in this post by Jamie Thomson.
http://blogs.conchango.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-Custom-Logging-Using-Event-Handlers.aspx
Everything seems to work well on my desktop during development, but when I deployed the packages to our DEV environment the packages still execute, but I'm now receiving warning messages in the sysdtslog90 log table...
Precompiled script failed to load. Attempting to recompile. For more information, see the Microsoft Knowledge Base article, KB931846 (http://go.microsoft.com/fwlink/?LinkId=81885).
My log table (SSISLog) looks OK, with only OnPostExecute messages that include the package / task information and rowcounts.
The KB article suggests upgrading to SP2, but I'm well past SP2, using Build 3159 on both machines.
Any suggestions on how to get rid of the warnings?
Thanks!
Leda
Force SQL Server To Recompile Stored Procedures Every Time They Run (SQL Server 7/2000)
Apr 27, 2005This is a solution for a very specific problem, and it's one that you'll hardly ever use, but it's important to know about that one scenario where it can save your neck. Ordinarily, stored procedures are only recompiled if they're no longer in the procedure cache. But if a stored procedure's execution plan is still in the cache, then SQL Server reuses the compiled storedprocedure and its existing execution plan. This is almost always the best course of action. Almost always, but not always.Sometimes, however, reusing an existing plan doesn't offer the most efficient performance. Imagine, for example, that your stored procedure accepts a parameter that determines the natureof a JOIN operation. The results can vary in a big way, so you wouldn't want your procedure to be locked into an execution plan that might be completely inappropriate for that JOIN. In a highlyspecialized case like this, you might want to force SQL Server to recompile the procedure every time the procedure runs. Doing so comes at a performance cost, but this might be offset by thesavings you gain in not executing the procedure with an awful compiled execution plan. Consider carefully whether to use this approach (or whether to re-engineer the over-design of yourapplication to avoid this situation in the first place). Should you need to instruct SQL Server to recompile each time, add the WITH RECOMPILE directive to the procedure, like this: CREATE PROCEDURE ProcName @Param int /* ... other parameters */ WITH RECOMPILE AS /* ... procedure code follows */
If we omit "WITH RECOMPILE", what will be the consequence? Thanks
Help! The Transaction Log Is Full Error In SSIS Execute SQL Task When I Execute A DELETE SQL Query
Dec 6, 2006Dear all:
I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :
Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".
So this confused me, any one has any experience on this?
Many thanks,
Tomorrow
Looking For A Way To Refer To A Package Variable Within Any Transact-SQL Code Included In Execute SQL Or Execute T-SQL Task
Apr 19, 2007I'm looking for a way to refer to a package variable within any
Transact-SQL code included in either an Execute SQL or Execute T-SQL
task. If this can be done, I need to know the technique to use -
whether it's something similar to a parameter placeholder question
mark or something else.
FYI - I've been able to successfully execute Transact-SQL statements
within the Execute SQL task, so I don't think the Execute T-SQL task
is even necessary for this purpose.
SSIS Execute Package With Execute Out Of Process = True Causes ProductLevelToLow Error
Mar 6, 2008
Hi.
I have a master package, which executes child packages that are located on a SQL Server. The Child packages execute other child packages which are also located on the SQL server.
Everything works fine when I execute in process. But when I set the parameter in the mater package ExecutePackageTask to ExecuteOutOfProcess = True, I get the following error
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Row Count" (5349).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Custom Split" (6399).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Data Source" (5100).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "DST_SCR Load Data" (6149).
The child packages all run fine when executed directly, and the master package runs fine if Execute Out of Process is False.
Any help would be greatly appreciated.
Thanks
Geoff.
Recompile SQL Task With Data Flow Task
Feb 23, 2007Hi,
I created a package with SQL 2005. The package gets the Access DB and then inserts it into SQL Server.
If I open the package in .NET, I can see the SQL Task and Data Flow Task. The SQL Task has a property sqlstatementsource, which has the necxessary SQL code to create the tables.
How can I tell the SQL Task to recompile the SQL code if I give it another DB name, because the tables differ and don't map in the Data Flow Task
Thanks
Conditional Execute By Execute SQL Task Return Value?
Jun 25, 2007I have a SSIS package contains an "Execute SQL Task". The SQL will raise error or succeed. However, it sounds the package won't pick up the raised error?
Or is it possible to conditional run other control flow items according the the status of SQL task execution?
Execute A SP In The Execute SQL Task
Jan 25, 2007I am trying to execute a SP in the execute SQL task in SSIS 2005..
but I keep getting an error:
SSIS package "Package.dtsx" starting.
Error: 0xC002F210 at Load_Gs_Modifier_1, Execute SQL Task: Executing the query "exec Load_GS_Modifier_1 ?, ?" failed with the following error: "Could not find stored procedure 'exec Load_GS_Modifier_1 ?, ?'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Load_Gs_Modifier_1
SSIS package "Package.dtsx" finis
I have set up two user parameters: startdate and enddate.. I am not sure what I am doing wrong????
Trigger Not Execute Some Data Or Insert Not Execute A Trigger For Some Data
Mar 3, 2008I have trigger, but not execute somedata because insert few row in every second. I use java to insert data to SQL server 2005. Data inserted to a table but not executing trigger for some data.
For example 100 data every second inserted to a table.
If insert data one by one to a table trigger fires success.
Please Help me.