0

I have a virtual server with a hosting company so CPU and RAM are limited. I need to watch load/performance over time to know when to upgrade the server.

The site runs multiple ASP.NET apps as well as PHP apps, all with MSSQL backend.

What are some vital perfmon counters to use to monitor a standard IIS/MSSQL web server: I.e. incoming IIS request volume, response times, MSSQL load, etc? A short description on why the counter matters and what to watch for (if not obvious) would also be helpful.

2 Answers 2

3

You should start with the basics;

  • Processor Information\% Processor Time
  • Memory\Pages/Sec
  • Memory: Available Bytes
  • Logical Disk*\Average Disk Queue Length
  • SQL Server:Buffer Manager\Buffer cache hit ratio

If you went with the default install of non-express SQL then it will take all the memory it can and starve IIS/Windows. On the other hand, if SQL doesn't have enough memory it will have a low Buffer cache hit ratio and you will probably see disk queueing. Most other counters will be a side-effect of a constraint on disk/memory/processor for a single server installation. Once you get to the point where you need to determine if it is: IIS/SQL, php vs asp.net, identify intensive WPs, etc. then the monitoring becomes more complex and you will need to do a lot of research or hire a contractor.

User performance monitor or advisor tool linked at the bottom to setup a data collection schedule to log the key performance counters. Generally a 5-15 minute sample is sufficient for overall system monitoring/analysis. If you are trying to diagnose a specific issue you may go down to 15s or less for short periods of time.

Pages/Sec: Average of extended periods > 150 can indicate a problem, but it also depends on disk. On a cloud server you might want to avg < 150 as the disk is likely shared or remote. Also look at Available Bytes, if pages/sec is high and Available Bytes is high, then the counter can be misleading as there are other causes than paging that are beyond the scope of this question

% Processor Time: If it averages or you see extended periods > 75-80% you are CPU bound.

Buffer cache hit ratio: Generally should be 96-99%, lower indicates that SQL is not effectively caching and needs more memory

Average Disk Queue Length: Log for each individual disk, > 2 indicate that the disk is saturated. You can also log PhysicalDisk\% Idle Time and if it falls below 20% it indicates disk saturation.

You can also use Microsoft's performance advisor to jumpstart your monitoring and have more thorough monitoring based on Microsoft recommendations; Microsoft Performance Advisor

0

Your question is difficult to answer objectively. However, I can tell you that if your DB is getting regular action and the DBs aren't small, MSSQL will grab as much memory as it can figure out how to use. (Though SQL Server Express version won't grab more than 1 GB.) So don't be suprised if SQL Server is using all of your available memory. And if it is, that doesn't necessarily mean you are in need of a memory upgrade, it could still perform fine under this scenario. My point is, memory usage may not be as meaningful of a metric as say, cpu usage or website latency.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .