DBA’s need to be proactive and perform health check and review of best practices periodically on all the servers to make sure that all the best practices for SQL Server are being implemented. This approach will avoid any issues which may arise in future. So rather than troubleshooting and fixing the problems after they occur, it is always good to proactively follow best practices and avoid the problems completely.
It is most common in most of the company’s that there are server outages, Performance problems with SQL Server causing unplanned application downtime. To resolve the issue, DBA’s would have done some research and made some configuration changes. If the DBA has performed a periodic review of the server and proposed those changes proactively, the issue itself would have not surfaced.
Best practices are not just applicable for SQL Server, it is also to be done at Operating System, DISK/SAN level and Network level. So, we can divide the best practice or health checks into below categories.
Operating System/SAN/Network Best Practices – SQL Server is one of the Operating System process and will heavily depends on its functioning and management. So, need to verify and enable any settings which can help SQL Server performance or avoid any issues.
Below are the some of the Operating System checks that can be performed on server with SQL Server installed.
– Make sure latest Operating System patches and security updates are installed on the system.
– Add SQL Server service account to “Lock Pages in Memory” local security policy, which basically reduces the chances of Operating System paging out or trimming SQL Server memory working set. On a VMWare server, it is more important, so that the memory is not taken back from the SQL Server by the VMWare balloon driver.
– Configure paging file to 1.5 times of available physical memory for servers will less amount of RAM, as these servers will use paging file at some point in time, but the best approach would be to increase the memory on the server. On servers with large amount of RAM, we do not need to have huge paging file, instead set paging file to same size of physical memory, just in case to grab the kernel dump in case of server crash.
– Starting with Windows Server 2008 R2, power plan is available to configure. There are normally three types of power plans available which are Balanced(this is default setting), High Performance, and Power Saver. For Servers running SQL Server, it is recommended to use High Performance power plan. This change may look simple, but is very powerful and can show significant performance gains. This option needs to be enabled from control panel and also at the NetBios level for it to show real effect. On VMWare systems, this has to be implemented on the ESX host server as well.
– Add SQL Server service account to “Perform volume maintenance tasks” local security policy, which basically enabled “Database Instant File Initialization”. Without this privilege, SQL Server will first zero out all data file before using it, which can show affect on performance on highly transaction systems. Log files cannot benefit from this, as this only works for data files. This helps very much on servers where auto-growth is enabled for database and is there is auto-growth that happens during business hours when system is being under load.
– Review installed Software’s, Applications, and Services on the server and Uninstall or Disable those which are not required, as they can use some resources too.
– It is one of the important recommendation from Microsoft to not install Anti-Virus software on dedicated SQL Server box, even if it installed, make sure that SQL Server exe’s, Dll’s and database files(.mdf, .ndf, .ldf) fileStream files, Full-Text related files from Anti-virus scan. This is very important as it can cause SQL Server to crash and generate Access Violation memory dumps and also can slow down the performance.
– It is recommended to format the disk drives having SQL Server database files using 64 KB block size. If already database files are on disks with lower block size, need to plan and move them on to new disk drives will 64 KB block size. This helps in improving the IO performance greatly.
– It is recommended to use RAID disks for SQL Server which will improve performance and also provides redundancy and support to recover data in case of disk corruption. It would be great to use Raid 10 for all the database and log file, but can be very expensive. Alternatively, you can use RAID 5 for data files and RAID 1 for Transaction Log files.
– There can be other settings at OS, SAN or Network level, so better to work with the System Administrators, SAN Admins and Network Administrators and discuss about improving setting at HBA drivers, HBA queue depth, VMWare settings, Network settings, etc and see if they are configured optimally and if any improvements can be done for those areas.
SQL Server Instance Best Practices – As a DBA, we need to make sure that all SQL Server configurations are done specific to the environment. There are no specific SQL Server configuration recommendations which is applicable for all server, so need to monitor, test and implement the best configurations specific to that server to perform well.
Below are some of the Configuration settings that need to be adjusted for better performance and stability.
– Make sure SQL Server has latest Service Pack (SP), Cumulative Updates (CU) and Security Updates installed.
– Always SQL Server hosting critical databases are running on their Dedicated Servers, so that there won’t be any impact due to other applications running on the system.
– SQL Server default instances run on port 1433 and named instance will be using random dynamic port. It is good to change the named instances to use static port, so that there will not be any problems during startup. Also, make sure to reserve this static port, so that no other applications will use this port, else this can cause SQL Server failure during startup.
– There are different network protocols that may be enabled on a server. Make sure only the required protocols like Shared Memory and TCP/IP are enabled, enable Named Pipes only, if it is used by applications.
– Configure SQL Server Max Server Memory, this is considered one of the most important settings. By default, this value will be set to very large number, meaning it allows SQL Server instance to use all of the available memory on the server, thus leading to server crash or server hang or serious memory pressure on the system. You would need to monitor the memory usage on the server and set appropriate Max Server Memory, leaving enough memory available for OS and other processes running on the server. Also, additional care needs to be taken, when there are multiple SQL Server instances installed on the server, in that case, Max Server Memory for each SQL Server instance should be set in such a way that, adding all instances Max Server Memory will be less than total available physical memory and also enough memory is left to OS and other processes.
– Affinity Mask is mostly good if it is not changed, but can be changed on servers with multiple SQL instances after careful evaluation and testing. Improper changes to this setting can cause serious performance problems.
– Max Degree Of Parallelism (MaxDOP) determines the level of parallelism, which basically has some control on the number of processors that can be used to run a particular query. Be default this is set to Zero, meaning a SQL query which goes for parallel execution can use all the available processors.
General recommendation from Microsoft about this setting for OLTP systems is
• For servers that use more than eight processors, set the MAXDOP=8.
• For servers that have eight or less processors, set MAXDOP=0 to N, where N equals the number of processors on the server.
• For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs that are assigned to each NUMA node.
• For servers that have hyper-threading enabled, the MAXDOP value should not exceed the number of physical processors.
Example: On server with 2 processor sockets, with each one having 4 cores and 8 logical processors and NUMA enabled, set the MaxDoP to 4.
Also, it is important to note that some applications has their own recommendations. For example SharePoint recommends to set the MaxDoP setting to 1, as they have designed their product in such a way that any SQL statement or Stored Procedure which is expected to benefit from parallelism has been explicitly defined with required MaxDoP level in the code itself and remaining query’s which does not benefit from parallelism would be run serially as per instance level MaxDoP setting. So, for any third party applications, contact them for recommendations for MaxDoP.
– Make sure SQL Server Priority Boost is disabled, it does not do any good, rather causes serious problems on the system, if this option is enabled. This was provided to be used only by Microsoft CSS to troubleshoot some problems and this will be removed in future versions of SQL Server.
– Login Auditing, by default only failed logins are audited, but someone may change it to audit both failed and successful logins. It is good to leave with default to audit only failed logins and if there is any requirement to audit successful logins, follow some other method like enabling Auditing feature, run SQL trace, extended events, etc.
– C2 Audit, is better left disabled.
– Create a job to periodically recycle SQL errorlogs like Monthly, to avoid large errorlog files. You may also consider creating some automated process to archive old recycled errorlog files for future reference.
– Change default data and log file path, so that any new databases created will have their data and log files created on different disk drives which are on appropriate RAID level, which will significantly improve the performance.
– Backup Compression which was introduced in SQL Server 2008 is a good option to be enabled at instance level which will save lot of disk space and network bandwidth, when stored on a backup server or tape.
SQL Server Database Best Practices – Database level settings are very important and provides lot of benefits related to performance, Security and stability, but are mostly ignored as these are more in depth settings are often forgot to configure by most of the DBA’s.
Below are some of the database level settings which provides lot of value.
– Each database by default has two files, one data file and one transaction log file, additional data and log file can be created and can be pointed to other disks to offload IO load which provides significant performance benefits. It is very important that data and transaction log files are placed on different disk drives as placing them on same drive causes lot of performance problems. Make sure that data and log files are placed on different disk drive which is on appropriate RAID level.
– System databases are created during the time of installation of SQL Server and are most often created in C:\ drive or other drives with less amount of disk space. Tempdb is also a system database and can grow large depending on the activity on the system, so need to place the system database files on different drives. Placing Tempdb on dedicated disk provides significant performance gains on systems where tempdb is heavily utilized. Also, it is good practice to create multiple tempdb files which helps benefit by reducing latch contention. There are different theories about how many tempdb files to create, in my opinion, best would be to create 1 tempdb file for 2 logical processors and limit to a maximum of 12 tempdb files. In special cases more than 12 tempdb files can be created after through testing.
– Set auto growth settings in-terms of fixed. Each database by default has one data file and one transaction log file. Additional data and log files can be created if required for performance benefits and the files can be placed on different drives. Each data and lot file is created with initial file size and can be configured to auto grow if initial file size is used and additional space is required. It is recommended to set a good initial size based on capacity planning and then enable the auto-growth setting in terms of fixed size like 256 MB or 512 MB, instead of growth in percentage which can cause problems when database files have to grow during business hours and leads to timeout’s and performance problems.
– For very large databases, it is good practice to create additional file groups and store different types of data bases on IO patterns in different filegroups. This will help in significant improvement in performance. You can create additional file groups like Index File group, BLOB filegroup, etc and place their physical file location on different disks.
– There are few database options which are mostly forgot or unknown to many DBA, but provides lot of benefits for performance, security and stability of SQL Server databases. Below are for of those options.
Database Recovery model – Recovery models determine amount of data that transaction log file needs to keep active. This will whether point-in-time recovery of all the data is possible or not and to what extent it is possible. Set the recovery model appropriately based on the Recovery Point Objective (RPO) SLA.
Auto Close – This should be disabled as this can cause performance problems.
Auto Shrink – This option should be disabled as it is not recommended to shrink a database which causes fragmentation and leads to notable performance degradation.
Auto Update Statistics – This option should be kept enabled. Statistics are used by SQL Server while compilation and execution of SQL query, and having accurate statistics helps in building accurate and optimal query plans, thus resulting in improved and stable performance. SQL Server determines how often does it need to auto-update the statistics.
Page Verify – This option should be set to CHECKSUM, which basically detects any database corruption. When a data page from memory is to be saved back to disk, SQL Server calculates a checksum and adds it to the page header and then stores the page on the disk. Later, when we read the page back to memory, SQL Server calculates the page checksum and compares it with checksum previously stored in the page header, if it matches then page will be successfully read into the memory and will be returned to the user, if it does not match then it reports that the database is corrupt.
– We need to make sure that number of Virtual Log Files (VLF’s) are minimum for each database transaction log files, as this can cause database recovery to take long time and uses will not be able to use the database properly. VLF’s can be controlled by setting up proper initial size for database file after doing capacity planning, and also auto-growth settings need to set interms of fixed size of 512 MB of higher less than 2 GB, so that number of VLF’s will be minimum.
– For performance perspective, it is important to have regular smart index maintenance on the server for all databases like rebuilding and re-organizing the indexes based on fragmentation percent.
– Updating Statistics is another most important thing which proved most of the times to significantly improve the performance. Schedule a job or use maintenance plan to update the statistics with full scan regularly like once a week or more often if there are lot of DML operations performed on the databases.
– Another important task to be performed regularly is to perform integrity check on all databases, which basically means running checkdb on all user and system databases to detect any corruption. Integrity checks can normally be scheduled to run weekly once.
– Backups are another important regular task, as we do not know when can a disaster strike, so we should be ready to be in a position to recover the data to the maximum extent possible based on the Recovery Time Objective (RTO) and Recovery Point Objective (RPO) SLA’s. Also, it is important to periodically test the restore of these backups on a test server to make sure that we can restore them successfully and also will provide us some time estimates on how much time, it can take to recover the data after a disaster.
There can be other best practices still exist and may not be covered here, so it is important to keep ourselves updated and add new findings to out best practice or health check list.
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.