Transcript Did You Know? SQL Server 2008 – [Feature]

SQL Server 2005 – Dynamic
Management Views & Functions
Whitney Weaver
Senior Consultant
[email protected]
Discussion Overview
•
•
•
•
•
•
•
History
Architecture
Scope
Permissions
Organization
Demo
Questions
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
Discussion Overview
•
•
•
•
•
•
•
History
Architecture
Scope
Permissions
Organization
Demo
Questions
Architecture
• Dynamic metadata made available by SQL 2005
SQLOS
• Resource database (mssqlsystemresource)
• Views and Functions
• Copy of DMV objects in each user database
Discussion Overview
•
•
•
•
•
•
•
History
Architecture
Scope
Permissions
Organization
Demo
Questions
Scope
• Two scopes for DMVs
– Server
– Database
• Sys schema (sys.dm_*)
• Data cleared on server restart
• Some DMVs can be reset without restart
Discussion Overview
•
•
•
•
•
•
•
History
Architecture
Scope
Permissions
Organization
Demo
Questions
Permissions
• SELECT permission on objects
• VIEW SERVER STATE
• VIEW DATABASE STATE
Discussion Overview
•
•
•
•
•
•
•
History
Architecture
Scope
Permissions
Organization
Demo
Questions
Organization
Common Language Runtime
I/O
Database Mirroring
Query Notifications
Database
Replication
Execution
Service Broker
Full-Text Search
SQL Server OS
Index
Transaction
Discussion Overview
•
•
•
•
•
•
•
History
Architecture
Scope
Permissions
Organization
Demo
Questions
Demo
• Show me the code!
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
Questions
• ??
Contact Information
• My email
[email protected]
• My blog
http://blog.magenic.com/blogs/whitneyw/default.aspx