Professional Web Applications Themes

Cache and Stored Procs vs Prepared plans - Microsoft SQL / MS SQL Server

"Art Corum" <com> wrote in message news:094501c36591$fe6c4e80$gbl...  The key to minimizing pressure on the procedure cache is to watch out for is the number of different queries the application uses. If the application is hard-coding parameter values into queries, then the number of "different" queries the procedure cache has to manage can explode. If all the queries are properly parameterized, then storing execution plans for every one of them is no big deal, as an application usually only has a couple hundred distinct queries. Prepared queries are tricky for another reason, though. Prepared queries are bound to the connection that ...

  1. #1

    Default Re: Cache and Stored Procs vs Prepared plans

    "Art Corum" <com> wrote in message
    news:094501c36591$fe6c4e80$gbl... 


    The key to minimizing pressure on the procedure cache is to watch out for is
    the number of different queries the application uses.

    If the application is hard-coding parameter values into queries, then the
    number of "different" queries the procedure cache has to manage can explode.
    If all the queries are properly parameterized, then storing execution plans
    for every one of them is no big deal, as an application usually only has a
    couple hundred distinct queries.

    Prepared queries are tricky for another reason, though. Prepared queries
    are bound to the connection that created them so you must be using
    persistent connections to take advantage of them. (The compiled plan
    generated for the prepared query is usable by other connections, but the
    particular instance is not) Prepared queries and connection pooling are
    mutually exclusive, unless you write your own connection pool and save the
    statement handles (or commands) along with the connection. Prepared queries
    also require an extra round-trip to the server to prepare, so until you run
    the query 5 or more times on the connection, you haven't paid for the price
    of preparing it in the first place.

    David


    David Guest

  2. #2

    Default Re: Cache and Stored Procs vs Prepared plans

    Hi Art

    Your understanding is basically correct. If the prepared plan is not
    considered trivial, it will be placed in cache. Why are you worried about
    that? If the plan is not reused, it will soon be freed up so that another
    plan can take it place. The only plans that stick around will be ones that
    are reused, and you should want those to stay around to avoid the cost of
    reoptimization. I don't understand why you think proc plans are more
    valuable to keep around than other plans, if the same plan is going to be
    reused. I agree that proc plans are probably more likely to be reused, but
    then they will naturally stick around longer.

    You can look at the master..syscacheobjects table to see what is in the plan
    cache, and there is a column that tells you how often the plan has been
    used.

    --
    HTH
    ----------------
    Kalen Delaney
    SQL Server MVP
    www.SolidQualityLearning.com


    "Art Corum" <com> wrote in message
    news:094501c36591$fe6c4e80$gbl... 


    Kalen Guest

  3. #3

    Default Re: Cache and Stored Procs vs Prepared plans

    Thank you for responding.

    I am woried about this because my developers are preparing
    everything they send to the database. Thus, the cache is
    full of both the stored procs that are being used and I
    would want cached, but also of the preparations for
    executing them, thereby using up cache. (I am assuming
    that the syscacheobject.objtype ='Prepare' are in cache
    along with the procs that are executed by the so prepared
    SQL)

    Furthermore, from the article quote that, for some reason
    or another, I feel you might be familiar with, it sounds
    like perparation plans in cache are handled the same way
    as are stored procs: they are not purged until the
    lazywriter has decremented their cost to zero. That is,
    both the procs that the prepared plan invokes and the
    preparation itself (syscacheobject.objtype='Prepare') are
    in cache where only the stored procs need to be should
    they not prepare everything.

     
    plan is not 
    you worried about 
    so that another 
    will be ones that 
    avoid the cost of 
    plans are more 
    plan is going to be 
    to be reused, but 
    what is in the plan 
    plan has been [/ref]
    fill [/ref]
    plans [/ref]
    just [/ref]
    to [/ref]
    amount [/ref]
    greater [/ref]
    it [/ref]
    I/O 
    >
    >
    >.
    >[/ref]
    Art Guest

  4. #4

    Default Re: Cache and Stored Procs vs Prepared plans

    "Art Corum" <com+-> wrote in message
    news:0b1d01c365ab$61ef6dc0$gbl... 

    Push them to use parameterized commands everywhere.
    That's the key to making everyone happy.

    David


    David Guest

  5. #5

    Default Re: Cache and Stored Procs vs Prepared plans

    To clarify my last response, when I look at
    syacacheobjects, I see the objtype 'Prepared' for a stored
    proc AND the compiled and executed plans for the stored
    proc as well. The 'Prepared' is just using cache
    unnecessarily, is it not (unless they are using this plan
    in rapid succession)? 
    plan is not 
    you worried about 
    so that another 
    will be ones that 
    avoid the cost of 
    plans are more 
    plan is going to be 
    to be reused, but 
    what is in the plan 
    plan has been [/ref]
    fill [/ref]
    plans [/ref]
    just [/ref]
    to [/ref]
    amount [/ref]
    greater [/ref]
    it [/ref]
    I/O 
    >
    >
    >.
    >[/ref]
    Art Guest

  6. #6

    Default Re: Cache and Stored Procs vs Prepared plans

    Yes, it still sounds like you understand correctly, but I still don't
    understand why you are worried. Are you running low on memory and do you
    think that using this little bit of extra space for plans is going to make a
    performance difference? You could run some tests and taking the same queries
    now submitted as prepared, and make them stored procs WITH RECOMPILE, and
    then the plans will never be stored in cache. If this makes a noticable
    performance difference, then you might worry.

    In general stored procs are preferred because you do have more control over
    whether their plans are stored and reused or not. You do not have this
    control over prepared and adhoc queries. But in 99.9% of cases, this is not
    a problem.

    Another thing I am not understanding is what you would like your developers
    to do instead.

    --
    HTH
    ----------------
    Kalen Delaney
    SQL Server MVP
    www.SolidQualityLearning.com


    "Art Corum" <com> wrote in message
    news:029f01c365ac$bd8fbd00$gbl... 
    > plan is not 
    > you worried about 
    > so that another 
    > will be ones that 
    > avoid the cost of 
    > plans are more 
    > plan is going to be 
    > to be reused, but 
    > what is in the plan 
    > plan has been [/ref]
    > fill [/ref]
    > plans [/ref]
    > just [/ref]
    > to [/ref]
    > amount [/ref]
    > greater [/ref]
    > it [/ref]
    > I/O 
    > >
    > >
    > >.
    > >[/ref][/ref]


    Kalen Guest

  7. #7

    Default Re: Cache and Stored Procs vs Prepared plans


    "Kalen Delaney" <com> wrote in message
    news:phx.gbl... 

    queries 
    over 
    not 
    developers 

    The root problem is with Ad Hoc queries with hard-coded parameters.
    Autoparameterization does not work well enough to protect the procedure
    cache from filling up with useless plans.


    select
    cacheobjtype,
    objtype,
    char(13) + char(10) + ' ' +
    replace(sql,char(10), char(10) + ' ' ) +
    char(13) + char(10) + char(13) + char(10)
    from master..syscacheobjects
    order by cacheobjtype, objtype, sql

    (results as text)


    I look at my syscacheobjects and it has hunderds of entries like this (from
    a 3rd party product)

    Compiled Plan,Adhoc,
    DECLARE INPAR1 int
    SELECT * FROM SomeTable WHERE ID= 45241

    Which is quite bad enough by itself because these plans are unlikely to ever
    be reused. They just add overhead to procedure cache, and squeeze out
    useful Adhoc plans.

    What Art is worried about is what happens when all these useless plans are
    "promoted" from Adhoc to Prepared. Do they start to squeeze out plans for
    stored procedures?

    David


    David Guest

Similar Threads

  1. Stored Procs
    By @ HOTMAIL DOT COM in forum ASP Database
    Replies: 6
    Last Post: November 26th, 12:06 PM
  2. Stored Procs Compiling
    By Stanley Sinclair in forum IBM DB2
    Replies: 10
    Last Post: September 22nd, 05:24 PM
  3. SQL stored procs in 8.1
    By Murty in forum IBM DB2
    Replies: 4
    Last Post: August 25th, 11:21 PM
  4. Stored Outlines For Queries In PL/SQL Stored Procs
    By Salaam Yitbarek in forum Oracle Server
    Replies: 0
    Last Post: December 5th, 01:54 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139