Dynamic Management View Extravaganza

It was at the PASS Summit 2011 this year as I sat in a session being presented by Jason Strate on extended events that I made a mental note…….why the heck aren’t you utilizing the DMV’s as much as I could be. Then last week I am reading an article by Steve Jones on his blog about two DMV’s that were released with Microsoft’s SQL Server 2008 R2 Service Pack 1 release.

Dynamic Views and Functions return information on the state of the server. They can be used to monitor the health of a server, assist in diagnosing problems, or just help in every day tuning performance. It is important to note that with each release or version release the views could be removed or altered.

Since the Dynamic Views and Functions are broken out into Server scoped and Database scoped you will need to take into account two permissions when viewing or trying to run queries against utilizing these views and functions:

  • Viewer Server State
  • Viewer Database State

Querying a view or function is pretty straight forward, an example would be:

SELECT *
FROM sys.dm_server_services

I think Microsoft has done a pretty good job in busting these out into groups and I have noted them below. The two that were released with Service Pack 1 are for checking the Service account.

Change Data Capture Related Dynamic Management Views I/O Related Dynamic Management Views and Functions
Change Tracking Related Dynamic Management Views Object Related Dynamic Management Views and Functions
Common Language Runtime Related Dynamic Management Views Query Notifications Related Dynamic Management Views
Database Mirroring Related Dynamic Management Views Replication Related Dynamic Management Views
Database Related Dynamic Management Views Resource Governor Dynamic Management Views
Execution Related Dynamic Management Views and Functions Service Broker Related Dynamic Management Views
Extended Events Dynamic Management Views SQL Server Operating System Related Dynamic Management Views
Full-Text Search Related Dynamic Management Views Transaction Related Dynamic Management Views and Functions
Index Related Dynamic Management Views and Functions Security Related Dynamic Management Views
Filestream-Related Dynamic Management Views (Transact-SQL)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s