Clearing adhoc plans from the memory CACHESTORE will be useful for the data buffer memory

  • Clearing adhoc plans from the memory CACHESTORE will be useful for the data buffer memory

  • Any thoughts would be deeply appreciated.

    For clarity, Would it be beneficial for data buffer caching if we could avoid the adhoc plans from the cache memory in terms of memory space?

  • Are you observing any memory pressure ?

    because Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Hi Bhuvanesh,

    Thanks for attending the question. I do not have any memory pressure in my server as the server has 120GB ram. However I can see 60 GB allocated for adhoc and 6 GB for objects like procs, tirggers and function(i mean for obj).

    There is no need for going adhoc queries for almost all cases.So right now am doing the adhoc plans to prepared proc. Out of the box, my thoughts is that if i can free the space allocated for adhoc, would i be beneficial for the data caching? Or, Whats the memory relation with Data caching buffer pool and cache pool?

  • I read the following statement out from my googling...

    "The regions within the memory pool are also highly dynamic. The SQL Server code constantly adjusts the amounts of the memory pool assigned to the various areas to optimize performance."

    Experts, Please confirm.

Viewing 5 posts - 1 through 4 (of 4 total)

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