January 2, 2014 at 9:20 am
Hi there,
I have a query on how SQL allocates memory across separate database instances.
I have 36GB RAM allocated to the whole server instances. I have created 5 separate database instances within the one physical instance. All 5 database instances are being used by the users.
Does SQL share the available memory across all the database instances equally or does SQL manage the memory at a server level and ignores the separate database instances?
Thanks, Russell.
--------------------------------------------
Laughing in the face of contention...
January 2, 2014 at 9:38 am
Russell
All five instances will be fighting for that 36GB. I would advise you to set max server memory on each instance, to stop that from happening.
Edit - when you say database instances, do you mean databases? You might want to look at Resource Governor in order to stop any particular process consuming too much memory.
John
January 2, 2014 at 9:50 am
arrjay (1/2/2014)
I have created 5 separate database instances within the one physical instance.
Can you clarify what you mean by 'instance'?
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
January 2, 2014 at 10:06 am
Hi there,
In terms of the database instances, I have installed SQL 5 times on the same server. This might not be called a database instance but I have 5 SQL services running on one machine.
http://technet.microsoft.com/en-us/library/ms143694.aspx
With this in mind, how would memory be shared/allocated?
Thanks!
--------------------------------------------
Laughing in the face of contention...
January 2, 2014 at 11:32 am
arrjay (1/2/2014)
In terms of the database instances, I have installed SQL 5 times on the same server. This might not be called a database instance but I have 5 SQL services running on one machine.
5 instances on one server. Each instance with an unspecified number of databases.
The instances will all try to allocate the full 36 GB, unless you've set max server memory to something other than the default, and will fight each other for the memory. They're independent of each other, it's just like 5 of any other application on a server.
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
January 2, 2014 at 4:43 pm
On a 36 GB server with 5 instances, I would set the MAX memory setting on each instance so that the total of all 5 was 32 GB. The optimum amount to allocate to each instance will depend on the database size, query load and number of connections, but in general I would not allocate more than the total size of the user databases on an instance or less than 2GB.
Set up Performance Monitor or other monitoring tool to moniter Page Life Expetancy for each instance, do some testing with end users and adjust as needed to give more to the instances than need it and less to the instances that are less stressed.
January 3, 2014 at 3:04 am
Thanks for the response guys.
The instances contain the exact same number of databases and almost exactly the same information.
The different database instances are used to test against different versions of our application.
I think I will test the database instances running an allocated memory threshold and track page life expectancy. If this turns out more fruitful than having all database instances using the MAX memory setting then great.
I guess the reason for posting this in the first place is that I heard SQL will allocate all the memory to itself (allowing a small amount for OS and other applications), if there are 5 SQL database instances, each allocating the full memory to itself then one would assume we would end up with a contention or inefficient processing issue. I guess this is managed but whatever memory is left in the buffer pool at run time.
Thanks again!
--------------------------------------------
Laughing in the face of contention...
January 3, 2014 at 3:16 am
What do you mean by "allocated memory threshold"? Is it an alert that's generated when a particular process uses more memory than the threshold you set? If so, I would advised using max server memory on each instance to prevent such an alert ever needing to be generated. If you don't, you risk data pages being swapped in and out of cache more frequently than is desirable, resulting in increased IO and CPU usage, possibly resulting in impaired performance.
John
January 3, 2014 at 3:21 am
arrjay (1/3/2014)
I guess the reason for posting this in the first place is that I heard SQL will allocate all the memory to itself (allowing a small amount for OS and other applications)
Yes, other than SQL doesn't reserve memory for OS and other apps. Unless it is told otherwise, it will try to allocate all memory. Once it allocates too much, Windows will set a low mem warning and SQL will back off and release some memory, usually. This is why setting max server memory is strongly recommended, even with one instance of SQL on the server.
If there are 5 SQL database instances, each allocating the full memory to itself then one would assume we would end up with a contention or inefficient processing issue.
Yes, you will. You'll have 5 instances all fighting for the same memory, all allocating and releasing memory all the time in response to Windows' memory notifications
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
January 3, 2014 at 3:22 am
Hi John - I was going to allocate 7.2 gig (36 gig / 5 instances) to each database instance as thought this would be the most efficient way to manage my resources. Are you saying the most efficient way would be to keep all database instances using MAX memory because there will be less swapping of pages within the memory cache?
--------------------------------------------
Laughing in the face of contention...
January 3, 2014 at 3:25 am
arrjay (1/2/2014)
All 5 database instances are being used by the users.
sounds like you're hitting this from an Oracle terminology point, theyre different entirely.
Each instance will default to attain as much memory as the server contains. As already specified you would be well advised to set the max memory limits on each of the SQL Server instances. Remember to leave some memory free for the OS
arrjay (1/2/2014)
Does SQL share the available memory across all the database instances equally
No it's a "free for all", see above
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 3, 2014 at 3:26 am
arrjay (1/3/2014)
Hi John - I was going to allocate 7.2 gig (36 gig / 5 instances) to each database instance as thought this would be the most efficient way to manage my resources.
7.2 is too high, it leaves no memory for the OS.
On a 36 GB server, about 6-8GB should be reserved for the OS. If we say 6GB, then that leaves 30GB to be split between the SQL instances, so 6 GB each. Monitor Available MB, if it's under a few hundred, reduce max server memory on one or more instance.
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply