Pieter Vanhove has published his Policy-Based Management-based DBA Morning Checklist and has some post-Summit additions:
Optimize for Ad Hoc Workloads
The policy is going to check if the server setting Optimize for Ad Hoc Workloads, is set to True. By default, this setting is set to False.
The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. More information can be found on https://msdn.microsoft.com/en-us/library/cc645587.aspx
I don’t see any downside by enabling this setting.
Not many shops use PBM, so I’m happy to see Pieter contributing this to the general community.
Nic Cain has an outstanding blog post on enabling Instant File Initialization in SQL Server 2016, specifically wondering what happens when group policy explicitly prohibits setting Perform Volume Maintenance Tasks privileges:
Much to my surprise the virtual SQL account showed up in the PVMT secpol setting. I had no idea how it got there. Reviewing the setting I was able to confirm that the account I used for install was not able to make any adjustments and yet somehow the permissions were set.
I’m happy to hear why I’m wrong, but I’d consider this a reasonable instance of privilege escalation: I may not want the DBA to be able to perform volume maintenance tasks on just any server, but I do want him to do it on SQL Server instances.
That’s a wonderful question, and I get asked this all the time.
I can justify the desire for virtualization in the scenario you described. There are a number of reasons to consider virtualization given those constraints.
Virtualize everything, as Klee suggests. The worst case is that administration gets slightly more complex, but the advantages are worth it.
SQL Server 2016 has a per-session wait stats DMV: sys.dm_exec_session_wait_stats. That’s exciting; wait stats are extremely interesting, but until now, impossible to use on a per-item level in a busy production system (where you’d most want to use them).
[R]esetting the data of sys.dm_os_wait_stats operating system view doesn’t affect the values of sys.dm_exec_session_wait_stats view.
Based on my MSDN reading, the sys.dm_exec_session_wait_stats DMV resets if the connection pool context is re-used or if the session closes. This is why DBCC SQLPERF doesn’t include a reset option for session-specific wait stats.
Does this mean there will be no 32-bit version of SQL Server 2016? They may make some desktop version; I don’t know nor have I been following. But as a Server product? RIP, and good riddance.
So you can thank (or damn) me for this one. Me, I’m going to celebrate. Where’s my bottle of Coca Cola with real sugar?
Allan Hirt, I thank you. I have one 32-bit device left: it’s a cheap tablet. Let’s not wait until 2038 to get rid of x86.