Sql-server – SQL commands to get performance statistics


Are there SQL commands that I could use to extract performance monitoring data from MS SQL 2005, such as:

  • transactions per second
  • page reads/writes
  • connections (@@CONNECTIONS gives the total, but what about current)
  • physical reads
  • locks and blocks
  • other counters that might be interesting?

Best Solution

You want to look at Dynamic Management VIews (DMVs), introduced with SQL 2005.

This is a really great document from MS that gives you an overview as to how to use DMVs troubleshoot performance issues: