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?.
