SQL Server Best Practices Library

SQLDocKit stores all the information about your SQL Servers and databases and provides you with metrics that can be analyzed according to best practices. Prevent common problems from occurring, and never doubt whether your SQL servers are up to date.

Report Name Description

Configuration

Auditing Login Failures Auditing of Login Failures is enabled by default. It was detected that the Auditing of Login Failures is not enabled on the instance of SQL Server. Read More
Blocked Process Threshold The current value specified for the blocked process threshold option is very low. Set the blocked process threshold option to a value of 5 or higher. Read More
Data Backup Volume If the volume that contains the database files fails, you cannot restore from the backup because the backup is also on the same volume. Read More
Disk Allocation Size Checks whether the block size equals 64 and whether the following calculation (Partition Offset / Block Size) comes up with an integer value as a result. In most cases, this is a storage best practice; however, you should refer to the storage documentation to determine whether this rule applies to your storage. Read More
Max Degree Of Parallelism The Microsoft SQL Server max degree of parallelism (MAXDOP) configuration option controls the number of processors that are used for the execution of a query in a parallel plan. Some applications like SharePoint, Dynamics NAV, SAP, BizTalk has the need to use MAXDOP = 1. Please confirm that your instance is not supporting one of these applications prior to changing the MAXDOP. Read More
Max Worker Threads Using incorrect values for the max worker threads option can result in performance, application responsiveness, or memory loss issues. Read More
Remote Access Remote Access is an obscure SQL Server to SQL Server communication feature that is deprecated. And you probably shouldn’t be using it. This checks if the ‘remote access’ Server Configuration Option is enabled or not. Read More
SQL Server Memory The minimum and maximum SQL memory values should be configured, and they should differ from the default values. Read More

Databases

Auto Close On On busy systems, repeated opening and closing of the database can result in performance problems and timeout issues. If the database is used regularly, disable the Auto Close option. Read More
Auto Shrink On Configure the database files for sizes required by application and maintenance usage. Avoid using the Auto Shrink option to manage the database file sizes. Read More
Database Autogrowth We recommend that you proactively manage the growth of data and log files by pre-growing all data and log files to their anticipated final size as much as possible. We also recommend that you enable autogrowth for safety reasons. Do not rely on the default autogrowth settings. Read More
Database Capacity We recommend that you proactively manage the growth of data and log files. A database that is close to full capacity may need attention to avoid initiating growth during critical hours. The thresholds given are used to direct attention to potentially problematic databases.
Database Collation Compares Database Collations to Server Collation. Read More
Database Files Database files and transaction logs should not be on the primary drive; they should be on separate drives. Read More
Simple Recovery Model For disaster recovery purposes, it is recommended that the FULL or BULK-LOGGED recovery model be used in production servers. Read More
Virtual Log File Count Too many virtual log files can cause transaction log backups to slow down and can also slow down database recovery and, in extreme cases, even affect insert/update/delete performance. Read More

Hardware Requirements

Free Disk Space This determines whether all servers have enough free disk space. Please check the available disk space to avoid system failure. Read More
RAM This determines the minimum and recommended amount of RAM for use with a SQL server. Read More

Maintenance

Active Directory Valid Logins Keep your logins accurate and up to date by removing SQL Server logins that are used by Active Directory users that are either disabled or removed from the domain.
Database Backups This checks whether the databases have been backed up recently. Read More

ModelDB

ModelDB Autogrowth The modelDB’s autogrowth should be in megabytes and set to a value larger than the default. You should not use the default settings; these values should be set in accordance with your environment. Read More
ModelDB Files Initial Size The modelDB’s initial size should be set to a value larger than the default. You should not use the default settings; these values should be set in accordance with your environment. Read More
ModelDB Recovery Model The modelDB’s recovery model should be set to FULL. Read More

Security

Guest Permissions The guest user cannot be dropped. However, run REVOKE CONNECT FROM GUEST within any database other than the master or tempdb database to revoke the guest user’s CONNECT permission and disable the guest user. Read More
Public Role Not Granted Server Permissions Checks that the server permission is not granted to the Public role. Read More
SQL Server User Password Policy Password complexity policies are designed to deter brute force attacks by increasing the number of possible passwords. Password expiration policies are used to manage the lifespan of a password. When SQL Server enforces password expiration policy, users are reminded to change old passwords, and accounts that have expired passwords are disabled. Read More
SQL Server Users With The Simple Password Check that password is not easy to guess. We check the following rules: the password is not blank, the password does not equal to the account name of the user, the password does not equal to some of the most common passwords.
Trustworthy Bit On The trustworthy bit is set to a value that is not recommended. If the conditions in the rule are met, a privileged database user can elevate privileges to the sysadmin role. In this role the user can create and run unsafe assemblies that compromise the system. Read More

SQL Server Agent

SQL Agent Service Account Running the SQL Server Agent service under a highly-privileged account represent a potential security risk. This check reports the service account for the SQL Agent engine of the connected instance(s). Read More
SQL Server Job Owner Checks SQL Server Agent Job owners against a login to validate which jobs do not match that owner. Customize Best Practices to set default job owner. Read More

TempDB

TempDB Files Each tempDB file should be on a separate drive. Read More
TempDB Files Configuration The number of tempdb files should be the same as the number of processor cores present on the SQL server and no larger than eight. Additionaly, all file sizes should be equal. Read More
TempDB Files Growth Set the file growth increment to a reasonable size to avoid the tempdb database files from growing by too small a value. If the tempdb file size is under 200 MB, set the File Growth to Megabytes value, otherwise set the File Growth to Percent value. Read More
TempDB Recovery Model The tempDB’s recovery model should be set to SIMPLE. Read More
TempDB Response Times The write response times for tempdb should be less than 20 ms and read response times should be less than 20 ms. Read More
Temp DB Separate Drive By default, the TempDB files are put on the same drive as the SQL Server binaries. Even if the user chooses a custom install, TempDB still goes on the same drive as the other data files, and that’s not a good idea either. Instead, the TempDB data files should be on their own dedicated drive. Read More
TempDB Size The size of the tempDB should be at least 10% of the largest database on the SQL server. Read More

Updates

Is SQL Server Up To Date This determines whether a SQL server is up-to-date. SQL Server cumulative updates are released regularly, so this also checks the Microsoft SQL Server Update Center for the latest updates.
Windows Updates This checks whether any servers have Windows updates that are not installed.