none
Execution Plan Caching - Zero Cost plans

    Question

  • Hi all

    I am aware that SQL Server's plan cache eviction policy is: When memory pressure is detected, all Zero-cost plans are removed from the plan cache.

    1. By zero-cost, does this mean the cost of executing the query or the cost of compiling the actual plan?
    2. By memory pressure - is this internal SQL memory pressure or pressure from the OS?
    3. Is this handled solely by the lazy writer?

    Thanks

    Wednesday, September 18, 2013 1:52 PM

Answers

    1. The cost of compiling the exec plan: Cost = IO cost + CPU cost + Memory cost. Cost is measured in "ticks" and cannot be more than 31.
    2. Both.
    3. This is handled by internal resource monitor thread that decreases cost of execution plan (2 ticks a time) and evicts zero cost exec plan. Resource monitor does this only when there is a memory pressure. Lazy writer is a process that flushes dirty pages from data memory pool to disk.

    Good reading about this topic:

    http://msdn.microsoft.com/en-us/library/dn148262.aspx

    http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/16/9-0-memory-pressure-limits.aspx


    Regards, Dean Savović


    • Edited by Dean Savović Wednesday, September 18, 2013 5:13 PM
    • Marked as answer by SQL24 Thursday, September 19, 2013 8:25 AM
    Wednesday, September 18, 2013 5:12 PM
  • Hi SQL24,

    You are right about lazy writer removing unused pages from the buffer pool, this is called maintaining the buffer pool free list of pages. Lazy writer also writes dirty pages from buffer pool, that have been written to transaction log, to data disk.

    Zero cost plan removal from procedure cache is not performed by Lazy writer, it is done by resource monitor thread.


    Regards, Dean Savović

    • Marked as answer by SQL24 Thursday, September 19, 2013 8:25 AM
    Thursday, September 19, 2013 7:50 AM
  • I am aware that SQL Server's plan cache eviction policy is: When memory pressure is detected, all Zero-cost plans are removed from the plan cache.

    1. By zero-cost, does this mean the cost of executing the query or the cost of compiling the actual plan?
    2. By memory pressure - is this internal SQL memory pressure or pressure from the OS?
    3. Is this handled solely by the lazy writer?

    Thanks

    Hello SQl24,

    >>By zero-cost, does this mean the cost of executing the query or the cost of compiling the actual plan?

    I got it from kalens article: Basic queries with no joins are regarded to as Simple and plans produced as such for these queries have zero cost so these are zero cost plans and they are also referred to as trivial plans.

    2.>>By memory pressure:

    This can happen both in case local or global memory pressure.

    3: SQL server itself determines it by resource monitor thread and takes action


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers



    • Edited by Shanky_621 Thursday, September 19, 2013 8:10 AM
    • Marked as answer by SQL24 Thursday, September 19, 2013 8:25 AM
    Thursday, September 19, 2013 8:09 AM
  • Hi SQL24,

    You have posted a link for SQL 2005 - the process changed in SQL 2008 to use the resource monitor thread which is why that's what has been posted above (if you don't post the version of SQL you are using then it will be assumed you are using a current version).

    Is it SQL 2005 you want the answer for?  Or are you working on a more recent version?

    • Marked as answer by SQL24 Thursday, September 19, 2013 8:25 AM
    Thursday, September 19, 2013 8:19 AM

All replies

    1. The cost of compiling the exec plan: Cost = IO cost + CPU cost + Memory cost. Cost is measured in "ticks" and cannot be more than 31.
    2. Both.
    3. This is handled by internal resource monitor thread that decreases cost of execution plan (2 ticks a time) and evicts zero cost exec plan. Resource monitor does this only when there is a memory pressure. Lazy writer is a process that flushes dirty pages from data memory pool to disk.

    Good reading about this topic:

    http://msdn.microsoft.com/en-us/library/dn148262.aspx

    http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/16/9-0-memory-pressure-limits.aspx


    Regards, Dean Savović


    • Edited by Dean Savović Wednesday, September 18, 2013 5:13 PM
    • Marked as answer by SQL24 Thursday, September 19, 2013 8:25 AM
    Wednesday, September 18, 2013 5:12 PM
  • check this http://technet.microsoft.com/en-us/library/aa337560(v=sql.105).aspx

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, September 18, 2013 5:34 PM
    1. The cost of compiling the exec plan: Cost = IO cost + CPU cost + Memory cost. Cost is measured in "ticks" and cannot be more than 31.
    2. Both.
    3. This is handled by internal resource monitor thread that decreases cost of execution plan (2 ticks a time) and evicts zero cost exec plan. Resource monitor does this only when there is a memory pressure. Lazy writer is a process that flushes dirty pages from data memory pool to disk.

    Good reading about this topic:

    http://msdn.microsoft.com/en-us/library/dn148262.aspx

    http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/16/9-0-memory-pressure-limits.aspx


    Regards, Dean Savović


    As far as I understood - Lazy Writer is a process that periodically checks the status of the buffer pool, including the plan cache and if there is memory pressure, it will take the necessary actions by doing the following:

    Removes unused pages from the buffer pool by using the LRU-K process

    Removes Zero-cost plans from the plan cache and the cost of all other plans is reduced by half.

    Is this right?

    Thursday, September 19, 2013 7:10 AM
  • Hi SQL24,

    You are right about lazy writer removing unused pages from the buffer pool, this is called maintaining the buffer pool free list of pages. Lazy writer also writes dirty pages from buffer pool, that have been written to transaction log, to data disk.

    Zero cost plan removal from procedure cache is not performed by Lazy writer, it is done by resource monitor thread.


    Regards, Dean Savović

    • Marked as answer by SQL24 Thursday, September 19, 2013 8:25 AM
    Thursday, September 19, 2013 7:50 AM
  • I am aware that SQL Server's plan cache eviction policy is: When memory pressure is detected, all Zero-cost plans are removed from the plan cache.

    1. By zero-cost, does this mean the cost of executing the query or the cost of compiling the actual plan?
    2. By memory pressure - is this internal SQL memory pressure or pressure from the OS?
    3. Is this handled solely by the lazy writer?

    Thanks

    Hello SQl24,

    >>By zero-cost, does this mean the cost of executing the query or the cost of compiling the actual plan?

    I got it from kalens article: Basic queries with no joins are regarded to as Simple and plans produced as such for these queries have zero cost so these are zero cost plans and they are also referred to as trivial plans.

    2.>>By memory pressure:

    This can happen both in case local or global memory pressure.

    3: SQL server itself determines it by resource monitor thread and takes action


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers



    • Edited by Shanky_621 Thursday, September 19, 2013 8:10 AM
    • Marked as answer by SQL24 Thursday, September 19, 2013 8:25 AM
    Thursday, September 19, 2013 8:09 AM
  • Hi SQL24,

    You are right about lazy writer removing unused pages from the buffer pool, this is called maintaining the buffer pool free list of pages. Lazy writer also writes dirty pages from buffer pool, that have been written to transaction log, to data disk.

    Zero cost plan removal from procedure cache is not performed by Lazy writer, it is done by resource monitor thread.


    Regards, Dean Savović

    "Zero cost plan removal from procedure cache is not performed by Lazy writer, it is done by resource monitor thread."

    I'm going to have to disagree with you on this point.

    This is taken from http://msdn.microsoft.com/en-us/library/ms181055%28v=sql.90%29.aspx

    Thursday, September 19, 2013 8:13 AM
  • Hi SQL24,

    You have posted a link for SQL 2005 - the process changed in SQL 2008 to use the resource monitor thread which is why that's what has been posted above (if you don't post the version of SQL you are using then it will be assumed you are using a current version).

    Is it SQL 2005 you want the answer for?  Or are you working on a more recent version?

    • Marked as answer by SQL24 Thursday, September 19, 2013 8:25 AM
    Thursday, September 19, 2013 8:19 AM
  • Hi SQL24,

    You have posted a link for SQL 2005 - the process changed in SQL 2008 to use the resource monitor thread which is why that's what has been posted above (if you don't post the version of SQL you are using then it will be assumed you are using a current version).

    Is it SQL 2005 you want the answer for?  Or are you working on a more recent version?

    Thanks Mike

    I wasn't aware that this process was change after 2005 - this is where my confusion was coming in.

    I wasn't referring to any specific version when I posted - I just wanted a better understanding of the process.

    Thursday, September 19, 2013 8:24 AM