How to Limit SQL Server Database Resources in SQL Server 2014

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.

Edited for clarity and consistency:

By the database itself? No. However, you can through the connection (what you should be trying to manage anyways) called the Resource Governor.

• CPU
• MEMORY
• Physical IO, depending on the version.

There are a number of Concepts which the Docs explain in greater detail:

Resource Pool

• a group of default, internal (cannot be changed), and custom made groups that have a set value on the amount of CPU, Memory, and Physical IO that a session may consume.

Workload Group

• a class of session requests that have similar characteristics. These are mapped to The appropriate resource pool and are set at the time of connection settings. Meaning the default database connect is used not the first USE statement or any statement afterwards.

The governor must be activated Or configured. A governor specific function is run against the connection's default settings and map to a workload group.

. This workload is attached to a specific resource pool where the limitations are enforced on all subsequent sessions that occur out of the connection.

This function is the most important part and you should keep it simple.

For more reading, visit the Docs:

Main Page -Resource Governor

Resource governor Resource pool

Research governor Workload group

Resource governor classifier function | Docs Microsoft