3

Related Perfmon: which counter identifies that threads are waiting? but sadly abandoned by the asker

Using perfmon, which counters should I pay particular attention to when debugging ASP.NET and SQL performance problems?

Some system stats in case that makes any difference

SQL Server 2008 (64 bit version)
Windows Server 2008 R2 Enterprise x64 (SP1)
12GB RAM
Xeon X5560 x 2.80GHz (2)

There are two fairly active database instances on this computer. One belongs to our product, one belongs to a SCADA vendor. I would love to see the performance separately on each of those two instances to know if one of the components keeps dropping into contention.

Community
  • 1
jcolebrand
  • 298
  • 5
  • 27
  • Is it a question for stackoverflow? I think this question is related with programing and troubleshooting. – sky100 May 13 '11 at 17:51
  • @Sky I wavered on that myself. Since I'm more concerned about SQL and IIS I figured this was the place to go. I don't normally see the problems on this site on any of my other customer sites (and they don't have a SCADA db on them either) – jcolebrand May 13 '11 at 17:52
  • I think it belongs here. It's most likely an administrator who should notice performance problems on the production servers. Follow up questions on how to fix any problems caused by bad T-SQL/.NET code would belong on SO. – Shane May 13 '11 at 17:58
  • @Dynamo I agree with that wholeheartedly. This is most likely caused by having the two databases on one box and us hitting some disk contention we weren't aware of or something similar. – jcolebrand May 13 '11 at 18:02

2 Answers2

3

This blog post by Brent Ozar is really good for the SQL side. I use the advice here as a starting point to monitor my servers. Perfmon has wait stats for each instance as well so you can easily compare how your instances are doing.

The Activity Monitor in SQL Server Management Studio is also useful for looking at the instances individually. I like to compare the top cumulative wait times for my instances against the data from Perfmon to try and see where I have problems.

Brents post has some very good advice to start you off and if you see any problems from there you can start digging deeper into the specific counters. If you have some time watch the video in the blog post as well as I picked up a few other bits of info that aren't in the text.

Shane
  • 1,869
  • 4
  • 20
  • 34
3

For ASP.net performance counters, you can check here

sky100
  • 504
  • 2
  • 3