I was working on a post about the new columns sql_memory_model, sql_memory_model_desc columns in DMV sys.dm_os_sys_info. These columns allow DBAs to programmatically identify if Lock Pages in Memory (LPIM) privilege is in effect at the service startup time.
I was setting it up, had one instance running with pages locked in memory and the other one without. Took some screenshots, then I noticed the MSSQL Tiger Team published the post
Developers choice: Programmatically identify LPIM and IFI privileges in SQL Server
So really there was no need for my post anymore. But what do I do with my beautiful screenshots?
Well not someone who likes to waste stuff, I decided to write just a couple of sentences and put the screenshots in this post and then link to the post by the MSSQL Tiger Team
Here is the query I ran to see instant file initialization is enabled, I ran this against two instances, one a SQL Server 2016 Service Pack instance, the other a SQL Server vNext instance
You can see the output in the pic below, the vNext instance does not have instant file initialization enabled, the 2016 SP1 instance does have instant file initialization enabled
To see if Lock Pages in Memory is in use, you can run the following query
You can see the output in the pic below, the vNext instance does not use Lock Pages in Memory, the 2016 SP1 instance does use Lock Pages in Memory
The sys.dm_os_sys_info DMV also has a new column that SQL Server 2016 SP1 does not have.. process_physical_affinity
To get all the details about this stuff in the pictures go read the post
Developers choice: Programmatically identify LPIM and IFI privileges in SQL Server published by the MSSQL Tiger Team