Resource utilisation considerations made by the Query Optimiser
A customer asked me late last year to find any available literature on if / how the optimiser takes current resource usage - locks, cpu, memory, disk, # users logged on etc into account when determining execution plans..
I was sure I had read in the past that the optimiser does in fact make at least [i]some[/i] execution plan decisions based on server resources but couldn't remember exactly what.
I wasn't able to find anything published on this anywhere. Not much turned up in BOL, printed books or the KB so I posted into the private MVP group to see what I could fish up from the SQL team & ended up with an interesting response.
After a bit of discussion around the topic the SQL Server Query Optimizer Team Program Manager, Eric Hanson, added this detailed response to the discussion. I thought it would be worth sharing here in case anyone else finds this of use..
======================================================
I talked to some of our developers and have some more to dd regarding the information considered by the optimizer about current system load.
* the query executor will strip out parallel operators from the plan and run the plan serially when the plan is executed if the load on the system is such that not enough threads can be allocated to run the plan with DOP 2 or more
* the query executor attempts to allocate a fairly large fraction of available threads (around half the reamaining ones) to an incoming query with a parallel plan. As the system load increases, at some point there may not be enough threads to run a newly dispatched parallel plan with DOP 2 or more. This would cause the system to strip out parallel operators and run it serially. This can lead to some non-obvious behavior, e.g. I start 3 queries at the same time that would take one hour each if run in parallel alone on a parallel machine. The first one to start grabs a lot of processors and runs in about 1 hour. The second one grabs less processors and runs for about 2 hours. The third one is stripped of parallel operators and runs serially, taking, say, about 8 hours. Plans are not killed and restarted, or given more threads, even though the machine's processors may free up while the plan is executing.
* We can keep 2 plans in cache for the same query, one parallel plan and one serial plan (this is relevent if you change the system's DOP setting with sp_configure while some plans are in cache already)
* when a query comes in, if the number of idle processors is <=1, then "serial plan" is specified as part of the search key during plan cache lookup. If the number of idle processors is >1, then "parallel plan" is specified during cache lookup.
* the ability to keep two plans in cache for the same query (parallel & serial) is in both Shiloh and Yukon
* the cost estimator of the optimizer does consider available buffer space owned by the SQL Server process in cost calculations. This is used in some cases to estimate page access cost, where a cache hit ratio is predicted. Since available buffer space owned by SQL Server can go up and down with system load, and as a function of how long SQL Server has been running, and thus how much memory it has grabbed from the OS, this can affect plan selection.