Post reply

Does a SP get precompiled?

  • March 5, 2013 at 3:09 am

    #290653

    Hi,

    I have a large Stored Procedure. One tiny part of it is dynamic: SET @qry = 'SELECT something'; EXEC(@qry) .

    Is the result of this that the SP will be compiled every time it is called? And if so, would it be better to move the dynamic part to a new, small SP, and call that one from the large SP? Or wouldn't that give any performance improvement?

    Thanks,

    Raymond

  • March 5, 2013 at 4:02 am

    #1593383

    Recompilation may not happen if you are writing a simple select as in - select 1,2. If you are selecting from a table or tables and there is filtering involved, the SP will get recompiled at run time. This is because the optimizer did not have a plan for the dynamic part of the SP and has to generate a plan at run time. When the query is like select 1, 2 the values are supplied inline.

    Check this out with the following query. Check the last execution time column. My guess is that you will see multiple rows for a single execution of the SP, probably two rows.

    SP recompilation will also happen if you use temp table in the SP and insert a certain number (6 IIRC) of records in the temp table. You may want to flush cache before running the script.

    DBCC freeproccache

    DBCC dropcleanbuffers

    SELECT TOP 10 qs.*

    FROM sys.dm_exec_query_stats qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

    CROSS APPLY sys.dm_exec_query_plan(qs.sql_handle) qp

    WHERE st.text LIKE '%select ''me''%'

    https://sqlroadie.com/

  • March 5, 2013 at 4:22 am

    #1593398

    A single piece of dynamic SQL will not cause the entire procedure to recompile. At worse, the dynamic section will compile each time, but that depends on a number of things.

    Since SQL 2005, recompilation hasn't been at the procedure level, rather at the statement level so if a statement in a procedure triggers a recompile, it's just the statements that need recompiling that get it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 5, 2013 at 5:53 am

    #1593430

    Thanks Gail. For an SP, there can be multiple entries in the dm_exec_query_plan DMV even without SP recompilation is what this implies, right? Will insertion into temp table cause recompilation of SP or only the piece of code where it is queried?

    PS: sorry for asking questions when I can find the answers myself. I don't have access to SQL Server right now.

    https://sqlroadie.com/

  • March 5, 2013 at 6:00 am

    #1593434

    Arjun Sivadasan (3/5/2013)

    Thanks Gail. For an SP, there can be multiple entries in the dm_exec_query_plan DMV even without SP recompilation is what this implies, right?

    No. A procedure has a single plan in cache only (other than when there are set options different)

    Will insertion into temp table cause recompilation of SP or only the piece of code where it is queried?

    As I said, since SQL 2005, recompilation hasn't been at the procedure level, rather at the statement level so if a statement in a procedure triggers a recompile, it's just the statements that need recompiling that get it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 5, 2013 at 8:44 am

    #1593512

    Thanks Gail.

    https://sqlroadie.com/

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply