I have learned over the years that some people really like it when I am verbose, long-winded, redundant and carry an otherwise small sentence on forever until you wonder to yourself, “Really, what it he point here?”, and then there are the other 98% of the people who stumble on my posts and articles and prefer I keep the post small and to the point. So this, my first T-SQL Tuesday post and first blog post in too long a time, is for the people who want to learn about two new columns in two existing DMVs and learn about them quickly.
Microsoft announced many new features in SQL Server 2016 SP1 and the fanfare was mostly centered around the Enterprise features now available in SQL Server 2016 Standard Edition. Many may have missed some hidden gems in the announcement. Two of these are columns added to the existing DMVs, sys.dm_server_services and sys.dm_os_sys_info. The columns provide information for two specific features that previously had to be gathered by opening gpedit.msc and/or scrolling through SQL error logs. I am referring to Lock Pages in Memory and Instant File Initialization (enabled via Perform Volume Maintenance Tasks privilege).
It is now possible to simply query the DMVs to determine if these are being used for the running SQL Server instance.
To determine if Lock Pages in Memory is the current memory model, simply run:
|
1 2 3 |
SELECT sql_memory_model , sql_memory_model_desc FROM sys.dm_os_sys_info; |
Since I chose to run my SQL Server instance as LocalSystem, I can see that indeed my instance is currently using the Lock Pages in Memory option in contrast to Conventional memory, which it would have had if the service account did not have the Lock Pages in Memory privilege. I do not, of course, recommend running SQL Server instances with LocalSystem as the execution account. I did this to prove a point about how you can unknowingly have options enabled for accounts with excessive, or more accurately, unmitigated privileges.
The next DMV provides similar information about Instant File Initialization.
|
1 2 3 4 5 6 7 |
SELECT servicename , startup_type , startup_type_desc , status_desc , last_startup_time , instant_file_initialization_enabled FROM sys.dm_server_services; |
And the result shows instant_file_Initialization_Enabled enabled on my instance, again because of my LocalSystem.
I have to admit, I have not used these DMVs much in the past and due to the fact that these two new columns will only be seen on SQL Server 2016 installations, I will have to wait a while to really make use of it but I thought they were worth mentioning and mentioning quickly.
Thanks for reading.
Load comments