Detecting W3WP CPU issues using jetBrains dotTrace

Question

Our W3WP process on our production server is constantly high. It doesn't max out at 100% but jumps up into the 90%s a fair bit. To help look into this I profiled the live aplication using JetBrains dotTrace.

The results were as expected. All the slow methods were NHibernate functions that queried our database. My question is, would these slow methods actaully affect the CPU on our web server, as our database server is on a separate machine. Surely if the database server is doing some work then the web server jsut waits for a response, and the CPU shouldn't go up?

If this is the case, how do I use dotTrace (or another tool if neccesary) to work out where the CPU is being used as opposed to the server just waiting for a response from elsewhere?

dotTrace screenshot of hot spots

You can see from the screenshot that most of the time is spend waiting for external HTTP requests to complete. However, these shouldn't affect the CPU usage on the web server I'd have thought

Answer 1

It may well be NHibernate itself that is doing the hard work on your web server, and that the database is actually doing relatively little.

I would recommend running a SQL profiler to see whether it is really the case that the database is taking a long time on a single call (from NHibernate).

My guess is that you will see NHibernate making lots and lots of calls to the database and then processing them (on your erb server server) and that it is this that is responsible for the high CPU.

If you have a lot of lazy fetching on joins, you can end up in the situation where NHibernate makes many, many calls to the database get the data for one request.