<div>
disclaimer: &nbsp;I am not a DBA<br />
that being said, yes SQL will utilize all available memory on a system unless your DBA sets it differently. &nbsp;<br />
the easiest way to look at memory on a sql box is open up taskmgr and go to performance mgr.<br />
then pull up the sql process and see what the % of memory is for that process. &nbsp;then break open calculator and do the math. &nbsp;total memory * percent of memory used by sql.<br />
<br />
a DBA could tell you a better way but this is the quick and dirty way.
</div>


<div>
Here is MS DOC that will help you set it to the optimal level. <br />
<br />
<a href="https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-2017" rel="ugc">https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-2017</a>
</div>


finance_teacher

<div>
SQL is a memory consumer that will consume as much as available, how big is your databases?<br />
You can configure SQL to limit how much it can consume.<br />
<br />
The more of the DB it can keep in memory, the faster the SQL response.<br />
<br />
Other than the 100 what issues do you see in terms of perfomance that needs addressing.
</div>


<div>
Let me be clear for you.<br />
<br />
If this is a dedicated SQL instance..set your SQL memory to 80% of the sql.<br />
<br />
There are more discreet formulas that take cpu into consideration but 80% for dedicated SQL is still good formula.<br />
<br />
MAX memory is never recommended.<br />
<br />
You do not want SQL Server eating your windows memory for OS operations or other apps running on server.<br />
<br />
If you are not a dedicated SQL Server then reduce the 80% down accordingly but you will need to decide how much RAM your system needs to have maximum for a set of processes.
</div>


<div>
You need to use the 'max memory' setting to restrict the amount of RAM that SQL uses. &nbsp;Starting at 80% of total RAM is a decent-enough rough guideline.<br />
<br />
EXEC sys.sp_configure 'max server memory', &lt;memory_size_<b>in_mb</b>&gt;<br />
<br />
For example, to limit SQL to, say, 40GB, you'd use 40960.
</div>


<div>
2. If not, how can I get TASK MANAGER to show me the current MEMORY Microsoft SQL Server 2016 is using ?<br />
<br />
<br />
This would be useful to determine your processors but as I stated earlier if this is dedicated SQL Server use 80%.<br />
<br />
So 16gb...I would leave 4 for operating system and use the other 12 as max memory on sql.<br />
<br />
If this is not set...then I also imagine you need to setup the Cost threshold of Parallism and the Max Degree Of parallism. &nbsp;<br />
<br />
Auto shrink = true<br />
And autostart = false<br />
<br />
<br />
These are all common settings missed on a new sql instance that will affect performance.
</div>


<div>
To be clear, the correct setting for autoshrink is off/false, and for autoclose it's off [and for autostart it's on, not usually an issue].<br />
<br />
You can only get accurate SQL memory usage from SQL itself: Windows and related tasks don't really know how SQL is using memory. &nbsp;For example:<br />
<br />
SELECT type AS clerk_type,<br />
&nbsp; &nbsp; CAST(SUM(pages_kb) / 1024.0 AS decimal(9, 1)) AS size_mb<br />
FROM sys.dm_os_memory_clerks WITH (NOLOCK)<br />
GROUP BY type<br />
HAVING SUM(pages_kb) / 1024.0 &gt;= 1.0<br />
ORDER BY size_mb DESC, clerk_type
</div>


<div>
@Scott...thanks for clarifying my comment. &nbsp;Its always good to check
</div>


<div>
<div class="content wysiwyg-content">
Someone told me Microsoft SQL Server 2016 is setup to use &quot;All available memory&quot;, therefore my Microsoft SQL Server's Windows Server 2016 TASK MANAGER will always show 100% memory use of the 128GB I have.<br />
<br />
1. Is that true ?<br />
2. If not, how can I get TASK MANAGER to show me the current MEMORY Microsoft SQL Server 2016 is using ?
</div>
</div>


Someone told me Microsoft SQL Server 2016 is setup to use "All available memory", therefore my Microsoft SQL Server's Windows Server 2016 TASK MANAGER will always show 100% memory use of the 128GB I have.

1. Is that true ?
2. If not, how can I get TASK MANAGER to show me the current MEMORY Microsoft SQL Server 2016 is using ?

Microsoft SQL Server 2016 -- MEMORY usage at 100% ?

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

This topic area includes legacy versions of Windows prior to Windows 2000: Windows 3/3.1, Windows 95 and Windows 98, plus any other Windows-related versions including Windows Mobile.

Windows OS

Windows 10 is a personal computer operating system featuring the "universal application architecture" (UAP); apps can be designed to run across multiple devices with nearly identical code, including PCs, tablets, smartphones, embedded systems, Xbox One, Surface Hub and HoloLens. Windows 10 also includes a virtual desktop system, a window and desktop management feature called Task View, the Microsoft Edge web browser, support for fingerprint and face recognition login, voice-based search (Cortana), new security features for enterprise environments, and DirectX 12 and WDDM 2.0 to improve the operating system's graphics capabilities for games.

Windows 10

Microsoft Azure is a cloud computing platform and infrastructure for building, deploying and managing applications and services through datacenters. It provides both platform-as-a-service (PaaS) and infrastructure-as-a-service (IaaS) services and supports many different programming languages, tools and frameworks, including both Microsoft-specific and third-party software and systems. Cloud Services is a PaaS environment and can be used to create scalable applications and services; there are specific software development kits (SDKs) provided by Microsoft for Python, Java, Node.js and .NET. Azure also has file and storage services, data management, analytics and DNS services.

Azure

Windows Server 2016 is the successor to Windows Server 2012 R2. Built upon the same core code as Windows 10, Windows Server 2016 brings enhancements in security, servicing, and connectivity. A particular focus on this release was hybrid-cloud scenarios, and has close ties to Azure and other Microsoft cloud initiatives. This does not detract from the many improvements that are available for on-premises-only deployments

Windows Server 2016 comes in Datacenter, Standard, and Essentials editions, and for servicing, has adopted windows 10's cumulative model. The new nano-server install is designed to be remotely managed and is designed to be kept current through continuous feature updates. The full GUI install operates similarly to windows 10's "Long Term Servicing Branch" (LTSB) model with cumulative security updates.

Windows Server 2016 has also shifted from a per-processor-and-CAL licensing model to a per-core-and CAL licensing model. This brings Windows Server's licensing more in line with Microsoft's other products and makes hybrid-cloud license planning easier as well.

Windows Server 2016

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.