June 14, 2012 at 12:16 am
I have been Asked several times in an Interview what would be the maximum amount of memory that a SQL SERVER CAN USE.
The Interviewer has Provided Multiple Scenerios like as Follows.Please reply with Better understanding for the same
Also In case any Configuration Changes required to enhance the usage of memory
----
32 Bit OS
32 Bit SQL Server
Maximum Memroy SQL SERVER can use??
----------
32 Bit OS
64 Bit SQL Server
Maximum Memroy SQL SERVER can use??
----
64 Bit OS
64 Bit SQL Server
Maximum Memroy SQL SERVER can use??
Thx in Advance.
June 14, 2012 at 12:32 am
June 14, 2012 at 6:47 am
Just to narrow it down for you a bit, memory management is primarily predicated on the OS, not SQL Server. So a 32-bit OS with a 64-bit SQL server, the limitation is going to be at the OS level. Other than that, the limits are very well documented in the Books Online as was already pointed out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 14, 2012 at 8:19 am
Just to be pedantic...
A 32 bit OS cannot run a 64-bit SQL Server.
A 64-bit OS can run 32-bit code, but not the other way around.
For memory details, books Online or chapter 4 of this: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
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
June 14, 2012 at 8:38 am
GilaMonster (6/14/2012)
Just to be pedantic...A 32 bit OS cannot run a 64-bit SQL Server.
A 64-bit OS can run 32-bit code, but not the other way around.
For memory details, books Online or chapter 4 of this: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
Not pedantic. I wasn't clear.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 14, 2012 at 7:04 pm
Jai-SQL DBA (6/14/2012)
I have been Asked several times in an Interview what would be the maximum amount of memory that a SQL SERVER CAN USE.The Interviewer has Provided Multiple Scenerios like as Follows.Please reply with Better understanding for the same
Also In case any Configuration Changes required to enhance the usage of memory
----
32 Bit OS
32 Bit SQL Server
Maximum Memroy SQL SERVER can use??
Generally, I would say the max memory setting would be 80% for SQL Server and you should reserve 20% to the OS...otherwise, once SQL server uses more memory, it won't release it and that cause the OS with no memory and the system can crash.
----------
32 Bit OS
64 Bit SQL Server
Maximum Memroy SQL SERVER can use??
As Gail mentioned, you cannot install a 64bit SQL server into a 32bit OS. It won't allow you.
Also, if you install a 32bit SQL server into 64bit OS, then you are not utilziing the 64bit memory....as the 32bit can acquire only max of 4GB of memory. So, in this scenario, you can enable AWE(Address Windowing Extenions) and access more than 4GB of memory.
----
64 Bit OS
64 Bit SQL Server
Maximum Memroy SQL SERVER can use??
I guess, you can use the same example as I mentioned in the above for 32 Bit OS
32 Bit SQL Server.
Thx in Advance.
To the Masters,
Please correct me if I have mentioned anything incorrectly.
Thanks,
TA
Regards,
SQLisAwe5oMe.
June 14, 2012 at 8:38 pm
You may want to look up the max value in the Books Online. They're pretty different between 32 & 64 bit OS.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 15, 2012 at 3:01 am
SQLCrazyCertified (6/14/2012)
Please correct me if I have mentioned anything incorrectly.
Why do you specifically call out AWE on 32 bit SQL on 64 bit OS, but make no mention of it on 32 bit SQL with 32 bit OS?
And where did that 80%/20% 'rule' come from?
Also the point 'once SQL server uses more memory, it won't release it' is incorrect.
The maximum are very different between 32 bit SQL and 64 bit SQL.
You may want to read through chapter 4 of this: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
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
June 15, 2012 at 5:08 pm
GilaMonster (6/15/2012)
SQLCrazyCertified (6/14/2012)
Please correct me if I have mentioned anything incorrectly.Why do you specifically call out AWE on 32 bit SQL on 64 bit OS, but make no mention of it on 32 bit SQL with 32 bit OS?
Why would you need to enable AWE if both SQL & OS are 32bit?
And where did that 80%/20% 'rule' come from?
Setting min and max memory is certainly a best practice, but what you set it at depends on what else is running on your server. The example 80%/20%, from my understanding the max memory setting is that you want to restrict how much SQL uses max memory.....so, for an example....lets say you have 1 sql instance only....and you have 10GB of memory, how would you allocate the memory?.....so, I would set the max memory as 8GB(80%) and leave the 2GB(20%) for OS...and if you don't set the max memory there is a chance the system will crash if OS left with no memory.. I was thinking of that example when I mentioned 80/20.
Also the point 'once SQL server uses more memory, it won't release it' is incorrect.
If you don't set the max memory what would happen? I have read that once SQL uses some memory for some process, it will not release it right away.
The maximum are very different between 32 bit SQL and 64 bit SQL.
You may want to read through chapter 4 of this: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
Regards,
SQLisAwe5oMe.
June 16, 2012 at 3:51 am
SQLCrazyCertified (6/15/2012)
GilaMonster (6/15/2012)
SQLCrazyCertified (6/14/2012)
Please correct me if I have mentioned anything incorrectly.Why do you specifically call out AWE on 32 bit SQL on 64 bit OS, but make no mention of it on 32 bit SQL with 32 bit OS?
Why would you need to enable AWE if both SQL & OS are 32bit?
Because of the 32-bit memory limitation.. Please see the chapter I referenced
lets say you have 1 sql instance only....and you have 10GB of memory, how would you allocate the memory?.....so, I would set the max memory as 8GB(80%) and leave the 2GB(20%) for OS....
In that case, I probably would as well, but the general rule is not 80/20. Please see the chapter I referenced for a fairly conservative initial setting for max server memory.
If you don't set the max memory what would happen? I have read that once SQL uses some memory for some process, it will not release it right away.
Not releasing it right away is not the same as not releasing it at all. SQL will release memory if the OS requires it (well, unless its memory settings prevent it)
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply