I often use sp_who and sp_who2 to see who is connected to what on our development database server. When we need to restore a database, I need to make sure no one else is connected and sp_who is a quick way to get that information. The annoying part is that it displays every connection to every database, you can’t filter by database. Vyas Kondreddi wrote a decent version that he named sp_who_3. It does pretty much what sp_who does, exception it takes lots of optional parameters that let you filter the data. I tweaked his code a little bit, but it’s good stuff.
We are in the middle of testing a new web application and we have 7 instances of it running on 2003 Server box. Each instance has a web page virtual directory and a web service virtual directory. Some of the web services are on another box, but more of the bits are on this server. I was doing some related file cleanup on this box and it felt a little sluggish. A quick peek at Task Manager showed both processors (dual Xenon) were pegged at 96% to 99% of the CPU, in the w3wp.exe process. This is the process that manages the application pool in IIS6. I had all of the sites running in the same default app pool and it was imposible to tell which site was causing the spike, or if it was caused by just having a single app pool.
With one app pool, it’s imposible to tell which site or sites is eating up the CPU cycles. I went in created application pools for each web site and web service folder and then assigned each site/service to it’s application pool. This gets me a few things. First of this is how it’s supposed to be set up in the first place. Each site will will get it’s own process and I can use the iisapp.vbs script to list each app pool with it’s process id and the associate site assigned to that pool. That should fix the problem or at the very least narrow down the list of the usual suspects.