If 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
We 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.
This 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
How to recompile a stored procedure automatically in SQL server 6.5?
Thanks in advance
Stella
Does the SQL Server 6.5 system recompile all stored procedures automatically when the server is restarted?
Thanks.
Stella
Hi,
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?
I 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?
Hey 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
Version 2008 R2
The stored procedure has the dependency on the table that was altered.
We 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 RelatedI want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies View RelatedHi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies View RelatedGreetings:
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
Thanks!
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
Thanks!
Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
Hello
I'm start to work with SSIS.
We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:
SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'
EXEC @RETVAL = sp_executeSQL @SQLSTRING
How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)
I found a way but I think i'ts only a workaround:
1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)
Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?
Thanks for an early Answer
Chaepp
Hi,
Do you know how to write stored procedures inside another stored procedure in MS SQL.
Create procedure spMyProc inputData varchar(50)
AS
----- some logical
procedure spMyProc inputInsideData varchar(10)
AS
--- some logical
--- go
-------
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
Thanks in advance
John
We 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.
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
Hi,
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)
What 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
Does 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 RelatedIs 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 !
Hello: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 RelatedWhat'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.
Hi!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 RelatedHi,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 RelatedHi ,
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
Hi,
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