Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Clearing adhoc plans from the memory CACHESTORE will be useful for the data buffer memory Expand / Collapse
Author
Message
Posted Tuesday, December 14, 2010 6:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 14, 2010 8:50 PM
Points: 34, Visits: 172
Clearing adhoc plans from the memory CACHESTORE will be useful for the data buffer memory
Post #1034845
Posted Tuesday, December 14, 2010 8:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 14, 2010 8:50 PM
Points: 34, Visits: 172
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?
Post #1034870
Posted Tuesday, December 14, 2010 10:42 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:16 AM
Points: 2,840, Visits: 3,983
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
Post #1034888
Posted Tuesday, December 14, 2010 11:36 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 3:47 AM
Points: 731, Visits: 2,042
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?

Post #1034903
Posted Wednesday, December 15, 2010 5:51 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 3:47 AM
Points: 731, Visits: 2,042
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.
Post #1035060
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse