At PASS Summit I presented my session Run Your DBA Morning Checklist with Policy-Based Management. I started my session by asking the audience what they wanted to check every morning on their SQL environment.
I noted the list down and compared it to my existing morning checklist. The good thing about the audience list was that I got some new ideas to add to my DBA Morning Checklist. I’m pleased to share my new DBA Morning Checklist 3.0 with the following checks added.
Default Backup Compression
The policy is going to check if the server setting Compress Backup is switched on. Backup compression increases CPU usage, and the additional CPU consumed by the compression process might adversely impact concurrent operations. Before enabling this setting, make sure that the CPU increase has no impact on your production environment. In my experience, I had never had any issue by using backup compression by default. In most cases, the full or differential backups are taken during low business hours, where the server can handle the extra CPU load.
Max degree of parallelism
The policy is going to check the server setting Max Degree of Parallelism. The condition is built according to the Microsoft recommendation
- For servers that use more than eight processors, use the following configuration: MAXDOP=8.
- For servers that have eight or less processors, use the following configuration where N equals the number of processors: MAXDOP=0 to N.
- For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs that are assigned to each NUMA node.
- For servers that have hyper-threading enabled, the MAXDOP value should not exceed the number of physical processors.
Maximum Server Memory
I’m just going to check if the Maximum Server Memory setting is different than the default setting 2147483647 MB. It’s difficult to check other values because the policy cannot check if there are other instances or other software that need memory.
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.
I’m going to check if every statistic has been updated since the last 7 days.
Remote DAC Connection
This policy is going to check if the server setting Remote DAC Connection is enabled.
The DAC lets an administrator access a running server to execute diagnostic functions or Transact-SQL statements, or to troubleshoot problems on the server, even when the server is locked or running in an abnormal state and not responding to a SQL Server Database Engine connection. More information can be found on https://msdn.microsoft.com/en-us/library/ms190468.aspx I recommend to always enable this setting after installation.
Tables with Fragmented indexes
This policy is verified against every table in every database. The policy will fail if the index has a fragmentation percentage which is higher than 10% and page count > 1000
Trace Flags Enabled
By default, no trace flags are enabled. However, some trace flags might be useful to enable on production servers. I have blogged about the trace flags I enable by default.
In this policy, I’m going to check if all these trace flags are enabled.
Perform volume maintenance task
Perform volume maintenance task is a local group policy that can be set to improve the Instant File Initialization. More information can be found on https://msdn.microsoft.com/en-us/library/ms175935.aspx
However, I was not able to define any policy to check this GPO… I thought it was possible to check this with WMI but didn’t find a solution for it yet. So, if somebody can help me out with this, please let me know 😉
But again, you can download my full DBA Morning Checklist 3.0 from http://aka.ms/dbamorningchecklist Feel free to adjust J