SQL Server Configuration with OLTP
In online transaction processing (OLTP) workloads, much of the workload is made up of thousands of low-cost queries. When a query comes into SQL Server, the query optimizer creates an execution plan for that query. The execution plan determines how SQL Server will gather the data, such as which tables and indexes to use. It also assigns an overall cost to each query regarding how much effort it will take to complete that query. That query cost is known as the estimated subtree cost. SQL Server has a method by which it determines which queries it will process in parallel and those that it won't. It determines this by the cost threshold for parallelism (CTP) value.
By default, the initial value set for CTP is 5. It is widely accepted that this value is too low for most environments and should be increased to a higher value. The appropriate value will ultimately depend on the workload running on the server. This can be determined by looking at the cached plans of your parallel queries. You'll be able to review which queries are being executed in a parallel manner and their execution counts. By reviewing these, you'll be able to determine what the value should be to force the more trivial plans to not be parallel and to allow your more expensive queries to take advantage of parallelism. Usually, a starting value of between 25 and 50 is an acceptable range. However, there are organizations that need a much higher value.
OLTP workloads can also generate many single-use ad hoc queries. Single-use ad hoc queries can bloat the plan cache, wasting valuable memory. Enabling the optimize for ad hoc workloads option on the server will store a small compiled plan stub rather than the entire execution plan. If the ad hoc query is executed again, the full compiled plan will be stored in the plan cache. In heavy OLTP workload environments where the optimize for ad hoc workloads option is not enabled, you could see several GB of memory wasted storing single-use plans.
Auto growth sizes for user databases should be configured for both data and log files to a fixed size rather than percentage. If a database is growing rapidly, you don't want it to have to grow by many small increments. Setting an appropriate initial size for the data and log file with a fixed size auto growth value helps prevent SQL Server from having to pause, grow, and resume each time the data and log files need to grow. Although the process is very quick, an excessive number of growths per day can impact the server.
Backup compression can also cut down on the time it takes to back up and restore a database. Backup compression does consume additional CPU, but most large backups (full and differential) are scheduled to run during non-peak times. The benefit of faster backups and restores usually negates any additional CPU load. Compressed backups also take up less space, which can save on storage costs. Backup compression can be enabled for the entire instance or specified at the time the backup is made using the WITH COMPRESSION command.
SQL Server databases encounter index fragmentation due to updates, inserts, and deletes. Index fragmentation is the result of data being moved around, leaving behind free space in the data pages. When SQL Server reads those pages, the empty space is also retrieved, which wastes space in the buffer pool. SQL Server must also scan and read more pages than it should if the data wasn't as fragmented.
For example, since SQL Server stores data on 8k pages, if those pages were 100% full and a table had 100 pages, after a number of updates, inserts, and deletes, those tables would become fragmented. That same data may now be spread across 125 pages that are only 75% fragmented. SQL Server would now have to scan 125 pages of data rather than the original 100. To control index fragmentation, regular index maintenance needs to be performed.
Index fragmentation can be remediated by performing index rebuilds or reorganizations. Index rebuild drops and recreates the index. For indexes with high levels of fragmentation, this could be a less expensive operation than reorganizing. Index rebuilds can be an online operation for Enterprise Edition, but it is an offline operation for Standard Edition, meaning that on Standard Edition, when the index is rebuilt, it is not available.
An index reorganization uses fewer system resources and is an online operation, making it the preferred method for Standard Edition instances for 24/7 workloads. You can automate index fragmentation by using SQL Server database maintenance plans, which can be configured to either rebuild or reorganize indexes based on fragmentation levels. There are also third-party tools and scripts available to address index fragmentation.
An index rebuild will update statistics for the table or indexed view. Statistics are used by the query optimizer to generate execution plans. Having up-to-date statistics can greatly improve query performance. By default, auto-update statistics are enabled on the SQL Server instance. Depending on the workload and size of the environment, statistics may not be updated frequently enough to ensure that queries are being compiled with current statistics. Creating an automated process to manually update statistics on a regular basis can ensure that the query optimizer has more up-to-date statistics to create more efficient plans. This is a problem that many organizations don't know they have, and the fix is simple.
Organizations relying on index fragmentation processes that rebuild and reorganize indexes based on fragmentation levels could be an issue. This is because index rebuild statement updates statistics, but an index reorganization does not. If an index in that environment never reaches the threshold to be rebuilt, the statistics would not be updated until they hit the auto update statistics threshold, which may not be soon enough. Organizations that are using a process to reorganize or rebuild indexes based on fragmentation levels should also have a process in place to update statistics.
Use the latest compatibility level for your databases when possible. This will ensure that you are utilizing the most up-to-date cardinality estimator for your version of SQL Server, as well as any features or functionality that are tied to that compatibility level. Compatibility levels are not automatically adjusted when you upgrade from one version to another for user databases. You must make this change manually.
Database corruption can happen. SQL Server includes a set of database console commands for administration tasks. DBCC CHECKDB is one of those commands. DBCC CHECKDB should be run periodically to check for any corruption that may have occurred. If a non-clustered index gets corrupted, you can simply drop and recreate the index. However, if the corruption is on a heap, clustered index, or system table, a database restore is most likely the only fix. You want to ensure that your backup retention and the interval for running DBCC CHECKDB provides adequate backups for you to be able to respond and restore data with any loss.
For example, imagine a scenario in which you only run DBCC CHECKDB once a week. You've also opted for just 7 days of backup retention. If the database were to be corrupted just after a DBCC CHECKDB was scheduled, then there would be almost 7 days to wait before the check would run again. This means that due to the fact you only have 7 days of backup retention, almost the entirety of your backup would be corrupted before another scan would detect the corruption and allow you to respond. A good policy would be weekly checks and at least 30 days of backup retention. Unfortunately, too many organizations find out too late that they have a problem and encounter data loss.