Metadata Views - SQL Server 2008

Most SQL Server DBAs do not exactly relish the idea of having to crawl through the system tables. The tasks of remembering the various types of id columns (which are often misleadingly named) and attempting to decode columns like xtype are often error-prone and not a productive use of the DBA’s time. For those of you who are still used to accessing system tables directly, you may be quite comfortable issuing queries like the following:

SELECT AS theTable, AS theColumn, AS theType
FROM sysobjects so
JOIN syscolumns sc ON =
JOIN systypes st ON sc.xtype = st.xtype
so.type = 'U'

Not only is this query tedious to write and read, the fact that you are accessing system tables directly is a big Microsoft no-no. The reason Microsoft is so strict on this policy has to do with the need for internal changes. If the SQL Server developers document an object or a function like a table, view, or stored procedure, they cannot simply change it at the next release to comply with a new feature, as this would break a lot of users’ applications. Thus, they must formally announce the feature or function as deprecated and wait three releases before they can actually change it or remove it from the product. To give Microsoft the flexibility of changing things freely under the covers, SQL Server exposes a series of catalog views, which are views on top of some system objects. The idea is that the catalog views will never change, so if you write your database applications and scripts using these views, then nothing will break when you upgrade your database application to the next version of SQL Server.

Taking our previous system table query example, let’s see this in terms of catalog views:

SELECT AS theTable, AS theColumn, AS theType
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.types ty ON c.system_type_id = ty.system_type_id

It’s not just the lack of a WHERE clause that makes this query more readable; it’s the little differences, like primary key column names that actually make sense!

Starting with SQL Server 2005, the system tables are deprecated, having been replaced by two new sets of views. For backward-compatibility purposes, the system tables from previous versions of SQL Server are also still around, in the form of a third set of views. The system tables themselves are now hidden from direct user contact, but should you have legacy code written against them, you’ll find that queries will still work and applications will not break; the views do a good job of mimicking the tables’ functionality. However, the new metadata views offer much greater usability.

Tables describing objects (for example, sysobjects and syscolumns) have been replaced by a set of views called the catalog views. Tables describing system state (for example, syscacheobjects and syslocks) are now represented by a set of views called the dynamic management views. And the older tables themselves can now be found in a set of views appropriately called the compatibility views. The ANSI standard INFORMATION_SCHEMA views are also still around, but due to the fact that so much of the functionality in SQL Server is not ANSI-compliant, these views fail to provide much value in SQL Server.

Compatibility Views

All of the SQL Server system tables have been migrated into a collection of views in the sys schema called the compatibility views. You should attempt to migrate existing code away from the compatibility views, and start using catalog views instead.

Querying the views works the same as with previous versions of SQL Server. SELECT * FROM sysobjects still returns information about objects in the current database. SELECT * FROM sysindexes still returns information about indexes. However, some columns have been deprecated, so you should carefully test existing code before migrating it to SQL Server 2008. For instance, in SQL Server 2000, the keys column of the sysindexes system table contained a list of the columns that made up the index. But in SQL Server 2005 and 2008, that column will always be null. Other columns in the sys.indexes view that are not quite backward-compatible are dpages, reserved, used, rowmodctr, maxirow, and statblob. Code that uses these columns should be rewritten to use the sys.indexes view.

Catalog Views

The catalog views are repositories for “static” metadata. They contain data about both server-wide and database-specific objects, including logins, tables, and stored procedures, as opposed to more “dynamic” data, such as locks and the state of the procedure cache. You’ll find that they are both more comprehensive and user-friendly than the system tables were. For instance, the sysindexes system tablecontained a column called indid that would hold various codes depending on the index type: 0 meant the table was a heap; 1 was a clustered index; and a value greater than 1 was a nonclustered index, unless it was 255, in which case it wasn’t an index at all, but an indication that the table had a large object (TEXT or IMAGE) column!

With catalog views, these cryptic values are gone, and the indid column has been replaced in the sys.indexes catalog view by a column called type_desc. This column can contain the following self-explanatory character values: HEAP, CLUSTERED, NONCLUSTERED, and XML—quite an improvement.

To find a list of all heap tables (tables without clustered indexes) in a SQL Server database, use the following query:

OBJECT_NAME(object_id) AS theTable
FROM sys.indexes
type_desc = 'HEAP'

Many other improvements have been made. For instance, almost all code numbers have been replaced by English-character strings, and bitmasks and other internal structures have been replaced by normalized tables. One enhancement in particular that many DBAs will enjoy is the addition of a column called modify_date to the sys.objects views and other views that inherit from it (including sys.procedures and sys.views). No more trying to pinpoint the last time someone ran an ALTER on one of the database objects.

Table lists some of the key system tables and the catalog views that now expose the same information.

System Tables and Their Catalog View Equivalents

System Tables and Their Catalog View Equivalents

Table is by no means a comprehensive list of the available catalog views. Virtually every type of object available in SQL Server has an associated catalog view. To see a complete list, navigate to the System Views node in Object Explorer in SQL Server Management Studio, as shown in Figure (note that in this image, Object Explorer’s filter is being used to limit results to objects in the sys schema). Throughout the rest of this chapter, we’ll mention various catalog views in the context of helping to manage the new features discussed.

Catalog views appear in Management Studio under the System Views node in Object Explorer.

Catalog views appear in Management Studio under the System Views node in Object Explorer.

Dynamic Management Views and Functions

Whereas the catalog views contain data about “static” objects, the dynamic management views and functions help the user investigate the ever-changing state of the server. Note that the dynamic management functions are really nothing more than parameterized views—they are not used for modifying data. These views and functions are, like the catalog views, collected in the sys schema, but they are prefixed with dm_. Although these views also replace and improve upon system table functionality from previous versions of SQL Server, the change that will excite most DBAs is the number of new metrics now available.

The dynamic management views have been named extremely well for browsing. Those prefixed with dm_exec contain data relating to actively executing processes. dm_os views contain operating system–related data. dm_tran views refer to transaction state data. dm_broker and dm_repl views contain data for Service Broker and replication, respectively. The dynamic management views are available in SQL Server Management Studio under the System Views node, as shown in Figure (note that in this image, Object Explorer’s filter is being used to limit results to objects with dm_ in their name).

Dynamic management views appear in Management Studio under the System Views node in Object Explorer.

Dynamic management views appear in Management Studio under the System Views node in Object Explorer.

Viewing Query Plans

One of the most useful functions is dm_exec_query_plan. This function shows an XML representation of query plans for cached and active queries, and can take as input the plan_handle value exposed by three of the dynamic management views. The first of these views, dm_exec_requests, exposes information about which queries are active at the time the view is queried. The second, dm_exec_query_stats, stores aggregate statistics about stored procedures and functions, such as last execution time and total working time—it’s a very useful view! And the dm_exec_cached_plans view replaces the older syscacheobjects system table, with data about compiled query plans.

As an example, to see the query plans for all active requests with valid plan handles, the following T-SQL could be used:

SELECT thePlan.query_plan
FROM sys.dm_exec_requests
OUTER APPLY sys.dm_exec_query_plan(plan_handle) thePlan
WHERE plan_handle IS NOT NULL

Once an XML query plan has been retrieved, it can be saved to a file with the extension .sqlplan. Double-click the file, and it will open in SQL Server Management Studio, displayed as a graphical query plan. This feature will prove quite useful for both archiving baseline query plans before performance-tuning work and for remote troubleshooting. The XML can be opened with any instance of SQL Server Management Studio, and it does not require connectivity to the server that generated it for graphical display.

Monitoring Memory Using Dynamic Management Views

Although SQL Server manages memory automatically, it is still an important job for DBAs to monitor their SQL Server implementations and identify memory-usage trends. These trends may show potential operational failures or performance degradation as more and more applications are requesting resources. SQL Server 2008 introduces a series of memory-related dynamic management views to aid in tracking memory usage.

Operating System Memory

SQL Server is limited by the memory restrictions imposed by the operating system. Two new dynamicmanagement views enable you to retrieve operating system–related memory usage information:
sys.dm_os_sys_info and sys.dm_os_sys_memory.

sys.dm_os_sys_info displays general memory information, such as the amount of physical and virtual memory available. sys.dm_os_sys_memory provides a lot more information. A query of this view will reveal not only the physical memory available, but also the state of the system via the memory resourcenotification bits. The memory-resource notification bits are bits that are set by the operating system to indicate the state of the memory resource (via the high memory signal and the low memory signal). The following are some columns in the sys.dm_os_sys_memory view:

Operating System Memory

SQL Server Process Memory

The sys.dm_os_process_memory view provides a complete picture of the process address space of SQL Server. The values are obtained through direct calls to the operating system and, in general, are not altered by SQL Server internal methods. Information that can be obtained from this dynamic management view includes the amount of virtual address space that is currently free, the number of page faults incurred by the SQL Server process, the percentage of committed memory in the working set, and many other interesting data points.

SQL Server’s Memory Manager

The memory manager for SQL Server is internal functionality to manage memory allocations within the SQL Server process. Observing the difference between process memory counters from sys.dm_os_process_memory and various internal counters from other dynamic management views, such as sys.dm_os_memory_brokers, can indicate memory use of external components in the SQL Server memory space. Many other dynamic management views dig even deeper into the core of SQL Server’s memory. It takes a lot of experience and knowledge to fully understand and utilize the information returned by these powerful views. If you are feeling adventurous, you can reference SQL Server Books Online for the complete description of functionality for those dynamic management views previously mentioned and three new memory-related views in SQL Server 2008:
sys.dm_os_memory_nodes, sys.dm_os_memory_brokers, and sys.dm_os_nodes.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

SQL Server 2008 Topics