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.

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.

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.