Sql-server – SQL commands to get performance statistics

monitoringperformancesql-serverstatistics

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:

http://download.microsoft.com/download/1/3/4/134644fd-05ad-4ee8-8b5a-0aed1c18a31e/TShootPerfProbs.doc