Monitor and Enforce Best Practices by Using Policy-Based Management


Applies To: SQL Server 2016

Policy-Based Management allows you to monitor best practices for the SQL Server Database Engine. SQL Server provides a set of policy files you can import as best practice policies, and then evaluate the policies against a target set that includes instances, instance objects, databases, or database objects. Evaluate policies manually, set policies to evaluate a target set according to a schedule, or set policies to evaluate a target set according to an event. For more information about Policy-Based Management, see Administer Servers by Using Policy-Based Management.

The following table lists the policies included with the installation of SQL Server and information about the best practices rules each policy evaluates. The policies are stored as XML files and must be imported into SQL Server. For more information about how to import policies, see Import a Policy-Based Management Policy.

Policy nameBest practice rule
Asymmetric Key Encryption AlgorithmAsymmetric Keys Encryption Strength
Backup and Data File LocationBackup Files Must Be on Separate Devices from the Database Files
Data and Log File LocationPlace Data and Log Files on Separate Drives
Database Auto CloseSet the AUTO_CLOSE Database Option to OFF
Database Auto ShrinkSet the AUTO_SHRINK Database Option to OFF
Database CollationSet the Collation of User-defined Databases to Match Those of the master and model Databases
Database Page VerificationSet the PAGE_VERIFY Database Option to CHECKSUM
Database Page StatusCheck Integrity of Database with Suspect Pages
Guest PermissionsGuest Permissions on User Databases
Last Successful Backup DateOutdated Backup
Public Not Granted Server PermissionsServer public Permissions
SQL Server 64-bit Affinity Mask OverlapCorrect Affinity Mask and Affinity Input and Output Mask Overlap
SQL Server Affinity MaskKeep the Affinity Mask Default Value
SQL Server Blocked Process ThresholdIncrease or Disable Blocked Process Threshold
SQL Server Default TraceDefault Trace Log Files Disabled
SQL Server Dynamic LocksKeep the Locks Configuration Option Default Value
SQL Server Lightweight PoolingDisable Lightweight Pooling
SQL Server Login ModeChoose an Authentication Mode
SQL Server Max Degree of ParallelismSet the Max Degree of Parallelism Option for Optimal Performance
SQL Server Max Worker Threads for 32-bit SQL Server 2000Verify Max Worker Threads Setting
SQL Server Max Worker Threads for 64-bit SQL Server 2000Verify Max Worker Threads Setting
SQL Server Max Worker Threads for SQL Server 2005 and aboveVerify Max Worker Threads Setting
SQL Server Network Packet SizeNetwork Packet Size Should Not Exceed 8060 Bytes
SQL Server Password ExpirationSQL Server Login Password Expiration
SQL Server Password PolicySQL Server Login Password Strength
Symmetric Key Encryption for User DatabasesSymmetric Keys on User Databases
Symmetric Key for master DatabaseSymmetric Keys on System Databases
Symmetric Key for System DatabasesSymmetric Keys on System Databases
Trustworthy DatabaseTrustworthy Bit
Windows Event Log Cluster Disk Resource Corruption ErrorDetect SCSI Host Adapter Issues
Windows Event Log Device Driver Control ErrorDevice Driver Control Error
Windows Event Log Device Not Ready ErrorDevice Not Ready Error
Windows Event Log Failed I_O Request ErrorDetect Failed Input and Output Requests
Windows Event Log I_O Delay WarningCheck Disk Input and Output Subsystem for IO Delay Problems
Windows Event Log I_O Error During Hard Page Fault ErrorInput and Output Error During Hard Page Fault
Windows Event Log Read Retry ErrorCheck Disk Input-Output Subsystem for Read Retry Problems
Windows Event Log Storage System I_O Timeout ErrorStorage System Input-Output Time-out
Windows Event Log System Failure ErrorUnexpected System Failures

Working with Policy-Based Management Facets

Community Additions