Hey there, let's work through your SQL Server memory issue step by step—first, that Task Manager percentage is probably misleading, so let's not jump to "memory leak" right away.

Task Manager's memory reporting for SQL Server is often tricky, especially on 64-bit systems with Lock Pages in Memory (LPIM) enabled (super common for production setups). Here's why:

• The max server memory setting only controls the SQL Server buffer pool and some related memory consumers—it doesn't cap all memory SQL Server uses. Things like CLR assemblies, full-text search, extended stored procedures, or third-party add-ins can eat up memory outside this limit.
• If LPIM is on, Task Manager counts locked memory pages that aren't part of the buffer pool you limited to 16GB. That's why your "16GB used" number is correct for the buffer pool, but the percentage looks way higher.

To confirm if it's actually a leak, run these DMV queries to get a precise breakdown:

-- See which SQL Server components are using the most memory
SELECT 
    type AS Memory_Clerk_Type,
    SUM(pages_kb)/1024 AS Total_MB_Used
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY Total_MB_Used DESC;

-- Find rogue sessions holding excessive memory
SELECT 
    s.session_id,
    s.login_name,
    s.program_name,
    SUM(ec.memory_usage)*8/1024 AS Session_MB_Used,
    t.text AS Query_Text
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections ec ON s.session_id = ec.session_id
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) t
GROUP BY s.session_id, s.login_name, s.program_name, t.text
ORDER BY Session_MB_Used DESC;

If you see a clerk (like MEMORYCLERK_SQLCLR or MEMORYCLERK_FULLTEXT) growing indefinitely without releasing memory, or a single session hoarding gigabytes over time, that's a leak red flag. Otherwise, it's just unaccounted-for memory outside your buffer pool limit.

If you need immediate relief without rebooting the entire server, try these options (note: some may temporarily hit query performance):

• Flush unused system cache: Run DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;. This tells SQL Server to dump cached memory that's not actively being used. It will flush query plans, so your first few queries after might be slower until plans are rebuilt—avoid during peak hours if possible.
• Clear clean buffer pool pages (last resort): Use DBCC DROPCLEANBUFFERS; to empty the buffer pool of unused data pages. This forces SQL Server to reload data from disk for subsequent queries, so only use this if you're sure the buffer pool is bloated with unnecessary data.
• Kill rogue sessions: If the DMV query above shows a specific session using way too much memory, terminate it with KILL <session_id>;—just double-check it's not a critical business query first!
• Temporarily adjust max server memory: If non-buffer pool memory is eating up space, you can bump up max server memory a bit (say to 20GB) to give the buffer pool more room. This is a band-aid, though—you still need to find the root cause.

To fix this long-term, tackle these steps:

1. Fix your max server memory setting: For a 32GB server, 16GB is way too low. You want to leave 8-10GB for the OS and other critical processes, so set max server memory to 22-24GB as a starting point. Update it with:

   sp_configure 'show advanced options', 1;
   RECONFIGURE;
   sp_configure 'max server memory (MB)', 24576; -- 24GB
   RECONFIGURE;
   

2. Verify actual SQL Server memory usage: Use this DMV to get the real numbers, ignoring Task Manager's confusing percentage:

   SELECT 
       physical_memory_in_use_kb/1024 AS SQL_Total_Physical_MB,
       locked_page_allocations_kb/1024 AS Locked_Pages_MB
   FROM sys.dm_os_process_memory;
   

   SQL_Total_Physical_MB should be close to your max server memory plus a small overhead. If Locked_Pages_MB is massive, confirm you actually need LPIM (it prevents page file swapping, but can mess with Task Manager's reporting).
3. Update SQL Server: Your version (11.0.6020.0) is SQL Server 2012 SP3. There are multiple cumulative updates (CUs) released after this that fix known memory leak bugs. Install the latest CU for SP3 to patch any underlying issues causing uncontrolled memory growth.
4. Audit rogue components:
   • If CLR memory is high, check your custom CLR assemblies—make sure they're properly disposing of objects to avoid leaks.
   • If full-text search is using too much memory, see if indexes are being rebuilt unnecessarily, or if there's a bug in the full-text engine (updating SQL Server might fix this).
   • Disable any third-party extensions or stored procedures you don't need—these often run outside the buffer pool and can leak memory.
5. Set up monitoring: Track memory usage over time using DMVs or SQL Server Management Studio's Activity Monitor. This will help you catch trends before they turn into server slowdowns or timeouts.

内容的提问来源于stack exchange，提问作者Sunny