Slava Oks has started what promises to be a great series on SQL Operating System (SQLOS) related dynamic management view (DMV) queries related to lower level, hardware related information about SQL Server 2005.
DMVs are one of my favorite features in SQL Server 2005, because they allow you to get much better visibility as to what is going on inside your database and database server compared to SQL Server 2000.
For the past few months, I have been compiling a small library of good DMV queries, organized by what they reveal, i.e. CPU, wait states, memory, index usage, blocking, etc. These are incredibly useful to me every day, but especially in a crisis situation.
Just remember that you must have your compatibility level set to 90, and that this information gets flushed when SQL Server is restarted, so for example, if you fail-over to another node with SQL Server clustering, the DMV stats will be cleared out.
Technorati Tag: SQL Server