Browsing SQL server 2008’s new DMVs

SQL Server 2008 is out of the bag and—luckily for my team—at Pythian we are already seeing customer interest in upgrading, even from SQL Server 2000 and 2005. There are many new features and there will definitely be more blog posts coming from the team regarding them, but for now, I was just browsing around the new 2008 Dynamic Management Views (DMVs), and did a quick overview of some that spiked my attention:

select * from sys.dm_db_mirroring_auto_page_repair

It looks like grouping by db or file id would be useful to zoom in on possible disk issues causing the page errors. Beware—only 100 rows are kept for any database, so if you want to keep a full history you should move those records on a scheduled basis.

select * from sys.dm_db_mirroring_past_actions

This one could be useful for monitoring when there is no witness on a mirroring setup; or for creating a mirroring history report.

select * from sys.dm_db_persisted_sku_features

This one should be used as a warning sign before moving databases between editions, specifically between Enterprise and Standard. Paul Randal has a great post on it: SQL Server 2008: Does my database contain Enterprise-only features?.

select * from sys.dm_db_script_level

The only thing I could find on this was this KB article: Error message when you try to run a query in SQL Server 2008: “Msg 916, Level 14, State 1, Line 1”, but no Books Online official page. It looks like some change-tracking internal view for hotfixing and service packing purposes. Oh well, it’s good to know what’s in your SQL anyway.

select * from sys.dm_exec_procedure_stats

This one is very similar to dm_exec_query_stats but on the Stored Procedure level instead of the statement level. It’s useful if you are interested only in stored procedures as a whole, or if you want to filter ad hoc SQL batches from your analysis.

select * from sys.dm_exec_trigger_stats

This is very nice, as trigger-related issues are a somewhat common cause of performance hiccups on client environments.

select * from sys.dm_os_memory_brokers

This is the DMV form of information that was returned in previous versions by DBCC MEMORYSTATUS. It is useful for identifying and diagnosing possible memory pressure issues with regular T-SQL querying. It was a real pain to get it with the DBCC MEMORYSTATUS output.

select * from sys.dm_os_memory_node_access_stats

This one is not documented in Books Online. It looks like read and write stats for different page types, aggregated by memory nodes. I wonder if internal memory pressure can be analyzed or diagnosed with some help from this view.

select * from sys.dm_os_memory_nodes

Books Online says, “Nodes are created per physical NUMA memory nodes.” Again, this is taken from the DBCC MEMORYSTATUS output.

select * from sys.dm_os_nodes

Tied to the SQL OS architecture*, this view has some interesting stats like active_worker_count and avg_load_balance. I will definitely be running some tests and checking the output of this.

select * from sys.dm_os_process_memory

General memory stats from the server, useful for SQL-based monitoring on columns such as process_physical_memory_low that could alert after N minutes have passed on low memory warning. Also for baselining with the page_fault_count. This is an accumulated value though, so the differences should be measured and stored on a separate monitoring table.

select * from sys.dm_os_sys_memory

These are system-wide memory statistics, including columns that show if the system is on high- or low-memory overall. Definitely useful for monitoring and correlating with SQL memory statistics on non-SQL Server exclusive machines (Which should not exist. But the ideal world and the real one are so different, aren’t they?)

select * from sys.dm_sql_referenced_entities
select * from sys.dm_sql_referencing_entities

These are both tools for tracking dependencies, covered in depth by Aaron Bertrand on Keeping sysdepends up to date in SQL Server 2008.

Now, I’m obviously just scratching the surface. There are many other new DMVs I haven’t had the chance to look at, many of them related to specific features like Full Text Search, Change Data Capture, FILESTREAM, Auditing, Extended Events (definitely a topic for upcoming posts) and Resource Governor.

If any of those interest you, take the time to browse around Books Online and explore them on your server (a trial version is out for free download!). Denis Gobo has a handy query to get all the names of the new DMVs. Start looking around and get ready for the upgrades to 2008 coming your way.

* Required reading for all SQL Server DBAs: A new platform layer in SQL Server 2005 to exploit new hardware capabilities and their trends. back