Performance tuning in SQL server environments: Execution plans, index strategies, and resource cost estimation

Performance Optimization in SQL Server relies on the Query Optimizer looking at an execution plan, the Parallelism Costs, and the memory grant that the query request says it needs [1-2]. The optimize for ad hoc workload setting looks to flag a query in the caching plan on whether this is a one time execution or will be re-used. If it is set then when a query is first executed only a lightweight plan is cached and does not have the full plan details of execution costs, memory grants, associated indexes and operations. It is a distinct cache plan from the full plan and greatly reduces the memory requirements for large queries that will not be re-run. As the Query Optimizer looks for the execution plan to minimize Resource Costs, Memory for the execution of the Query, overall execution time of the query, and potentially other factors that impact the cost of the plan, HVAC works on the time taken and resource requirements.

The overall execution time of the query includes factors such as the Cost of CPU Time taken to execute the query, the IO Cost of the queries use of disk and memory, the Cost of Network, Security grants and other factors that contribute to the query execution [3-5]. The HVAC algorithm adjusts the estimates for the execution plan based on the elapsed time so far and resource load being observed whilst the query is in execution. It then examines the degree of expected parallelism for the query and compares these estimates against the SQL Server System Resource Governor’s settings for MaxDOP, Memory Limits for MAX, and the setting configured for MAX Cost, abort threshold. The Compatibility Level is looked at to flag for SQL Server 2019 batch mode adaptive joins that are capable of reducing overall query execution time. The HVAC algorithm provides a mechanism for the overall execution plan for the query to be dynamically adapted based on the query’s progress in the execution plan, and the system resources that it is consuming, such as Degree.of Parallelism and Memory Grants, where the expected time to complete exceeds the threshold set in the System Resource Governor's Delegation Threshold setting.