Measuring plan eviction

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;







up vote
4
down vote

favorite












We have a SQL Server 2016 SP1 with max memory set to 24GB.

This server has a high numbers of compiles, only 10% of these compiles are from Ad-Hoc queries. So the newly compiled plans should be stored in the plan cache but the size of the plan cache is not increasing (approx 3.72GB).


I suspect that there is local memory pressure that lead to removal of plans from the cache. The plan cache pressure limit is 5GB. (75% of visible target memory from 0-4GB + 10% of visible target memory from 4GB-64GB + 5% of visible target memory>64GB). When a cachestore reaches 75% of the pressure limit, plans should be removed from the cache. In my case 75% of 5 GB is 3.75GB. So it is plausible this is the cause of the high compiles.


Is there a way to measure (perfmon, extended events, ...) the removal from plans out of the cache? So I can be certain local memory pressure is really the cause of the high compiles?






sql-server-2016 database-internals plan-cache







share|improve this question



























    up vote
    4
    down vote

    favorite












    We have a SQL Server 2016 SP1 with max memory set to 24GB.

    This server has a high numbers of compiles, only 10% of these compiles are from Ad-Hoc queries. So the newly compiled plans should be stored in the plan cache but the size of the plan cache is not increasing (approx 3.72GB).


    I suspect that there is local memory pressure that lead to removal of plans from the cache. The plan cache pressure limit is 5GB. (75% of visible target memory from 0-4GB + 10% of visible target memory from 4GB-64GB + 5% of visible target memory>64GB). When a cachestore reaches 75% of the pressure limit, plans should be removed from the cache. In my case 75% of 5 GB is 3.75GB. So it is plausible this is the cause of the high compiles.


    Is there a way to measure (perfmon, extended events, ...) the removal from plans out of the cache? So I can be certain local memory pressure is really the cause of the high compiles?






    sql-server-2016 database-internals plan-cache







    share|improve this question























      up vote
      4
      down vote

      favorite









      up vote
      4
      down vote

      favorite











      We have a SQL Server 2016 SP1 with max memory set to 24GB.

      This server has a high numbers of compiles, only 10% of these compiles are from Ad-Hoc queries. So the newly compiled plans should be stored in the plan cache but the size of the plan cache is not increasing (approx 3.72GB).


      I suspect that there is local memory pressure that lead to removal of plans from the cache. The plan cache pressure limit is 5GB. (75% of visible target memory from 0-4GB + 10% of visible target memory from 4GB-64GB + 5% of visible target memory>64GB). When a cachestore reaches 75% of the pressure limit, plans should be removed from the cache. In my case 75% of 5 GB is 3.75GB. So it is plausible this is the cause of the high compiles.


      Is there a way to measure (perfmon, extended events, ...) the removal from plans out of the cache? So I can be certain local memory pressure is really the cause of the high compiles?






      sql-server-2016 database-internals plan-cache







      share|improve this question













      We have a SQL Server 2016 SP1 with max memory set to 24GB.

      This server has a high numbers of compiles, only 10% of these compiles are from Ad-Hoc queries. So the newly compiled plans should be stored in the plan cache but the size of the plan cache is not increasing (approx 3.72GB).


      I suspect that there is local memory pressure that lead to removal of plans from the cache. The plan cache pressure limit is 5GB. (75% of visible target memory from 0-4GB + 10% of visible target memory from 4GB-64GB + 5% of visible target memory>64GB). When a cachestore reaches 75% of the pressure limit, plans should be removed from the cache. In my case 75% of 5 GB is 3.75GB. So it is plausible this is the cause of the high compiles.


      Is there a way to measure (perfmon, extended events, ...) the removal from plans out of the cache? So I can be certain local memory pressure is really the cause of the high compiles?






      sql-server-2016 database-internals plan-cache




      sql-server-2016 database-internals plan-cache






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked 54 mins ago









      Frederik Vanderhaegen

      556315




      556315




















          1 Answer
          1






          active

          oldest

          votes

















          up vote
          3
          down vote













          There's an XEvent for that:




          query_cache_removal_statistics



          Occurs when a query plan is removed from the plan cache and the
          historical statistics for the object are about to be destroyed




          So something like:



          CREATE EVENT SESSION [PlanCacheEvictions] ON SERVER 
          ADD EVENT sqlserver.query_cache_removal_statistics(
           ACTION(sqlserver.sql_text))


          Also if your plan cache has a large number of single-use plans consider setting optimize for ad hoc workloads.






          share|improve this answer




















            Your Answer








            StackExchange.ready(function()
            var channelOptions =
            tags: "".split(" "),
            id: "182"
            ;
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function()
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled)
            StackExchange.using("snippets", function()
            createEditor();
            );

            else
            createEditor();

            );

            function createEditor()
            StackExchange.prepareEditor(
            heartbeatType: 'answer',
            convertImagesToLinks: false,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: null,
            bindNavPrevention: true,
            postfix: "",
            imageUploader:
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            ,
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            );



            );













             

            draft saved


            draft discarded


















            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f222091%2fmeasuring-plan-eviction%23new-answer', 'question_page');

            );

            Post as a guest

















            1 Answer
            1






            active

            oldest

            votes








            1 Answer
            1






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes








            up vote
            3
            down vote













            There's an XEvent for that:




            query_cache_removal_statistics



            Occurs when a query plan is removed from the plan cache and the
            historical statistics for the object are about to be destroyed




            So something like:



            CREATE EVENT SESSION [PlanCacheEvictions] ON SERVER 
            ADD EVENT sqlserver.query_cache_removal_statistics(
             ACTION(sqlserver.sql_text))


            Also if your plan cache has a large number of single-use plans consider setting optimize for ad hoc workloads.






            share|improve this answer
























              up vote
              3
              down vote













              There's an XEvent for that:




              query_cache_removal_statistics



              Occurs when a query plan is removed from the plan cache and the
              historical statistics for the object are about to be destroyed




              So something like:



              CREATE EVENT SESSION [PlanCacheEvictions] ON SERVER 
              ADD EVENT sqlserver.query_cache_removal_statistics(
               ACTION(sqlserver.sql_text))


              Also if your plan cache has a large number of single-use plans consider setting optimize for ad hoc workloads.






              share|improve this answer






















                up vote
                3
                down vote










                up vote
                3
                down vote









                There's an XEvent for that:




                query_cache_removal_statistics



                Occurs when a query plan is removed from the plan cache and the
                historical statistics for the object are about to be destroyed




                So something like:



                CREATE EVENT SESSION [PlanCacheEvictions] ON SERVER 
                ADD EVENT sqlserver.query_cache_removal_statistics(
                 ACTION(sqlserver.sql_text))


                Also if your plan cache has a large number of single-use plans consider setting optimize for ad hoc workloads.






                share|improve this answer












                There's an XEvent for that:




                query_cache_removal_statistics



                Occurs when a query plan is removed from the plan cache and the
                historical statistics for the object are about to be destroyed




                So something like:



                CREATE EVENT SESSION [PlanCacheEvictions] ON SERVER 
                ADD EVENT sqlserver.query_cache_removal_statistics(
                 ACTION(sqlserver.sql_text))


                Also if your plan cache has a large number of single-use plans consider setting optimize for ad hoc workloads.







                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered 23 mins ago









                David Browne - Microsoft

                9,581725




                9,581725



























                     

                    draft saved


                    draft discarded















































                     


                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function ()
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f222091%2fmeasuring-plan-eviction%23new-answer', 'question_page');

                    );

                    Post as a guest
































































                    Comments

                    Post a Comment

                    Popular posts from this blog

                    How to decode/decipher Mozilla Firefox proprietary .jsonlz4 format? (sessionstore-backups/recovery.jsonlz4)

                    Image
                    Clash Royale CLAN TAG #URR8PPP up vote 3 down vote favorite I'm trying to get a handle on Mozilla Firefox's proprietary file format .jsonlz4 , used, for example, for sessionstore-backups/recovery.jsonlz4 , but to no avail. How do I get back my data, specifically, some long text I've typed in some textareas of a crashed session? It's my data! firefox backup json session-restore jsonlz4 share | improve this question asked 3 hours ago cnst 1,049 3 16 33 add a comment  |  up vote 3 down vote favorite I'm trying to get a handle on Mozilla Firefox's proprietary file format .jsonlz4 , used, for example, for sessionstore-backups/recovery.jsonlz4 , but to no avail. How do I get back my data, specifically, some long text I've typed in some textareas of a crashed session? It's my data! firefox backup json session-restore jsonlz4 share | improve this question asked 3 hours ago cnst ...
                    Read more

                    White Anglo-Saxon Protestant

                    Image
                    White Anglo-Saxon Protestants ( WASPs ) are a social group of wealthy and well-connected white Americans, of Protestant and predominantly British ancestry, who trace their ancestry to the American colonial period. Until at least the 1960s, this group has dominated American society and culture and dominated in the leadership of the Whig and Republican parties. They usually are very well placed in major financial, business, legal and academic institutions and had close to a monopoly of elite society due to intermarriage and nepotism. [1] During the latter half of the twentieth century, outsider ethnic and racial groups grew in influence and WASP dominance weakened. Americans are increasingly criticizing the WASP hegemony and disparaging WASPs as the epitome of "the Establishment". The Random House Unabridged Dictionary (1998) says the term is "Sometimes Disparaging and Offensive". [2] [3] The term WASP is often used as a pejorative to classify their historical dom...
                    Read more

                    How to be valuable when automation & IT are stealing my job (and maybe my whole career)? [closed]

                    Image
                    Clash Royale CLAN TAG #URR8PPP .everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0; up vote 10 down vote favorite I am a non-IT employee, graduated in business studies, and work in a field related to customer relationship management. I have grown in this field during the past five years since completing my education, and there is a frightening trend in my company and across the industry: People are being laid off because automation can do the jobs of hundreds with the help of programming. Even my own team was halved and I keep wondering what I can do to stay relevant in the longer run. I have the sincere impression that very soon my team won't exist anymore and also skills/jobs in my field will become redundant across other industries. It also seems that only high level functions will require employees but they will be people with solid IT qualifications. So here is my question: How to ensure I stay valuable to my company (or eve...
                    Read more