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...