Kline: First, I would be remiss if I didn’t point out the Amazon has a number of products for monitoring RDS-all described HERE. For example, Tom mentioned both Amazon CloudWatch and Amazon Enhanced Monitoring as possible ways to see performance data. Plus, I work for a database performance monitoring company and would strongly encourage you to look at our products, such as DB Sentry, SQL Sentry Essentials, or SQL Sentry. And finally, for completely free query tuning help, don’t forget Plan Explorer.
Having said that, in those rare cases when you have to resort to PerfMon, you must carefully balance the overhead of frequent PerfMon polling versus the value of the data it provides, not to mention the cost of storing and manipulating the performance data. PerfMon is a lot higher overhead than SentryOne polling, about 3-5% versus less than 1% for SQL Sentry. Also, note that PerfMon is cumulative. So, you add 3-5% more overhead for each additional person running PerfMon. (For what it’s worth, you can save yourself some of the headaches of using PerfMon by sticking to the SQL Server DMV called sys.dm_os_performance_counters and writing the performance data directly to a table within your RDS database. You then query the table where you store that performance info, rather than directly invoking PerfMon).
Having gotten past all the caveats and provisos, I would recommend capturing performance data continuously while users are active (if no users are active, see if you can entirely pause the instance to save money). Depending on the kind of workload and criticality of the database/instances, I tracked between 25-75 PerfMon counters with a default polling frequency of every three minutes for business-critical workloads but adjusted up or down (for both the number of PerfMon counters and the frequency of polling) according to the criticality of the database/instance.
For an even shorter list of important PerfMon counters, check out my former colleague Allen White’s blog post, “15 SQL Server Performance Counters to Monitor.” He recommends 17 PerfMon counters, several of which you’d have to exclude because they’re Windows counters not available on RDS.