SQL Server Best Practices Library

SysKit SQL Manager 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 On SQL Servers, the Auditing of Login Failures option is enabled by default. This report checks if the Auditing of Login Failures is enabled on the SQL Server instance. Read More
Blocked Process Threshold The report tells you if the value specified for the blocked process threshold option is low. That means you should tweak 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 This report 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 poor performance and application responsiveness, or even memory loss issues. Read More
Remote Access Remote Access is an obscure SQL Server to SQL Server communication feature that is deprecated. Community best practice is that you ought to avoid it. The report checks whether the ‘remote access’ Server Configuration Option is enabled or not. Read More
SQL Server Max Memory If you have configured SQL server’s max memory, ensure that the operating system has enough memory available. If the operating system has no memory available, it will start using the page file instead of RAM. Using the page file instead of memory will result in poor system performance.
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 Always track 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 report shows thresholds which you can use to spot potentially problematic databases.
Database Collation The report 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 Use the FULL or BULK-LOGGED recovery model when dealing with 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 The report determines if all servers have enough free disk space. You should be keeping an eye on 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 The report checks whether the databases have been recently backed up. Read More

ModelDB

ModelDB Autogrowth The modelDB’s autogrowth should be in megabytes and set to a value larger than the default. Don’t use the default settings. Also, 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. Don’t use the default settings. Also, these values should be set in accordance with your environment. Read More
ModelDB Recovery Model Set the modelDB’s recovery model to FULL. Read More

Security

Different SQL Service Accounts Use separate accounts for different SQL Server services. Running different services under the same account is considered a security risk. Read More
Guest Permissions You can’t drop the guest user. However, you can run the REVOKE CONNECT FROM GUEST within any database other than the master or tempDB database. This will revoke the guest user’s CONNECT permission as well as disable the user. Read More
Public Role Not Granted Server Permissions The report checks if server permission is granted to the Public role. Read More
SQL Server User Password Policy The report checks if you have the password policy option enabled.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 The report checks if you have a password that can be easily guessed. SysKit SQL Manager checks for 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 default trustworthy bit option is not a best practice. If the trustworthy bit is turned on, a privileged database user can elevate the permission level to the sysadmin role. That leaves the door open for security breaches and unsafe assemblies that can compromise the system. Read More

SQL Server Agent

SQL Agent Service Account Running the SQL Server Agent service under a highly-privileged account is a security risk and a bad practice. The report checks whether the SQL Agent account on the server has administrator privileges. 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. If the number of logical processors is greater than 8, use 8 data files and then, if contention continues, increase the number of data files by multiples of 4. Additionally, 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 a bad practice. Instead, the TempDB data files should be on a 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 The report check whether servers in your environment are up to date. SQL Server cumulative updates are released regularly, so this report also checks the Microsoft SQL Server Update Center for the latest updates.
Windows Updates This report checks whether any servers have Windows updates that are not installed.