Top configuration settings to review in your SQL Servers

To ensure optimal performance of a SQL Server, and to keep things running smoothly and efficiently, we must review how our SQL Servers are configured, both within the SQL Server itself, and on the server where it resides.

1. Separate I/O Paths for Data, Log and TempDB Files. Transaction Log and database data files should always reside on separate drives to reduce I/O contention due to the nature of the work that these files perform. It is also important that TempDB data files reside on their own drive as often this is the busiest database on a server (due to its usage as a temporary data space for data aggregation and sorting, temporary user objects such as tables and cursors, and row versions for databases using snapshot isolation transactions and the fact that it can be used by all users for data from any database on the server). SSD Drives for TempDB data files are ideal for their type of work; they can also be placed on high-RPM spinning disks, preferably an array with multiple spindles.

2. Recovery Model and Transaction Log backups. The model database on a SQL Server defines how any user databases are created on that server or instance, and often the model database has a recovery model of full, which allows for the following feature usage and restoration methods:
A. Point in time recovery
B. Log shipping
C. AlwaysON Availability Groups
D. Database Mirroring
Using a full recovery model requires that transaction log backups be taken, to both allow for the features listed above and to manage the size of the transaction log file. Every transaction in a database using full recovery is logged to the transaction log, and as such, the transaction log file can become very large without proper management of the file, which is taking a transaction log backup at regular intervals. If none of these features are going to be used for a given database, then the recovery model for that database should be set to simple, which automatically reclaims log space to keep the file size small, but only allows for backups to be restored to the most recent full and differential backup.

3. Maintenance and backup jobs. To ensure recoverability of databases in the event of a failure or disaster on an instance or server, it is necessary to take backups of a database at regular intervals. It is also important to rebuild or reorganize indexes, for performance sake, check databases for corruption with consistency checks, and manage the size of the MSDB database (which tracks job and backup activity information). Every server should have these jobs running successfully on the server; in the event that any of these job types fail, it is an indicator that there is an issue with the SQL Server itself that requires attention.

4. Maximum Degree of Parallelism. If a SQL Server has more than one CPU is available on the server, it’s default value for this setting of 0 will allow it to determine the number of processors employed to run a single statement, for each parallel plan execution, up to number of CPU’s on the server. For example, on a server with 8 CPU’s, any single query could potentially utilize all 8 CPU’s at the same time to execute the query, which would deny access to the CPU from other queries until the initial query completes. The max degree of parallelism option value can be changed to limit the number of processors to use in parallel plan execution. To suppress parallel plan generation, set max degree of parallelism to 1, which would make each query plan single threaded; this allows for more CPU’s to be available to service multiple queries, at the cost of any single query potentially taking longer in time to execute. Setting the value to a number greater than 1 restricts the maximum number of processors used by a single query execution, but allows for both a multi-threaded query plan execution and access to the free CPU’s to be used by other queries. If you see the CXPACKET wait type as a top wait type on your server, this could be an indicator that the maximum degree of parallelism setting is too high and needs to be adjusted.

5. Job and Database Ownership. If the owner of a Job was an employee in Windows/AD whose security identifier (SID) exists in SQL Server (the individual was added as a login to SQL Server), then the job will still work. This also applies for SQL Logins which might be disabled (like “SA”). The reason for this is that the SID for the job owner exists in the table sys.server_principals and SQL Server will know that this SID relationally is a sysadmin. Since SQL Server Agent will not do impersonation for sysadmins, there’s no need to check Active Directory and see if this SID exists. When the owner’s SID does not exist as a sysadmin, an issue occurs. The issue is that the recorded owner of the job is the SID for the Windows user, even though that SID doesn’t exist in sys.server_principals. If this user still exists in Windows/AD, then the job will work. Agent will run this job owned by “?”, ask Windows who this SID is and see that this SID is a member of a group which exists as a login in SQL Server and that is sysadmin. Now, if the SID does not exist in Windows anymore, the job is owned by a user “?”, and no longer exists in Windows/AD so there is no record to tell SQL Server that the user had sysadmin privileges. The problems that can arise from an orphaned owner for a job or database are:
A. Problems executing stored procedures
B. Problems with tools
C. Broken cross-database ownership chains
D. Failing Jobs
It is best practice to either assign ownership to a user created specifically for this purpose, or at the very least, to the SA user.

SmartDog Services is here to help you with your SQL Server configurations. Our SQL Server Health Check service reviews configuration settings on your server, both at the database engine and windows OS levels, as well as performance aspects of the server like identifying long running queries and reviewing failing jobs, and makes recommendations on corrections that need to be made to rectify any issues. SmartDog can also assist with SQL Server implementations and migrations, and respond to any performance issues that may arise in your environment. Please contact us if you would like to pursue any of these services in more detail.