Sql-server – Configure RAM for SQL Server and NUMA

memorynumasql serversql-server-2012

Accidental DB admin here.

Question:

Would you still manually limit max RAM for SQL Server even though the sole purpose of that server is to serve SQL Server database engine only?

I have Microsoft Windows 2012 R2 Server, SQL Server 2012 Std, with 32GB RAM.

Admin constantly login to the SQL Server to use its console and all.

Secondly, how do you check if this Windows server is NUMA enabled?

Best Answer

Would you still manually limit max RAM for MSSQL eventhough the sole purpose of that server is to serve MSSQL only?

That is bit debatable topic considering you have SQL Server 2012 here. If you would have asked same question about SQL Server 2005/2008, which was on Windows Server 2003/2008, I would strongly suggest to set a max server memory limit due to bugs which were there in Windows operating system(2003/2008). But memory configuration and OS performance changed significantly from SQL Server 2012 onwards and Window Server 2008/2012 r2 onward respectively, if you ask my opinion, You can leave max server memory configuration to default if you have enough RAM on system and you are just using database engine features. If you ask MS engineers about this they would also say that SQL Server is coded well to manage memory by itself and if you have system dedicated just to SQL database engine you can leave SQL Server max server memory to default SQL Server will manage it efficiently.

Anyways if you still want to configure you can read Similar question and answers given On this SE Thread

There are situation where max server memory is required I have outlined that in This SE thread

Secondly, how do you check if this Windows server is NUMA enabled?

A little search would lead you to this Blogs.msdn article. It has various pictorial description when SQL Server is NUMA aware and is using it.

Related Solutions

SQL Server 2012 RAM Allocation Issues – How to Safely Allocate More Memory

I'd check the perfmon metrics for per-cpu CPU busy, as well as the perfmon metrics instance-wide and per NUMA node for PLE, database pages, etc. Two recent SQL Server KBs

However, for some workloads, especially with higher core count, high concurrent query count, and lots of database disk IO, using trace flags 8015 (disable NUMA support at SQL Server level) and 8048 (remove per-socket query memory allocation botlleneck) will provide even better results than the fixes contained in SQL Server 2012 SP1 CU4. (I confirmed this on our test equipment with simulations of our workload - YMMV)

Trace flag 8015 deserves thorough evaluation before deployment. Memory affinity and attending lower memory latency is sacrificed for managing a single large bpool. It also results in one lazy writer instead of one per NUMA node, and the idea of affinitizing a connection endpoint within a single SQL Server instance to each NUMA node also disappears. But for some workloads the benefits are undeniable. Don't use 8015 without 8048.

I've never seen or heard of any measurable cost to adding trace flag 8048, and if CMEMTHREAD waits and associated spinlocks are triggered by query memory allocation - its the only reliable way to eliminate them.

Sql-server – SQL Server Service Account login or password is not valid error

The issue isn't with the account you are attempting to use, but your account. If you look in the setup log file, which is created during the installation of the SQL Server application, you will see the details of the issue, with technical jargon that will indicate the issue is probably a permission issue with your account having permission to perform a check on the service account credentials:

Slp: Sco: Attempting to check if container ‘WinNT://DOMAIN’ of user account exists
Slp: UserSecurity.ValidateCredentials — Exception caught and ignored, exception is Access is denied.
Slp: UserSecurity.ValidateCredentials — user validation failed

Rebooting usually fixes this issue, as does changing the service account value after installation is complete. A minor issue that sometimes appears.

Best Answer

Related Solutions

SQL Server 2012 RAM Allocation Issues – How to Safely Allocate More Memory

Sql-server – SQL Server Service Account login or password is not valid error

Related Question