Olivier Le Moal - Fotolia

Manage Learn to apply best practices and optimize your operations.

SQL Server best practices for handling the post-installation blues

The job isn't over after a successful SQL Server installation. These SQL Server best practices will help you optimize your new SQL Server installation from the start.

In any organization, SQL Server installation, configuration and maintenance are some of the key responsibilities of a database administrator. In this article, we'll take a look at some of the important SQL Server best practices to follow after a successful installation of the database software.

Let's start with patching your databases. From time to time, Microsoft releases service packs, cumulative updates and hot fixes for versions of SQL Server that have not reached end of life on technical support. It is highly recommended to download and install newer patches from Microsoft's Update Center webpage for SQL Server on a regular basis, starting immediately after an installation is completed. As a best practice, the patches need to be tested in a development environment and then subsequently moved to the production environment. Most of the patches require you to reboot SQL Server, so install them during planned downtime if possible.

Microsoft SQL Server 2012 introduced the Product Update feature into the installation setup. This feature discovers and suggests the latest updates available to enhance SQL Server security and performance. It's also built into SQL Server 2014 and SQL Server 2016's installation setup.

Configure tempdb to reduce contention

Applications that extensively use tempdb can cause latch contention issues when allocating new pages in the temporary database. If heavy contention occurs, tempdb-related queries may become unresponsive for short periods of time. These symptoms indicate that SQL Server tempdb needs to be resized to improve overall query performance. The number of data files you need to configure for tempdb depends upon the number of logical processors allocated to an instance of SQL Server. 

Create database maintenance plans

Once you've created user databases on a SQL Server instance, database maintenance plans can be set up to automate tasks such as rebuilding indexes to better organize data, compressing data files, and backing up all of your databases and transaction log files. The backups will allow you to achieve point-in-time recovery of databases based on your database recovery plan. Separately, remember to regularly back up all of the system databases built into SQL Server -- except for tempdb, which can't be backed up. This includes the Resource database, a read-only database that stores all system objects and can make it easier to upgrade to new versions of SQL Server. However, SQL Server can't back up the Resource database itself -- you have to manually do a file- or disk-based backup.

Run the DBCC CHECKDB command regularly

As part of SQL Server best practices, database administrators (DBAs) also need to be proactive about detecting database corruption in their environments. One way to do this is to run the DBCC CHECKDB command regularly against all user and system databases. The command performs consistency checks against databases to look for corruption issues that could result in lost data and processing problems.

To overcome a system failure or other disaster with minimal or no data loss, DBAs also need to perform nightly or weekly full database backups onto another SQL Server instance -- then run the DBCC CHECKDB command to identify potential corruption. In addition, there's no substitute for periodically testing full database backups and your disaster recovery plan.

Monitor the msdb suspect_pages table

The suspect_pages table in the msdb system database can also help in identifying database corruption. It stores the identifications of suspect pages in a SQL Server instance, along with the ID of each page's database. The table includes errors like bad checksum, torn page, and error messages 823 and 824. So the best practice is to create a regularly running SQL Server job to monitor the suspect_pages table and send out an email to the DBA team every time a new record is added.

Allow 'perform volume maintenance task'

During database creation and restoration and data or log file growth, SQL Server fills any space consumed with zeros. This operation consumes a lot of SQL Server resources. Avoid this by granting "perform volume maintenance task" privileges to the SQL Server Database Engine service. Once you grant this privilege, SQL Server will skip the zeroing step and instead instantly allocate the newly added space to the database. This also helps reduce the time it takes to restore a database.  

Use the dedicated administrator connection

The dedicated administrator connection lets a DBA connect to a server when it's in an abnormal state and not responding to user connections. This helps the DBA run troubleshooting queries or diagnostic functions. Use the sp_configure system stored procedure to enable this feature at the instance level.

Enable backup compression on instances

The database backup compression feature is available to SQL Server Enterprise and Standard Edition customers. This feature can be enabled at the SQL Server instance level using SQL Server Management Studio (SSMS) or T-SQL scripts. It can be used to create compressed backups of all databases without actually specifying the WITH COMPRESSION clause.

Configure minimum and maximum server memory

SQL Server best practices also include configuring the minimum and maximum memory that can be allocated for SQL Server processes in each database instance. In its default setting, SQL Server dynamically changes memory allocations based on processing workloads and available resources. But DBAs can manually set the minimum and maximum memory levels in order to limit the amount of memory that the database can access. The potential maximum server memory for a particular instance can be calculated by subtracting the amount of physical memory required for the operating system and any other instances from the total memory capacity in your SQL Server system.

Set the maximum degree of parallelism

Use the max degree of parallelism setting to limit the maximum number of processors used for parallel plan execution. The default value is zero, which enables SQL Server to tap all available processors up to a ceiling of 64. Limit the maximum number of CPUs used for a single query execution by specifying the desired total. This is an advanced-level configuration change that can be performed either by using SSMS or the sp_configure system stored procedure. 

Next Steps

Database trends to keep in mind after upgrading past SQL Server 2005

Things to consider before installing SQL Server 2014

Microsoft improves in-memory OLTP for SQL Server 2016

Dig Deeper on Microsoft SQL Server Installation