• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

Sql Server Database memory management

Hi All,

We have a .Net based web application.
Each client of ours has a different database.
All these databases reside on the same server.
Eventhough tables and views are same in each database, some have more transactions than others. Some of these databases are bigger than others.
We are using Sql Server 2008.
My questions:

1)Can i control memory assigned for each database?
I want to assign more memory to databases that have more transactions and less to ones that have less number of transactions.

2)If yes, is this a good practice?


Thanks in Advance.

0
shahjagat
Asked:
shahjagat
2 Solutions
 
Scott PletcherSenior DBACommented:
1) No.  SQL Server itself controls that.
0
 
lcohanDatabase AnalystCommented:
Optimizing Server Performance Using Memory Configuration Options: http://msdn.microsoft.com/en-us/library/ms177455.aspx

How to determine proper SQL Server configuration settings: http://support.microsoft.com/kb/319942/EN-US/

According to Microsoft, for 32-bit editions of SQL Server, it is important to make sure that the /3GB and /PAE switches are set in accordance with the amount of physical memory in the system startup as follow:

• If you have 3-4 GB memory, include the /3GB switch in the startup.
• If you have 4-8GB memory, use /3GB and /PAE
• If you have 16 GB or more, use only /PAE, as /3GB will cripple memory over 8GB.

• When you have set /PAE, go into SQL Server's configuration and set the option to use AWE to ON. If you do this, however, you need to also specify a maximum memory value in SQL Server, if you do not, then SQL Server will take all but 128MB of the computer's memory if the automatic memory management is used in SQL Server.

• The user who runs the SQL Server needs to have the 'lock pages in memory' user right in the local security policy, or it will have problems allocating the memory for SQL Backup's extended stored procedure. If you have checked everything above, please check this as well.
0
 
AnujCommented:
1. If you are on SQL Server 2008 Enterprise Edition, you can use Resource Governor to manage your workloads. Resource Governor can be used to limit the CPU, memory consumption based on the application.  An example is, suppose you have two database in same server and one is OLTP and Other is a reporting database and, your most of the users are connected to critical OLTP database, imagine only top management uses the reporting database and the users are few and used rarely . In this case you can distribute your CPU and memory across the application, i.e You can limit the CPU and Memory of your reporting Application to 20% and the rest is assigned to the OLTP application.

2)If yes, is this a good practice?
This is a good practice when you need to control the resource allocation for your applications or users. Like in a situation where your server have multiple work loads with one using heavy resource but rarely used (reporting DB like as mentioned in the example) and other workload using the server more frequently(like business).
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<1)Can i control memory assigned for each database?>>
No.  At least not directly.  You may however use the resource governor to limit the memory quota to a specific login, hence to a specific application.

<<2)If yes, is this a good practice?>>
See above.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Note that a very effective way to control RAM consumption is to promote plan cache reuse using stored procedures instead of other call types.
0
 
shahjagatAuthor Commented:
Gave points to anujnb as his answer was the first one.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now