SQL commands to get performance statistics

Question

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?

Answer 1

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

Answer 2

The best way of seeing what's going on under the hood in SqlServer is to use the Performance Monitor built into windows, click Admin Tools -> Performance. If you haven't used it before the trick is to start it, then click the + icon at the centre top of the window, a dialog opens with 100s of different measures that you can then chart,.watch, or log.

SQL Server has loads of counters that you check out, what all the data means is of course a different question. This solution doesn't integrate with TSQL or Management Studio, but it is the best way of finding out what's going on.

Answer 3

A great place to learn how to performance tune SQL Server is Brent Ozar's website.

It includes details of how to use Performance Monitor, DMV's and how to data mine and interpret the results.

http://www.brentozar.com/sql-server-performance-tuning/