Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)
Browse by Tags · View All
#SQLServer 305
SQL Server 304
Administration 252
DBA 241
T-SQL 234
#TSQL 232
Development 226
Tips 216
Guidance 148
Best Practices 119

Archive · View All
April 2011 14
March 2012 11
December 2011 11
March 2011 11
December 2012 10
October 2011 10
January 2011 10
September 2013 9
January 2013 9
November 2012 9

Catalog views and DMVs – An introduction – Tricks to find DMVs in SSMS/Object Explorer - Underappreciated features of Microsoft

May 9 2011 12:00AM by Nakul Vachhrajani   

Today’s post is part of the series that I am running under the title "Under Appreciated Features of SQL Server". The series has been inspired by Andy Warren's editorial on SQLServerCentral.com.

Microsoft SQL Server 2005 was a completely different SQL Server. The internals had changed and so had the mechanisms to monitor and manage the server. What this meant was that administration tasks had to be re-developed – all procedures had to be re-written and all script repositories had to be redone. Why? SQL Server 2005 made it easier for administrators to query the SQL Server meta-data by the introduction of Catalog views and Dynamic Management Views.

Catalog Views

In the days of SQL Server 2000, administrators had to query the system tables directly. This had a number of drawbacks due to the tight coupling with the underlying table structure. However, looking at it from a developer perspective, these tables are internal to the SQL Server and we as administrators were invading it’s privacy.

Hence, starting SQL Server 2005, any metadata information that was used by the SQL Server database engine is now exposed to the administrators via Catalog Views. They are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information.

You can read all about catalog views on Books On Line at: http://msdn.microsoft.com/en-us/library/ms174365.aspx.

If you are still stuck with SQL 2000 system table based queries

You can mover over to Catalog Views by using Compatibility Views (http://msdn.microsoft.com/en-us/library/ms187376.aspx). But, if you are now thinking of moving away from system tables, my suggestion would be to take the big jump and move over to Catalog Views directly.

A mapping of the system tables to the catalog views is available at http://msdn.microsoft.com/en-us/library/ms187997.aspx

Dynamic Management Views & Functions

Fondly abbreviated “DMV”, Dynamic Management Views are perhaps the most powerful feature of the SQL 2005+ line-up. No SQL Server release previously had the ability to allow the user to dynamically query the internals of the SQL Server while it was running. DMVs allow administrators to monitor the health of the SQL Server instance, diagnose problems and tune performance. If SQL Server were a human, DMVs would turn the skin transparent and allow you to see the pumping heart and the entire blood flow.

Contrary to older releases of SQL Server, they are not views based off the system tables, which would require that system tables are regularly updated and then queried, which would mean the introduction of performance issues. DMVs are such that they report off the underlying metadata, which make them faster and more accurate or “real-time”.

Dynamic Management Functions (DMF) are also available, which represent the internal state of the SQL Server as a function of the input provided. DMVs and DMFs are together known as Dynamic Management Objects.

Two types of Dynamic Management Objects are available to the users:

  1. Server scoped dynamic management views, which require VIEW SERVER STATE permission on the server
  2. Database scoped dynamic management views, which require VIEW DATABASE STATE permission on the database

Naming Conventions

Dynamic Management objects are always prefixed with dm_* and reside in the resource database (i.e. the sys schema).

DMVs can be used in T-SQL statements using two, three or four-part naming, just as is the case with a user table. DMFs, on the other hand can be referenced using two or three-part names. Dynamic Management objects cannot be referenced using a one-part name. (which means that you need to reference the DMV dm_exec_requests as sys.dm_exec_requests and not dm_exec_requests).

Securing Dynamic Management Objects

Dynamic Management Objects expose the internal state of any SQL Server. Therefore, they need to be controlled by user security and everyone cannot be provided access to them.

By default, in any Microsoft product, the paradigm is “secure by default”. Keeping this in mind, for SQL Server, the DENY would always take precedence. As discussed earlier, dynamic management objects need VIEW STATE permissions on the server and the database. Hence, all the administrators need to do is DENY the VIEW STATE permissions to wherever required (if server-level access is not required, DENY on the server, but allow on the database or, do the reverse if only server-level access is to be provided.

To know more about dynamic management objects, please visit:http://msdn.microsoft.com/en-us/library/ms188754.aspx

Where to find these in the Object Explorer

Dynamic Management Objects are, as mentioned, prefixed with the sys schema, indicating that they are members of the resource database. But, the resource database is hidden. Does this mean that the dynamic management objects are also hidden? No.

SSMS is a very powerful tool, and we use it so much that we almost take it for granted. The Object Explorer is exactly what it’s name suggests – allows you to explore all SQL Server objects – system or user defined.

Hence, to find dynamic management objects, all we need to do is to expand the master database in the Object Explorer and navigate out to the Views->System Views node.

image

Next, right click on the Views and filter the views listing for views belonging to the sys schema and objects containing dm_ in their name. To learn more about filtering objects in the Object Explorer, read my previous post in the Underappreciated Features series here.

Expand the filtered list and you can see all the DMVs listed for your convenience.

image

You can follow the same process to filter and look for the programmability components (dynamic management functions) in the master database.

Some key differences

It is important to understand that Catalog views are views based on the underlying system tables. Because data in tables is permanently persisted, the values in the catalog views are not reset upon server instance restart.

Dynamic objects, on the other hand report directly off the meta-data in memory, which means that they report data since the last SQL Server restart. This is a very important difference to keep in mind when working with catalog views and dynamic objects.

In Conclusion

Microsoft SQL Server, with each passing release is making increasing the richness and use of Dynamic Management objects and catalog views. I would request all readers to explore these powerful objects and make working with SQL Server a pleasurable experience.

Until we meet next time,

Be courteous. Drive responsibly.

Tags: Development, Administration, T-SQL, Performance Tuning, Tips, SQL Server, #SQLServer, #TSQL, Best Practices, Guidance, DBA, SSMS, Tools and Utilities, Series,


Nakul Vachhrajani
4 · 36% · 11606
2
 
0
Lifesaver
 
 
0
Learned
 
0
Incorrect



Submit

7  Comments  

  • Very good introduction to Dynamic Management Views. I think it will be really helpful if you can write a few follow-up articles that shows some of the commonly used queries that retrieve various pieces of 'life-saving' information from these DMVs.

    commented on May 9 2011 12:17AM
    Jacob Sebastian
    1 · 100% · 32235
  • Thank-you for the feedback. Sure - I will add your feedback to my list of things to write about.

    commented on May 9 2011 12:28AM
    Nakul Vachhrajani
    4 · 36% · 11606
  • Which DMV's are accessible with View Database State permissions?

    commented on May 18 2011 3:18PM
    joeller
    829 · 0% · 35
  • With View Database State permissions, you can only have permissions to view the database information and not the server. You can get more details from the MSDN page here. Great question, by the way. Thanks!

    commented on May 18 2011 11:55PM
    Nakul Vachhrajani
    4 · 36% · 11606
  • Thanks for the quick reply. Yes I had been to that page. I only saw two catagories that specified database in the name. "Database Related" and "Database Mirroring Related" . "Database Related" only has four DMV's and "Database Mirroring Related" only one. Iwas wondering if there were any others not so listed that we could access since we only have "View Database State" rights.

    commented on May 19 2011 8:33AM
    joeller
    829 · 0% · 35
  • Hi Nakul,

    Today i got a chance to have a look on this article about DMVs .Am going to learn some thing new about DMVs by walk through your articles.

    Thanks a lot.

    commented on Feb 21 2013 3:08AM
    Bala Krishna
    83 · 2% · 676
  • @BalaKrishna: Glad you liked it!

    commented on Feb 21 2013 11:38AM
    Nakul Vachhrajani
    4 · 36% · 11606

Your Comment


Sign Up or Login to post a comment.

"Catalog views and DMVs – An introduction – Tricks to find DMVs in SSMS/Object Explorer - Underappreciated features of Microsoft " rated 5 out of 5 by 2 readers
Catalog views and DMVs – An introduction – Tricks to find DMVs in SSMS/Object Explorer - Underappreciated features of Microsoft , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]