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.
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.
Scott Pletcher
1) No. SQL Server itself controls that.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Note that a very effective way to control RAM consumption is to promote plan cache reuse using stored procedures instead of other call types.
ASKER
Gave points to anujnb as his answer was the first one.