Note: This reply is .NET centric because thats what I find is most commonly used on the Microsoft stack.
CPU/memory stats are per worker process. So if you're looking to get per site information, your first step is going to be separating each site into it's own worker process (application pool).
Once that is complete, you can get per process stats by simply opening up task manager. If you're on windows 2003+ they'll be called w3wp.exe. Open view -> columns, and tick PID to show PID's for each process. Then open up a command prompt and run "iisapp" this will map PID's to App Pool names.
Perfmon (start->run->perfmon) exposes a lot of good information but it's not necessarily easy to access.
There are a million different monitoring packages that can read perfmon information remotely and trigger alerts when thresholds are reached. Anything from Microsoft's Systems Center Operations Manager, to What's Up Gold, to free tools like Nagios.
The thing to remember with perfmon data is it's counters are at the OS and framework level. So you can glean some good information about some of your worker processes, and .NET states.
The answer to your specific question can be found in the .NET data provider for SQL server object. Keep an eye on the connection pool counts for your application.
If you're having a performance issue and you want to capture data from a running process, take a look at IIS DebugDiag. This will allow you to create a memory dmp file when certain conditions are met, and it even has some canned analyzers which will automagically troubleshoot some common problems. Note that out of the box DebugDiag mostly has info on native (read: non-managed) code. If you're using .NET and running into memory issues check out Tess's script for DebugDiag which will do some .NET centric memory analysis on DMG files. Debug Diag .NET Memory Script
The thing is, if you're looking for actual metrics on how long a specific method is running, this sort of information isn't really available without paying for some very nice, but very expensive tools. If you've got a good tools budget (read: more then 10k) I highly recommend AviCode.