Query Plan Memory

• Sachin Nandanwar

  SSCertifiable

  Points: 7699

  More actions

  February 28, 2011 at 5:56 am

  #235242

  Hi,

  I was having a discussion with one of my colleague regarding the buffer pool.He seems to be suggesting that the query plan is a part of buffer pool while I was of the view that query plan is a part of the non buffer pool(MemoryToLeave area).

  Just not sure who is correct...Hopefully its me...;-)

  --------------------------------------------------------------------------------------------------
  I am just an another naive wannabe DBA trying to learn SQL Server

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  February 28, 2011 at 10:46 am

  #1292445

  The plan cache is part of the buffer pool sharing with the data cache and a few other things. The mem-to-leave (non-paged memory) is for memory allocations not in 8k chunks. Backup buffers, thread stacks, CLR, in-process linked servers, couple other things.

  See http://msdn.microsoft.com/en-us/library/ee343986%28v=sql.100%29.aspx

  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

• Sachin Nandanwar

  SSCertifiable

  Points: 7699

  More actions

  March 1, 2011 at 2:32 am

  #1292768

  Thanks for the feedback.

  Just one confusion here. Are only those query plans cached whose data pages are present in the buffer pool ?Because if query plans whose data pages are not a part of the buffer pool are cached then wouldn't that increase the chances of memory pressure ?

  I havent yet read the link you posted.So not sure whether the above question has already answered in the link.

  --------------------------------------------------------------------------------------------------
  I am just an another naive wannabe DBA trying to learn SQL Server

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  March 1, 2011 at 4:14 pm

  #1293278

  Data cache and plan cache are not synchronised. Clearing one does not clear the other, removing pages from one does not remove pages from the other.

  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