SQL Server Express Edition Ram upgrade - suggestion.
-
HI ,
Currently setup sql server express edition 2012 R2 with O.S. installed 2008 r2 std.
I am aware about express edition database size limit is 10 GB, Constrained to a single CPU , and Maximum memory utilized by SQL Server Database Engine is 1GB .
As of now 8 GB of ram installed and customer wants to increase ram on server.
From SQL instance we have set the default allocation for memory like it's use maximum.
Sometime when work loads, memory utilization on server was 80-90% and its only used by SQL server. at that time slowness issue occurred.
So my question is if i am increase the ram on server that may be helpful on express edition. Performance will increase on server or not ?
Thanks,
Bhautik Shah -
Try to upgrade the I/O with Ssd for the Sql Server Data and Log.
You'll improve a lot the perfomance.
-
If the memory is really becoming exhausted on the server, then yes, you'd expect to see some performance increase by increasing it. Will it be much? That's very hard to say.
-
Updating to a newer version of SQL Server should help as well. Each new version gets faster and faster.
-
You already know about the memory limit, each instance won't use more than 1GB of RAM.
@iroal made a good suggestion: Move your DB, logs, OS and swap files to SSDs to gain some performance. Or buy licenses (and CALs) for SQL Server Standard Edition.
-
@thwr said in SQL Server Express Edition Ram upgrade - suggestion.:
Or buy licenses (and CALs) for SQL Server Standard Edition.
Or move to PostgreSQL
-
@scottalanmiller said in SQL Server Express Edition Ram upgrade - suggestion.:
@thwr said in SQL Server Express Edition Ram upgrade - suggestion.:
Or buy licenses (and CALs) for SQL Server Standard Edition.
Or move to PostgreSQL
Not that difficult, but could require them to update their application.
-
@thwr said in SQL Server Express Edition Ram upgrade - suggestion.:
@scottalanmiller said in SQL Server Express Edition Ram upgrade - suggestion.:
@thwr said in SQL Server Express Edition Ram upgrade - suggestion.:
Or buy licenses (and CALs) for SQL Server Standard Edition.
Or move to PostgreSQL
Not that difficult, but could require them to update their application.
Often well worth it when overrunning SQL Express.
-
HI ,
if the instance taking Maximum memory utilized by SQL Server Database Engine is 1GB then why its utilized 6 to 7 gb on actual.
-
@bhautik.shah said in SQL Server Express Edition Ram upgrade - suggestion.:
HI ,
if the instance taking Maximum memory utilized by SQL Server Database Engine is 1GB then why its utilized 6 to 7 gb on actual.
Likely a caching mechanism.
-
Bump up the memory a little, give it another GB or two and see if it uses that too. Your hypervisor isn't out of memory, right?
-
Standard practice for any server is to give exactly the memory that is needed. You start a little high and then tune down as you can. Big round numbers like 8GB are okay as starting points, but should be adjusted right away. This makes everything faster and your servers more dense. So bump this up a bit, run for a month, and tune down if the memory is not used.
-
@scottalanmiller said in SQL Server Express Edition Ram upgrade - suggestion.:
@bhautik.shah said in SQL Server Express Edition Ram upgrade - suggestion.:
HI ,
if the instance taking Maximum memory utilized by SQL Server Database Engine is 1GB then why its utilized 6 to 7 gb on actual.
Likely a caching mechanism.
Can you explain this a bit more? To the uninitiated it seems odd that if the program has a hard limit 1 GB, that some other facet of that same program could spiral to 6+ times that amount.
-
@Dashrender said in SQL Server Express Edition Ram upgrade - suggestion.:
@scottalanmiller said in SQL Server Express Edition Ram upgrade - suggestion.:
@bhautik.shah said in SQL Server Express Edition Ram upgrade - suggestion.:
HI ,
if the instance taking Maximum memory utilized by SQL Server Database Engine is 1GB then why its utilized 6 to 7 gb on actual.
Likely a caching mechanism.
Can you explain this a bit more? To the uninitiated it seems odd that if the program has a hard limit 1 GB, that some other facet of that same program could spiral to 6+ times that amount.
No facet of that program. Just the operating system components doing their normal caching. SQL Server does not further cripple the OS underneath.
-
OK - I'll go down the rabbit hole.
Educational request time.
Let's assume we have a Windows 2008 R2 Server running SQL Express 2012 R2 and nothing else. The application that accesses the DB is remote.
Where does the above mentioned caching come in?
-
@Dashrender said in SQL Server Express Edition Ram upgrade - suggestion.:
Where does the above mentioned caching come in?
The two biggest places are disk caches and application caches. For example, does all of SQL Server get moved into memory or only part of it? Does all of the database get moved into memory, or only part of it? (That last part is the part that gets capped at 1GB.) Do all of the OS components get loaded into memory, or loaded from disk as needed? Do the log files stay in memory?
