How can i reduce time to generate execution plan and store into cache when Stored procedure execute first time ?

  • patelchandresh330 80387

    SSC-Addicted

    Points: 452

    How can i reduce time to generate execution plan and store into cache when Stored procedure execute first time ?

    I am using

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT,

    Views in join

    in Stored procedure.

    Please guide me.

    Thanks in Advance....

  • Gail Shaw

    SSC Guru

    Points: 1004424

    The delay on first execution is unlikely to be the execution plan. Far more likely to be loading the data into cache. Plans just don't usually take that much time to generate.

    You can test it out, on a dev server, using DropCleanBuffers or FreeProcCache to do one of the two, then see where the slow down is actually coming from.

    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
  • patelchandresh330 80387

    SSC-Addicted

    Points: 452

    GilaMonster,

    yes you are right..

    it takes time to cache more than 1.25 GB data approx.. huge amount of data page scanning and logical reads happen by this procedure..

    i really appreciate ur answare..

    Thanks for your valuable answer..

  • Gail Shaw

    SSC Guru

    Points: 1004424

    patelchandresh330 80387 (9/25/2014)


    huge amount of data page scanning and logical reads happen by this procedure..

    Then you probably want to focus on tuning the procedure so that it doesn't need to read so much. The less it needs to read, the faster the load into cache will be and the less impact the procedure will have on whatever else is running

    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
  • patelchandresh330 80387

    SSC-Addicted

    Points: 452

    Hi,

    how can we know the "Compile" and "data cache" time of particular store procedure when it execute first time.

    can you please guide me??

    Thank You in Advance...

  • ChrisM@Work

    SSC Guru

    Points: 186043

    Can you post the actual execution plan for your stored procedure?

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • patelchandresh330 80387

    SSC-Addicted

    Points: 452

    Hi,

    it happen in in most of all stored procedure. it takes more time when execute first time and then from next time it will execute in 0 seconds.

    i checked logical read/write, physical read/write. it is not big figure now.

    is there any way to know following time of particular stored procedure:

    1. Compile time (in milliseconds)

    2. stored procedure plan generation and plan cache time (in milliseconds)

    3. data/index page cache time (in milliseconds)

    is any DMV query or demo script available or any other way?

    can you please guide me..

    Thanks in advance...

  • Grant Fritchey

    SSC Guru

    Points: 395399

    Compile time for the plan is stored with the plan itself. If you look at the graphical operator it can be seen in the first operation of the plan (usually SELECT/INSERT/UPDATE/DELETE). You can also see this value in the XML.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 8 posts - 1 through 8 (of 8 total)

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