TravisDBA (7/26/2012)
"I'm not sure how I'd want this to work on my SQL Servers. After all, any load I placed on them wouldn't necessarily just occupy CPU. It would also impact the buffer pool, as the type of process I chose would influence what would stay (or go) in that bit of memory."You can throttle and control individual processes (CPU & Memory) in SQL Server now. It's called the Resource Governor.:-D
Resource governor doesn't limit CPU and memory for individual processes. It works with groups of logins, and it doesn't provide any ability to prevent resources from being used without a competing group.
So if your web site uses a common login, or all logins from your website, then you do not limit resources between these processes. If one pegs the server, they all suffer. They are all in the same pool.
Potentially you could use a second workload group to stick an artificial load on the server and remove it when you needed more resources.
The memory limits are not for the buffer pool, but query memory. Important, but not the same thing.