Memory management in SQL Server

Question

Memory management in SQL Server

shovankar

Say Hey Kid

Points: 694
More actions
January 31, 2012 at 12:36 pm

#263577

Hi All,
I was going through a document for SAP MAXDB where I found that the MAXDB system uses a technology named LiveCache- through which all application data and objects are located in the main memory i.e. the RAM.This in turn speeds up the performance of the system.
I was wondering if such kind of memory management can be achieved in SQL Server as well. Atleast if there is a way through which SQL server objects like tables can be allocated fixed space in the main memory or not.
Need your inputs on this.
Regards,
Shovan.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

You just have to give SQL Server enough memory to hold the entire database in memory, and it will take care of the rest.

RishiKapoor Old Hand Points: 396 More actions · Answer 2

You can restrict SQL Server to use maximum amount of memory of a server by using sp_configure option.

Memory is allocated to the objects dynamically by the SQL Server therefore you cannot assign memory to the objects yourself.

Suresh B. SSC-Insane Points: 22986 More actions · Answer 3

shovankar (1/31/2012)
... data and objects are located in the main memory...
I was wondering if such kind of memory management can be achieved in SQL Server...

Are are you saying this concept is not there in SQL Server?

If yes, for your information, this concept is already there in SQL Server.

SQL Server keeps the read data pages and executed query plans in the memory.

You don't have to do anything to achieve this.

Suresh B. SSC-Insane Points: 22986 More actions · Answer 4

Are you using SQL Server Express edition?

Only in SQL Server Express edition, AutoClose is ON by default.

When the last user exits, the database is closed cleanly (data and objects would be removed from RAM) automatically.

shovankar Say Hey Kid Points: 694 More actions · Answer 5

Hi Suresh,

Thanks a lot for your reply. I am using SQL SERVER 2008 ENTERPRISE edition with 32 GB ram. I was just wondering if any kind of programming can be done from the database end or if there are any settings through which I can have the objects (tables/view) alocate in the memory,that are being frequently used by the application. If this can be achieved then the performance will be much higher.

I know that SQL Server uses daynamic memory management, but stii if this can be achieved.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

shovankar (2/3/2012)
Thanks a lot for your reply. I am using SQL SERVER 2008 ENTERPRISE edition with 32 GB ram. I was just wondering if any kind of programming can be done from the database end or if there are any settings through which I can have the objects (tables/view) alocate in the memory,that are being frequently used by the application.

There is not.

If this can be achieved then the performance will be much higher.

Probably not. SQL caches data based on usage, so so objects that are frequently used by the application will remain in memory anyway.

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 7

shovankar (2/3/2012)
Hi Suresh,
Thanks a lot for your reply. I am using SQL SERVER 2008 ENTERPRISE edition with 32 GB ram. I was just wondering if any kind of programming can be done from the database end or if there are any settings through which I can have the objects (tables/view) alocate in the memory,that are being frequently used by the application. If this can be achieved then the performance will be much higher.
I know that SQL Server uses daynamic memory management, but stii if this can be achieved.

@shovankar

There will a dbcc command to put the object to the memory. But

"it's deprecated, but in SQL Server 2005 it actually does NOTHING at all."

http://www.sqlskills.com/BLOGS/PAUL/post/DBCC-PINTABLE.aspx

P.S SQL always keep the data in memory which is used often. Therefore you no need to put that to memory.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Suresh B. SSC-Insane Points: 22986 More actions · Answer 8

Run PerfMon and monitor your server. If the Buffer Catche Hit Ratio is higher, your application is getting the data from memory.

If it is lower, you have to take steps to improve it.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

The buffer cache hit ration is another of those near-useless counters. If it's high, you may be fine or you may have a severe memory problem. If it's low, you probably have severe problems already

http://www.simple-talk.com/sql/database-administration/great-sql-server-debates-buffer-cache-hit-ratio/

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

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 10

I've not actually used it before, but have read some articles on using the Resource Governor - could you not try using that to restrict memory usage/cpu/etc by application?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience