I have a SQL 2005 database running in an instance of SQL Server 2005 Express Edition. There are two different .Net applications running that constantly connect to this database and perform various tasks. One application inserts data at a rate between 0 and 200 records every 5 seconds, these inserts require some look ups to be done before the inserts can be done. The second application then polls the DB every 30-60sec and performs analysis on this data, this application also has a GUI.
The two apps and the database are all running on a single stand alone machine that isn't connected to a network. The machine is not a server, just a commodity 32bit PC running a fully service packed version of XP, it contains 4 gig of RAM.
The application with the GUI occasionally becomes sluggish and sometimes completely unresponsive for short 10-30second periods, but generally recovers. To be honest, the 'bug' reports are a little thin on solid information at this stage, all I know for sure is that whatever is wrong has somehow become all my fault. With issues like this I usually find that once I log some solid performance data and can show actual response times etc the issues 'magically' stop being issues anymore. So I have some performance counters (using perfmon.exe) in place for the two .Net applications and am waiting for a significant time period to pass before analysing them. But I currently don't have any counters in place for the SQL Server instance.
What performance counters should I have in place to monitor the SQL Instance's Memory, CPU, Performance, etc... ?
Can you point me to any decent articles explaining what each of these counters means. I found Tips for Using SQL Server Performance Monitor Counters by Brad McGehee which is good, but possibly a little dated - some of the tips seem to be for 2000 instances only.
Any other hints, tips, etc?