Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - Compile and Recompile has very interesting meaning when learning Machine learning

  • Compile and Recompile has very interesting meaning when learning Machine learning. However, the same thing has similar but very interesting effect in SQL Server. Many related compilation of the code with performance at some degree it is correct and at some degree they have nothing to do with each other. When does your stored Procedures RECOMPILE inside SQL Server?

    Posted on 01-14-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

5  Answers  

Subscribe to Notifications
  • Score
    10

    When Does Recompile Stored Procedures

    Normally when a user calls a stored procedure, SQL Server does not create a new data access plan to retrieve the information from the database. The queries used by stored procedures are optimized only when they are compiled. As you make changes to the table structure or introduce new indexes which may optimize the data retrieval you should recompile your stored procedures as already compiled stored procedures may lose efficiency. By recompiling stored procedures you can optimize the queries. There are three ways in which you can force SQL Server to recompile your stored procedure:

    refrence click:: http://support.microsoft.com/kb/243586

    When a user executes a stored procedure, if it is not already available in cache, SQL Server loads the procedure and compiles a query plan. The compiled plan is saved in cache and is reused by subsequent callers of the stored procedure until some action occurs to invalidate the plan and force a recompilation. The following actions may cause recompilation of a stored procedure plan:

    •Dropping and recreating the stored procedure

    •Restoring the database containing the stored procedure or any object referenced by the stored procedure

    •The stored procedures plan dropping from the cache

    •Use of a WITH RECOMPILE clause in the CREATE PROCEDURE or EXECUTE statement.

    •Schema changes to any of the referenced objects, including adding or dropping constraints, defaults, or rules.

    •Running sp_recompile for a table referenced by the procedure.

    •Restoring the database containing the procedure or any of the objects the procedure references (if you are performing cross-database operations).

    •Sufficient server activity causing the plan to be aged out of cache.

    •Rows changed threshold: There are thresholds on the number of row changes that cause stored procedure recompilation, depending on the type of object as follows:

      Table type- Permanent  
      Empty condition -< 500 Rows
      Threshold when empty -# of Changes >= 500 
      Threshold when not empty -# of Changes >= 500 + (20 percent of Cardinality)
    


      Table type- Temporary   
      Empty condition -< 6 Rows
      Threshold when empty -# of Changes >= 6
      Threshold when not empty -# of Changes >= 500 + (20 percent of Cardinality)
    


      Table type-Table Variable  
      Empty condition -No thresholds
      Threshold when empty -No thresholds
      Threshold when not empty -No thresholds
    

    The above thresholds, recorded by Profiler under the SP:Recompile event, govern the recompilation of permanent and temporary tables. The data column EventSubClass provides the reason for recompilation. When a threshold is crossed, EventSubClass will = 2, indicating that statistics changed. Row changes are recorded in the rowmodctr column of the sysindexes table.

    •Use of certain SET options in stored procedures can cause recompilation.

    1.Generally, those that affect query behavior or result sets such as: 
      ANSI_DEFAULTS 
    
      ANSI_NULL_DFLT_OFF 
    
      ANSI_NULL_DFLT_ON 
    
      ANSI_NULLS 
    
      ANSI_PADDING 
    
      CONCAT_NULL_YIELDS_NULL 
    
      FORCEPLAN 
    
    2.Other SET options include: 
      ANSI_WARNINGS 
    
      ARITHABORT 
    
      LANGUAGE 
    
      NUMERIC_ROUNDABORT 
    
      QUOTED_IDENTIFIER
    

    All these reasons for recompiling a stored procedure did exist in earlier versions, and caused the plan to recompile before beginning execution of the procedure. In SQL Server 7.0, a new behavior is introduced that may cause a stored procedure to recompile during execution. This new behavior ensures that the optimizer always has the best possible plan for each specific statement within a procedure. The following events may cause a run-time recompilation of a stored procedure:

    •Stored procedure will recompile if there is a sufficient number of rows in a table referenced by the stored procedure has changed. SQL Server will recompile the stored procedure to be sure that the execution plan has the up-to-date statistics for the table.

    •The procedure interleaves Data Definition Language (DDL) and Data Manipulation Language (DML) operations.

    •Stored procedures will recompile if the developer has place interleaving Data Definition Language operations with Data Manipulation Language operations. This is usually caused when temporary objects are created and referenced throughout the code.

    •The procedure performs certain operations on temporary tables.

    In some cases, the cost of recompiling the stored procedure is more than the benefit derived from doing so, especially for large procedures. It is very important to note that when a recompilation is triggered, the entire batch or procedure is recompiled. This means that performance degradation is directly proportional to the size of the procedure or batch.


    refrence click :: http://msdn.microsoft.com/en-us/library/ms190439.aspx

    (a) The sp_recompile system stored procedure forces a recompile of a stored procedure the next time it is executed. For example:

    Exec sp_recompile [MySPName]

    (b) Create a stored procedure specifying WITH RECOMPILE option.

    If WITH RECOMPILE is specified SQL Server does not cache a plan for this stored procedure;

    the stored procedure is recompiled each time it is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure, resulting in different execution plans to be created each time. Ideally, you should not use this option because by using this option, you lose most of the advantages you get by substituting SQL queries with the stored procedures. However, you can use this option during early development stages of the project where database structure is still evolving and stored procedures should be recompiled as a result of these changes.

    Here is how you can create a store procedure using RECOMPILE option:

    CREATE PROCEDURE usp_MyProcedure WITH RECOMPILE
    AS
    Select SampleName, SampleDesc From SampleTable
    
    GO
    

    (c) You can force the stored procedure to be recompiled by specifying the WITH RECOMPILE option when you execute the stored procedure. Use this option only if the parameter you are supplying is atypical or if the data has significantly changed since the stored procedure was created.

    For Example:

    EXEC usp_MyProcedure WITH RECOMPILE
    

    Vedio Link http://www.novicksoftware.com/videorecompile_profiler.htm

    Thanks

    Yogesh

    Replied on Jan 14 2012 9:16AM  . 
    Yogesh Kamble
    142 · 1% · 349
  • Score
    4

    The sp_recompile system stored procedure forces a recompile of a stored procedure the next time that it is run.

    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 every time that it is executed.

    If we only want individual queries inside the stored procedure to be recompiled, instead of the complete stored procedure, specify the RECOMPILE query hint inside each query you want recompiled.

    Replied on Jan 15 2012 8:45PM  . 
    Anup Warrier
    209 · 1% · 224
  • Score
    10

    The following will force a stored procedures to recompile:

    1. Adding indexes(if optimizer sees it improves)
    2. Massive skew of data
    3. Changing data in indexed columns
    4. dropping and recreating the stored procedure
    5. using the WITH RECOMPILE clause in the CREATE PROCEDURE or the EXECUTE statement
    6. changing the schema of any referenced objects
    7. running the sp_recompile system stored procedure against a table referenced by the stored procedure
    8. restoring the database containing the stored procedure or any object referenced by the stored procedure
    9. the stored procedures plan dropping from the cache
    10. Stored procedure will recompile if there is a sufficient number of rows in a table referenced by the stored procedure has changed. SQL Server will recompile the stored procedure to be sure that the execution plan has the up-to-date statistics for the table.
    11. Stored procedures will recompile if the developer has place interleaving Data Definition Language operations with Data Manipulation Language operations. This is usually caused when temporary objects are created and referenced throughout the code.
    12. When Parameter sniffing happens
    13. Set operation(Mainly on SQL server 2000)
    SET ANSI_DEFAULTS OFF 
    SET ANSI_NULLS OFF 
    SET ANSI_PADDING OFF 
    SET ANSI_WARNINGS OFF 
    SET ARITHABORT OFF 
    SET CONCAT_NULL_YIELDS_NULL OFF 
    SET DATEFORMAT dmy 
    SET FORCEPLAN ON 
    SET LANGUAGE svenska 
    SET NUMERIC_ROUNDABORT ON 
    

    Notes: SQL Server 2008 R2 features statement-level recompilation of stored procedures. When SQL Server 2008 R2 recompiles stored procedures, only the statement that caused the recompilation is compiled, instead of the entire procedure. As a result, SQL Server uses the parameter values as they exist in the recompiled statement when regenerating a query plan. These values may differ from those that were originally passed into the procedure.

    Eg:

    USE pubs 
    SET NOCOUNT ON 
    GO 
    --Create dummy proc so we can do ALTER PROC when we generate the proc 
    CREATE PROC p AS 
    PRINT 'dummy' 
    GO 
    --Create table to hold the SET parameters to set: 
    CREATE TABLE ct (cn VARCHAR(100) PRIMARY KEY) 
    --Populate the table 
    INSERT ct VALUES('SET DATEFIRST 2') 
    INSERT ct VALUES('SET DATEFORMAT dmy') 
    INSERT ct VALUES('SET DEADLOCK_PRIORITY LOW') 
    INSERT ct VALUES('SET LOCK_TIMEOUT 200') 
    INSERT ct VALUES('SET CONCAT_NULL_YIELDS_NULL OFF') 
    INSERT ct VALUES('SET CURSOR_CLOSE_ON_COMMIT ON') 
    INSERT ct VALUES('SET DISABLE_DEF_CNST_CHK ON') 
    INSERT ct VALUES('SET LANGUAGE svenska') 
    INSERT ct VALUES('SET ARITHABORT OFF') 
    INSERT ct VALUES('SET ARITHIGNORE ON') 
    INSERT ct VALUES('SET ANSI_NULLS OFF') 
    INSERT ct VALUES('SET NOCOUNT OFF') 
    INSERT ct VALUES('SET NUMERIC_ROUNDABORT ON') 
    INSERT ct VALUES('SET QUERY_GOVERNOR_COST_LIMIT 30') 
    INSERT ct VALUES('SET ROWCOUNT 30') 
    INSERT ct VALUES('SET TEXTSIZE 3000') 
    INSERT ct VALUES('SET ANSI_DEFAULTS OFF') 
    INSERT ct VALUES('SET ANSI_PADDING OFF') 
    INSERT ct VALUES('SET ANSI_WARNINGS OFF') 
    INSERT ct VALUES('SET FORCEPLAN ON') 
    INSERT ct VALUES('SET IMPLICIT_TRANSACTIONS ON') 
    INSERT ct VALUES('SET REMOTE_PROC_TRANSACTIONS ON') 
    INSERT ct VALUES('SET TRANSACTION ISOLATION LEVEL READ COMMITTED') 
    INSERT ct VALUES('SET XACT_ABORT ON') 
    
    
    --Create the proc which in turns create a proc with the various SET parameters inside. 
    --Then debug this proc, set a breakpoint and execute the proc named "p" for  
    --each iteration and see if it triggered a recompile. 
    CREATE PROC generate_proc AS 
    DECLARE @sql NVARCHAR(2000), @set_option NVARCHAR(100) 
    DECLARE c CURSOR FOR SELECT cn FROM ct ORDER BY cn 
    OPEN c 
    FETCH NEXT FROM c INTO @set_option 
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
    SET @sql = 'ALTER PROC p AS' + CHAR(13) + CHAR(10) 
    SET @sql = @sql + @set_option + CHAR(13) + CHAR(10) 
    SET @sql = @sql + 'SELECT au_fname, au_lname FROM authors WHERE au_lname = ''White''' + CHAR(13) + CHAR(10) 
    PRINT @set_option 
    EXEC(@sql) 
    FETCH NEXT FROM c INTO @set_option 
    END 
    CLOSE c 
    DEALLOCATE c 
    
    Replied on Jan 15 2012 11:44PM  . 
    Latheesh NK
    51 · 4% · 1178
  • Score
    9

    There are two recompile events available in Profiler in SQL 2008

    1. SP:Recompile under Stored Procedures
    2. SQL:StmtRecompile under T-SQL

    The SP:Recompile event in the SQL Profiler indicates when SQL Server recompiled a stored procedure or a trigger. In SQL Server 2000 Service Pack 2 (SP2) or in SQL Server 2005, the SP:Recompile event provides more information about why the recompilation occurs, which is helpful when you are troubleshooting recompile issues.

    SQL Server 2005 Reason for recompilation

    1. Schema changed
    2. Statistics changed
    3. Deferred compile
    4. Set option changed
    5. Temp table changed
    6. Remote rowset changed
    7. For Browse permissions changed
    8. Query notification environment changed
    9. Partition view changed
    10. Cursor options changed
    11. Option (recompile) requested
    12. Dropping and recreating the stored procedure
    13. Using the WITH RECOMPILE clause in the CREATE PROCEDURE or the EXECUTE statement
    14. Restoring the database containing the stored procedure or any object referenced by the stored procedure
    15. The stored procedures plan dropping from the cache

    Schema Changes

    This recompile occurs when any of the base tables for the query have changed, when any of the indexes for the query have been rebuild or changed and when sp_recompile is run for any of the base tables.

    It does not occur for a procedure if that procedure is altered, nor does it appear if sp_recompile is run for the procedure. Both of those result in a sp:CacheRemove event.

    Statistics Changed

    The recompile occurred because statistics that the statement in question used have changed.

    The SET statement in a stored procedure causes a recompile:

    Set arithabort
    Set ansi_null_dflt_on
    Set ansi_defaults
    Set ansi_warnings
    Set ansi_padding
    Set concat_null_yields_null
    

    Deferred compile
    A deferred compile occurs when an object referenced by a statement within the batch does not exist when the batch is first compiled.

    CREATE PROCEDURE [dbo].[TestingCacheEvents2]
    AS
    
    SELECT ID, SomeDate, Status
    FROM TestingCacheEvents
    WHERE Status = 'M'
    
    CREATE TABLE #Temp (Status char(2), CountStatus int)
    
    INSERT INTO #Temp
    SELECT status, count(*)
    FROM TestingCacheEvents
    GROUP BY Status
    
    DROP TABLE #Temp
    

    Option Recompile requested

    CREATE PROCEDURE [dbo].[TestingCacheEvents5]
    AS
    
    SELECT ID, SomeDate, Status
    FROM TestingCacheEvents
    WHERE Status = 'M'
    
    SELECT Status, COUNT(*)
    FROM TestingCacheEvents
    GROUP BY Status
    OPTION (RECOMPILE)
    

    http://sqlinthewild.co.za/index.php/2010/11/18/recompiles
    http://support.microsoft.com/kb/308737

    Replied on Jan 16 2012 7:34AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    8

    Why to Recompile stored procedure?

    As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them. This optimization occurs automatically the first time a stored procedure is run after SQL Server is restarted. It also occurs if an underlying table that is used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not occur until the next time that the stored procedure is run after SQL Server is restarted. In this situation, it can be useful to force the stored procedure to recompile the next time that it executes

    Another reason to force a stored procedure to recompile is to counteract, when necessary, the "parameter sniffing" behavior of stored procedure compilation. When SQL Server executes stored procedures, any parameter values that are used by the procedure when it compiles are included as part of generating the query plan. If these values represent the typical ones with which the procedure is called subsequently, then the stored procedure benefits from the query plan every time that it compiles and executes. If not, performance may suffer.

    Forced Recompilation

    SQL Server provides three ways to force a stored procedure to recompile:

    1. The sp_recompile system stored procedure forces a recompile of a stored procedure the next time that it is run. It does this by deleting the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure is run.

    2. 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 every time that it is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure and cause different execution plans to be created every time. Use of this option is uncommon and causes the stored procedure to execute more slowly, because the stored procedure must be recompiled every time that it is executed.

      If you only want individual queries inside the stored procedure to be recompiled, instead of the complete stored procedure, specify the RECOMPILE query hint inside each query you want recompiled. This behavior mimics SQL Server's statement-level recompilation behavior noted earlier in this section, but in addition to using the stored procedure's current parameter values, the RECOMPILE query hint also uses the values of any local variables inside the stored procedure when compiling the statement. Use this option when atypical or temporary values are used in only a subset of queries that belong to the stored procedure.

    3. You can force the stored procedure to be recompiled by specifying the WITH RECOMPILE option when you execute the stored procedure. Use this option only if the parameter you are supplying is atypical or if the data has significantly changed since the stored procedure was created.

    Replied on Jan 16 2012 10:57PM  . 
    ATif-ullah Sheikh
    133 · 1% · 391

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.