SQL Server 2005 – Dynamic Management Views & Functions

1. SQL Server 2005 – Dynamic Management Views & Functions Whitney Weaver Senior Consultant whitneyw@magenic.com

2. Discussion Overview • History • Architecture • Scope • Permissions • Organization • Demo • Questions

3. History • Added to SQL Server 2005 based on customer demand for greater visibility into their instance • A few dozen people in virtual team put DMVs together • Will continue to be driven by customer need • Roughly 30 new DMVs in SQL 2008

4. Discussion Overview • History • Architecture • Scope • Permissions • Organization • Demo • Questions

5. Architecture • Dynamic metadata made available by SQL 2005 SQLOS • Resource database (mssqlsystemresource) • Views and Functions • Copy of DMV objects in each user database

6. Discussion Overview • History • Architecture • Scope • Permissions • Organization • Demo • Questions

7. Scope • Two scopes for DMVs • Server • Database • Sys schema (sys.dm_*) • Data cleared on server restart • Some DMVs can be reset without restart

8. Discussion Overview • History • Architecture • Scope • Permissions • Organization • Demo • Questions

9. Permissions • SELECT permission on objects • VIEW SERVER STATE • VIEW DATABASE STATE

10. Discussion Overview • History • Architecture • Scope • Permissions • Organization • Demo • Questions

11. Organization

12. Discussion Overview • History • Architecture • Scope • Permissions • Organization • Demo • Questions

13. Demo • Show me the code!

14. Resources • Blogs http://www.sqlskills.com/blogs/conor/ http://sqlblog.com/blogs/louis_davidson/ • BOL http://technet.microsoft.com/en-us/library/ms188754.aspx • Paul Mestemaker Program Manager, Microsoft SQL Server

15. Questions • ??

16. Contact Information • My email whitneyw@magenic.com • My blog http://blog.magenic.com/blogs/whitneyw/default.aspx