Sql Server Database memory management

Posted on 2011-10-26
Last Modified: 2012-06-27
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.

Question by:shahjagat
    LVL 68

    Expert Comment

    1) No.  SQL Server itself controls that.
    LVL 39

    Expert Comment

    Optimizing Server Performance Using Memory Configuration Options:

    How to determine proper SQL Server configuration settings:

    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.
    LVL 15

    Accepted Solution

    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).
    LVL 23

    Assisted Solution

    by:Racim BOUDJAKDJI
    <<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.
    LVL 23

    Expert Comment

    by:Racim BOUDJAKDJI
    Note that a very effective way to control RAM consumption is to promote plan cache reuse using stored procedures instead of other call types.

    Author Closing Comment

    Gave points to anujnb as his answer was the first one.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now