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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Clearing adhoc plans from the memory...

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

Rate Topic

Display Mode

Topic Options

Author

Message

latheesh.jaya

Posted Tuesday, December 14, 2010 6:00 PM

SSC 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

latheesh.jaya

Posted Tuesday, December 14, 2010 8:53 PM

SSC 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

Bhuvnesh

Posted Tuesday, December 14, 2010 10:42 PM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1034888

sqlzealot-81

Posted Tuesday, December 14, 2010 11:36 PM

Right there with Babe

Group: General Forum Members
Last Login: Today @ 4:44 AM
Points: 730, Visits: 1,703

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

sqlzealot-81

Posted Wednesday, December 15, 2010 5:51 AM

Right there with Babe

Group: General Forum Members
Last Login: Today @ 4:44 AM
Points: 730, Visits: 1,703

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 »

Permissions