SQL 2008 R2 System Views

 

 

System views are virtual tables that uncover metadata that relates to many different aspects of SQL Server. Several different types of views target different data needs. SQL Server 2008 offers an extended number of system views and view types that should meet most, if not all, your metadata needs. The available system views can be shown in the Object Explorer in SSMS.  . There are far too many views to cover in detail in this , but we cover each type of view and provide an example of each to give you some insight into their value.



Compatibility Views

Compatibility views were retained in SQL Server 2008 for backward compatibility. Many of the system tables available in SQL Server 2000 and prior versions of SQL Server are now implemented as compatibility views. These views have the same name as the system tables from prior versions and return the same metadata available in SQL Server 2000. They do not contain info that was added after SQL Server 2000. You can find most of the compatibility views in the Object Explorer by looking for system views that have names starting with sys.sys. As an example, sys.syscolumns, sys. syscomments, and sys. sysobjects are all compatibility views. The first part of the name indicates the schema that the object belongs to (in this case, sys). All system objects are part of this sys schema or the INFO_SCHEMA schema. The second part of the name is the view name, which corresponds to the name of a system table in SQL Server 2000. You should transition from the use of compatibility views to the use of other system views, such as catalog views. The scripts that were generated in SQL Server 2000 and reference SQL Server 2000 system tables should continue to function in SQL Server 2008, but this ability is strictly for backward compatibility.



Catalog Views

Using catalog views is the preferred method for returning info used by the MS SQL Server DB engine. There is a catalog view to return info about almost every aspect of SQL Server. The number of catalog views is far too large to list here, but you can increase some insight into the range of info available by looking at the subsequent list, which displays the categories of info covered by catalog views:

Some of the catalog views return info that is new to SQL Server 2008. Examples comprise the Change Tracking and Resource Governor catalog views. Other catalog views provide info that can have been available in prior versions throughout system tables, system procedures, and so on, but the new catalog views expand on the info returned and include elements that are new to SQL Server 2008. To demonstrate the use of a catalog view, let's compare a simple SQL Server 2000 CHOOSE statement that returns object info to a CHOOSE statement in SQL Server 2008 that returns similar info. The subsequent example shows a CHOOSE statement written in SQL Server 2000 to return any stored procedure generated after a given date:

choose o.crdate, o.name
from sysobjects o
where type = 1
p1
and crdate > '1/1/08'
order by crdate, name

Now, compare this CHOOSE statement to one that uses a SQL Server 2008 catalog view. The sys.objects catalog view is a new alternative to the SQL Server 2000 sysobjects system table. The subsequent CHOOSE uses the sys.objects catalog view to return the same type of info as the preceding example:

choose o.create_date, o.modify_date, name
from sys.objects o
where type = 'p'
and (create_date > '1/1/08'
or o.modify_date >= '1/1/08')
order by 1,2, 3

As you can see, the modify_date column has been added to the CHOOSE statement. This column did not exist with the sysobjects system table. The addition of this column allows you to identify objects that were generated as well as objects that were modified or altered. Let's look at an example of using a catalog view to return the same kind of info returned in SQL Server 2000 with a system procedure. The handy sp_helpf ile system procedure returns info about DB files associated with a given DB. This SQL Server 2000 procedure is still available in SQL Server 2008. An alternative to this procedure is the new sys.master_files catalog view. This view returns all the info that sp_helpfile returns and more. The subsequent example shows a CHOOSE statement using the sys .master_files catalog view to return the DB files for the AdventureWorks2008R2 DB:

choose *
from sys.master_files
where db_name(DB_id) = 'AdventureWorks2008R2'

You have the distinct advantage of being able to choose the DB files for all the DBs on your server by using this catalog view. You can also tailor your CHOOSE statement to isolate DB files based on the size of the DB or the location of the physical DB files. As an example, to return all DB files that are found somewhere on your C drive, you could use the subsequent CHOOSE:

choose db_name(DB_id), physical_name
from sys.master_files
where physical_name like 'c:\%'

There are plenty of catalog views that provide info about SQL Server. When you are looking to return info about SQL Server modules, you should look to the catalog views first. These views provide a great deal of flexibility and allow you to isolate the specific info you need.



Info Schema Views

Info schema views provide another system table-independent option for accessing SQL Server metadata. This type of view was available in prior versions of SQL Server. Using info schema views is a viable alternative for accessing SQL Server metadata from a production app. The info schema views enable an app that uses them to function properly even though the underlying system tables can have changed. Changes to the underlying system tables are most prevalent when a new version of SQL Server is released (such as SQL Server 2008) , but changes can also occur as part of service packs to an current version. The info schema views also have the advantage of being SQL-92 compatible. Compliance with the SQL-92 standard means that SQL statements written against these views work with other DBMSs that also adhere to the SQL-92 standard. The SQL-92 standard supports a three-part naming convention, which SQL Server has implemented as DB.schema.obj ect. In SQL Server 2008, all the info schema views are in the same schema, named INFO_SCHEMA. The subsequent info schema views or objects are available:

When you refer to info schema views in a SQL statement , you mus t use a qualified name that includes the schema name . As an example, the subsequent statement returns all the tables and columns in a given DB, using the tables and columns info schema views:

choose t.TABLE_NAME, c.COLUMN_NAME

from INFO_SCHEMA.TABLES t

join INFO_SCHEMA.COLUMNS c on t.TABLE_NAME = c.TABLE_NAME

order by t.TABLE_NAME, ORDINAL_POSITION

Fortunately, the name s of the info schema views are fairly intuitive and reflect the kind of info they contain. The relationships between the info schema views can be derived from the column name s shared between the tables.



Dynamic Management Views

Dynamic management views (DMVs), which were introduced in SQL Server 2005, provide a simple means for assessing the state of a server. These views provide a lightweight means for gathering diagnostic info without the heavy burden associated with the tools available in SQL Server 2000. The SQL Server 2000 diagnostic tools, such as heavy Profiler traces, PerfMon, dbcc executions, and pssdiag, are still available, but oftentimes, the info returned from the DMVs is enough to determine what can be ailing a SQL Server machine. An extensive number of DMVs are available in SQL Server 2008. Some DMVs are scoped at the server level, and others are scoped at the DB level. They are all found in the sysschema and have names that start with dm_. Table 7.3 lists the different types of DMVs. The DMVs in this table are categorized based on function as well as the starting characters in the DMV names. The naming convention gives you an easy means for identifying the type of each DMV.

 

Category Name Prefix Information Captured
Auditing dm_audit New Audition information
Service Broker dm_broker Server Broker statistics, including activated tasks and connections
Change Data dm_cdc New Change Datae Capture information
CLR dm_clr CLR information, including the CLR loaded assemblies
Crytographic dm_cryp Security related data
TDE dm_database Transparent Data Encryption
Database dm_db Databases and database objects
Execution dm_exec Execution of user code
Full-Text dm_fts Full-Text Search information
I/O dm_io Input and output on network disks
Operating System dm_os Low-level operating system information, including memory and locking information
Provider dm_provider Extensible Key Management
Query Notification dm_qn Active Query Notification subscriptions
Replication dm_repl Replication information, including the articles, publications, and transaction involved in replication

 

To illustrate the value of the DMVs, let's look at a performance scenario and compare the SQL Server 2000 approach to a SQL Server 2008 approach using DMVs. A common performance related question is "What stored procedures are executing most frequently on my server?" With SQL Server 2000, the most likely way to find out is to run a Profiler trace. You need to have a Profiler trace that has already been running to capture the stored procedure executions, or you need to create a new trace and run it for a period of time to answer the performance question. The trace takes time to create and can affect server performance while it is running. With SQL Server 2008, you can use one of the DMVs in the execution category to answer the same performance question. The subsequent example uses the sys.dm_exec_query_stats DMV along with a dynamic management function named dm_exec_sql_text. It returns the object IDs of the five most frequently executed stored procedures, along with the actual text associated with the procedure:

choose top 5 q.execution_count, q.total_worker_time,
s.dbid, s.objectid, s.text
from sys.dm_exec_query_stats q
CROSS APPLY sys.dm_exec_sql_text (q.sql_handle) s
ORDER BY q.execution_count desc

The advantage of using a DMV is that it can return past info without having to explicitly create a trace or implement some other performance tool. SQL Server automatically caches the info so that you can query it at any time. The collection of the data starts when the SQL Server is started, so you can get a good cross-section of info. Keep in mind that your results can change as the server continues to collect info throughout time. Many of the performance scenarios such as those that relate to memory, CPU utilization, blocking, and recompilation can be investigated using DMVs. You should consider using DMVs to address performance problems before using other methods in SQL Server 2008. In many cases, you can be able to avoid costly traces and glean enough info from the DMV to solve your problem.

DMVs are also a great source of info that does not relate directly to performance. As an example, you can use the dm_os_sys_info DMV to gather vital server info, such as the number of CPUs, the amount of memory, and so on.