Alter SP

If I issue an "Alter SP", What happens to calls from apps using the
exisiting SP in a production environment?
For example, will my alter statement put the actual SP offline, current
appel in a web app will error with a specific error number one, recompilation
happpens automatically......
Thanks in advance
The app will be blocked from using it until the proc is recompiled.
Depending on how long it takes to recompile the proc the application might
time out.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SalamElias" <eliassal@.online.nospam> wrote in message
news:8C14C710-D83B-400E-85CC-9D546ECEAF62@.microsoft.com...
> If I issue an "Alter SP", What happens to calls from apps using the
> exisiting SP in a production environment?
> For example, will my alter statement put the actual SP offline, current
> appel in a web app will error with a specific error number one,
> recompilation
> happpens automatically......
> Thanks in advance
|||Hi Salam,
I agree with Hilary's comment.
If you want to store procudure recompile as soon as possible, you could run
sp_recompile.
Running sp_recompile on a stored procedure or a trigger causes them to be
recompiled the next time they are executed. When sp_recompile is run on a
table or a view, all of the stored procedures that reference that table or
view will be recompiled the next time they are run. sp_recompile
accomplishes recompilations by incrementing the on-disk schema version of
the object in question.
Or use the procedure_option RECOMPILE :
ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
[ { @.parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ [ OUT [ PUT ]
] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS
{ <sql_statement> [ ...n ] | <method_specifier> }
<procedure_option> ::=
[ ENCRYPTION ]
[ RECOMPILE ]
[ EXECUTE_AS_Clause ]
<sql_statement> ::=
{ [ BEGIN ] statements [ END ] }
<method_specifier> ::=
EXTERNAL NAME
assembly_name.class_name.method_name
Note : RECOMPILE
Indicates that the SQL Server 2005 Database Engine does not cache a plan
for this procedure and the procedure is recompiled at run time.
In SQL Server 2000, whenever a statement within a batch causes
recompilation, the whole batch, whether submitted through a stored
procedure, trigger, ad-hoc batch, or prepared statement, is recompiled. In
SQL Server 2005, only the statement inside the batch that causes
recompilation is recompiled. Because of this difference, recompilation
counts in SQL Server 2000 and SQL Server 2005 are not comparable. Also,
there are more types of recompilations in SQL Server 2005 because of its
expanded feature set.
Statement-level recompilation benefits performance because, in most cases,
a small number of statements causes recompilations and their associated
penalties, in terms of CPU time and locks. These penalties are therefore
avoided for the other statements in the batch that do not have to be
recompiled.
The SQL Server Profiler SP:Recompile trace event reports statement-level
recompilations in SQL Server 2005. This trace event reports only batch
recompilations in SQL Server 2000. Further, in SQL Server 2005, the
TextData column of this event is populated. Therefore, the SQL Server 2000
practice of having to trace SP:StmtStarting or SP:StmtCompleted to obtain
the Transact-SQL text that caused recompilation is no longer required.
SQL Server 2005 also adds a new trace event called SQL:StmtRecompile that
reports statement-level recompilations. This trace event can be used to
track and debug recompilations. Whereas SP:Recompile generates only for
stored procedures and triggers, SQL:StmtRecompile generates for stored
procedures, triggers, ad-hoc batches, batches that are executed by using
sp_executesql, prepared queries, and dynamic SQL.
Reference:
SQL Server 2005 Books Online - Execution Plan Caching and Reuse
http://msdn2.microsoft.com/en-us/library/ms181055.aspx
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server
2005
http://www.microsoft.com/technet/pro...05/recomp.mspx
SQL Server 2005 Books Online - ALTER PROCEDURE (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms189762.aspx
Sincerely,
Ray Yen
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti.../default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Hilary Cotter" <hilary.cotter@.gmail.com>
| References: <8C14C710-D83B-400E-85CC-9D546ECEAF62@.microsoft.com>
| Subject: Re: Alter SP
| Date: Mon, 2 Oct 2006 13:11:49 -0400
| Lines: 32
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
| Message-ID: <ucipbWk5GHA.512@.TK2MSFTNGP06.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: ool-44c103e1.dyn.optonline.net 68.193.3.225
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFT NGP06.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:446875
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| The app will be blocked from using it until the proc is recompiled.
| Depending on how long it takes to recompile the proc the application
might
| time out.
|
| --
| Hilary Cotter
| Director of Text Mining and Database Strategy
| RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
|
| This posting is my own and doesn't necessarily represent RelevantNoise's
| positions, strategies or opinions.
|
| Looking for a SQL Server replication book?
| http://www.nwsu.com/0974973602.html
|
| Looking for a FAQ on Indexing Services/SQL FTS
| http://www.indexserverfaq.com
|
|
|
| "SalamElias" <eliassal@.online.nospam> wrote in message
| news:8C14C710-D83B-400E-85CC-9D546ECEAF62@.microsoft.com...
| > If I issue an "Alter SP", What happens to calls from apps using the
| > exisiting SP in a production environment?
| > For example, will my alter statement put the actual SP offline, current
| > appel in a web app will error with a specific error number one,
| > recompilation
| > happpens automatically......
| >
| > Thanks in advance
|
|
|