Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old February 11th, 2005, 05:29 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: Cairo, , Egypt.
Posts: 336
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to alyeng2000
Default Procedures Recompilation

Hi All,
i Heared That Some Procedure Forced to be Recompiled by Sql Server for each execution even Command With recompile Is Not Added


could i Know If The Procedure is Recompliled Or Not
by profiler or else

Ahmed Ali
Software Developer
__________________
Ahmed Ali
Senior Software Developer
  #2 (permalink)  
Old February 11th, 2005, 10:23 AM
Friend of Wrox
 
Join Date: Jan 2004
Location: , , USA.
Posts: 303
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Generally SQL server recompile stored proc when

(1)You told SQL server to do so explicitly by

(a)The sp_recompile system stored procedure forces a recompile of a stored procedure the next time it is run.
(b)Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled each time it is executed.
(c)You can force the stored procedure to be compiled by specifying the WITH RECOMPILE option when you execute the stored procedure.

(2)New distribution statistics generated either explicitly from a statement such as UPDATE STATISTICS or automatically.

(3)SET Statements That Change Session Options

Changing the value of five session options with the SET statement cause a recompile. The options are: ANSI_DEFAULTS, ANSI_WARNINGS, ANSI_PADDING, CONCAT_NULL_YEILDS_NULL.

(4) Any structural changes made to a table or view referenced by the stored proc (ALTER TABLE and ALTER VIEW).
  #3 (permalink)  
Old February 11th, 2005, 10:38 AM
Friend of Wrox
 
Join Date: Jan 2004
Location: , , USA.
Posts: 303
Thanks: 0
Thanked 0 Times in 0 Posts
Default

*could i Know If The Procedure is Recompliled Or Not
by profiler or else*

http://support.microsoft.com/kb/308737/


Similar Threads
Thread Thread Starter Forum Replies Last Post
2 procedures? scandalous Access 5 January 26th, 2007 05:06 PM
Help me to DO Stored Procedures msbsam SQL Server 2000 3 October 23rd, 2006 01:54 PM
Stored Procedures jazzcatone Classic ASP Databases 0 August 28th, 2005 02:57 PM
Regarding Procedures spraveens MySQL 1 March 10th, 2004 10:08 AM
Right Security Procedures Walden SQL Server 2000 0 November 12th, 2003 11:09 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.