Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Unanswered: Performance tool...must be working, it's making me ask questions! *LOL*

    Hi all,

    I have downloaded an evaluation copy of a SQL Server performance tool (a fancy version of the Profiler) called Speed Coefficient from Imceda. Pretty interesting so far, but some questions are forthcoming (and probably will continue to come as I drill down and learn more about performance thangs).

    It tells me I have a recompilation that I did not expect, it says the reason is "object not found at compile time, deferred to run time", but doesn't do too well at specifically telling me which object it is complaining about (yeah, not REALLY a complaint, but perhaps more a "mention", but I digress...)

    I thought originally it was, perhaps, an object that I had not referenced correctly, but as it turns out, it is, I believe, referring to a global temporary table one of my procs creates. Upon further reflection/introspection, it makes sense to me that this is the case, since it won't HAVE the temp table object to kick around until it is created at run time.

    Does this make sense? If so, I guess this is one of those times where the tool just makes reference to a possible issue, but it's up to the user to understand what the underlying cause of the "mention" is, and to determine if it is "OK" to have the recompilation occur.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    ah-HA!!!

    To understand stored procedure recompilation more fully it is important to understand how SQL Server manages stored procedure query plans. When you create a stored procedure, its text is stored in the syscomments system table. When the stored procedure is executed, SQL Server first checks to see if the procedure has a compiled query plan available in cache. If not, the procedure is read from syscomments and a new query plan is compiled and stored in cache. The following can cause a stored procedure to be recompiled:

    The WITH RECOMPILE clause was used in the CREATE PROCEDURE or the procedure was executed using the WITH RECOMPILE option
    A schema change occurred on any of the referenced objects: Adding/dropping constraints, defaults, or rules, or dropping an index
    Running sp_recompile on the stored procedure of any referenced object
    A referenced table is dropped and re-created
    The procedure is dropped and re-created
    All copies of the execution plan in the cache are in use
    The procedure is no longer in the cache
    A sufficient percentage of data changes occurred on a referenced table in the stored procedure
    The procedure alternates between executing Data Definition Language (DDL) and Data Manipulation Language (DML) operations
    The procedure performs certain operations on temporary tables

    NOW...does anyone know an easy (read that "painless") way to figure out what, specifically, the "problem" is?
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •