10

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?

9

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.

  • 2
    I like to use Brent Ozar's table for a quick-reference on memory settings for general use cases. – LowlyDBA Jun 16 '15 at 14:00
  • 3
    That is ofcourse for quick reference and acts as a starting point for a value. I am quite sure if you ask him he would say you need to further look at perfmon counters to reach optimum value. This is why I added a SE link just in case OP wants to set max server memory. – Shanky Jun 16 '15 at 14:08
2

SQL is NUMA aware so you can find NUMA info using a query. You can also use this query to see how many NUMA nodes you have and which CPU and cores are assigned to which NUMA:

SELECT parent_node_id, scheduler_id, cpu_id
FROM sys.dm_os_schedulers WITH (NOLOCK) 
WHERE [status] = N'VISIBLE ONLINE';

Or just how many NUMA:

select COUNT(distinct Parent_node_id)
from sys.dm_os_schedulers
where [STATUS] = 'VISIBLE ONLINE'
    and Parent_node_ID < 64
-2

Yes, you should limit the max memory for SQL Server even though the server has only one purpose. Otherwise SQL Server may start using memory Windows itself needs to operate well. It is usual to leave 1GB -> 20% of the total RAM to Windows, depending on your needs.

The NUMA configuration of the server is displayed in Task Manager by selecting a process, right-click and select Set Affinity. The Windows Resource Monitor also displays the NUMA information.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.