Jonathan had a client issue recently where SQL Server’s buffer pool had been forced down to a ridiculously small size, only a few hundred MB, but the OS also showed basically no free memory. Page Life Expectancy was zero! What was going on?
From investigating SQL Server’s memory usage, the memory manager showed that target and total memory were the same, at only 1.2GB, and lock pages in memory was correctly set. The next thing to check was for ballooning in VMware – a common cause of memory issues – but this wasn’t the problem either.
Next, querying sys.dm_os_sys_memory showed almost 100GB in kernel non-paged pool memory. Jonathan immediately guessed that it was a kernel driver memory leak – based on some deliberate-error-inducing coding he’d done of kernel drivers – so the next step was to see what filter drivers were on the system.
Check It Out!
You can do this using the command fltmc on any Windows machine. For instance, in a command prompt using ‘Run as administrator’ on a Windows 10 laptop, it shows:
C:\WINDOWS\system32>fltmc Filter Name Num Instances Altitude Frame ------------------------------ ------------- ------------ ----- bindflt 1 409800 0 UCPD 11 385250.5 0 WdFilter 11 328010 0 storqosflt 0 244000 0 wcifs 1 189900 0 dbx 5 186500 0 CldFlt 5 180451 0 FileCrypt 0 141100 0 luafv 1 135000 0 npsvctrig 1 46000 0 RsFx0800 1 41008.00 0 Wof 8 40700 0 FileInfo 11 40500 0
And then you can investigate each driver using the Allocated Filter Altitudes list from Microsoft (as they assign a position in the filter driver stack to each filter driver), starting with those with the highest altitudes. From there, you can prove what’s taking up memory using the PoolMon tool from the Windows Driver Development Kit (see Use PoolMon to Find a Kernel-Mode Memory Leak).
Turns out in the client’s case it was a driver from an old tool that hadn’t been de-installed – problem solved!
Bottom line: don’t have the mindset that what manifests as a problem in SQL Server is always a SQL Server problem, as sometimes the issue is environmental and SQL Server is the unwitting victim of the root cause!