Hi Cliff,
I see two aspects in your questions. Capacity planning and capacity optimization.
To do proper capacity planning, you should execute load testing on your website. Visual Studio comes with great load testing tool that will enable you to simulate visitors and capture + record performance counters. We have some capacity planning with DNN and this is how we proceed using Visual Studio:
- we record a test scenario, limited to a small use case (such as viewing a survey question and submitting an answer)
- we play the scenario with load increments (such as adding 5 new users every 30 seconds)
- we monitor the response time, and ensure we get a response under 2 seconds (which is our target service level)
- once we hit this mark, we note the number of concurrent users
- we then play the same test using a constant load of our maximum number of concurrent users and record resource utilization.
- Base indicators: memory, cpu, bandwidth on the webserver, memory, cpu, transactions, disk I/O on the sql server
Given this information, we can identify a bottleneck. Your top performance will always be limited by one core resource: cpu + memory (processing power), bandwidth speed, storage speed.
If you see the CPU nearing 100% much of the time, then CPU is what is limiting your capacity. If it was disk that was your bottleneck, or any other resource, you would most likely not be able to hit 100% CPU.
Once you have identified your bottleneck, you have 2 options. a) either scale this resource, such as adding more CPU b) try to optimize your website to reduce its requirement for this type of resource.
If you host both SQL and the web server on the same machine, the first step would be to check what process is using the CPU (is it w3wp,exe or sqlservr.exe). I would suspect the web server to be taking the CPU and not SQL Server.
If it is your website that is using much of the CPU, then you would need to do some profiling on the website to determine what part of the website is using a lot of CPU. Visual Studio also has a great tool to do profiling (not all editions, you'll need to verify which ones). With profiling, you'll be able to spot which module / aspect of the website is using a lot of CPU. Then, either change this functionality (such as using other module / menu) or if they are custom modules, review and optimize your custom code.
To answer your specific questions:
> Should we be limiting SQL Server's access to the CPU at all or let it self manage?
If SQL Server is using a lot of CPU, it probably needs to do so to service web requests. I wouldn't try to limit this. But make sure SQL Server is the one using the CPU
> What are some connection pooling best practices?
I don't think you need to tweak connection pooling parameters, but definitely don't turn this feature off
I forgot one last thing. The stressing method explained here is used to determine the bottleneck at max capacity. This is the maximum number of users your website can handle at exactly the same time. It is not representative of actual concurrent users capacity. To estimate your real capacity, you should record a more realistic scenario, such such as "logging in, viewing survey question 1, submitting, viewing survey question 2" and include think times (users do not click each second).
Agreed, this is a large question. I hope you'll find some usefull information in this post.
Best,
Etienne