Introduction to DMV's and DMF's in SQL Server
2005
Diagnosing problems in SQL Server 2000 has always been a
point of concern from both developers and DBA's. More often than not we would
have had a need to use undocumented and DBCC commands which are sometimes very difficult to understand
too. SQL Server 2005 on the contrary is like a open book, no need
to use bit based operations and undocumented column values. Welcome the introduction of Dynamic Management Views and Fuctions a.k.a DMV's and DMF's.
From the
basic definition these dynamic management views and functions very much replace
all the DBCC command outputs and the pseudo table outputs. Hence it is far more
easier to detect the health of SQL Server using these views and functions. All
these are defined in the sys schema. There are two scope for
these views and function: Server scoped and Database scoped.
Incidentally unlike in SQL Server 2000 now to view these objects the user needs
to have SELECT permissions and VIEW SERVER/DATABASE STATE permissions. Now that
I mentioned about SQL Server 2000, try this yourself, create a readonly
user in a database and select the sysobjects table and check the
results returned in SQL Server 2000 and SQL Server 2005.
There are multiple categories in which these views and
functions have been organized. The below table shows the split:
|
Categories
|
Count
|
|
dm_broker*
|
4
|
|
dm_clr*
|
4
|
|
dm_db*
|
12
|
|
dm_exec*
|
14
|
|
dm_fts*
|
5
|
|
dm_io*
|
4
|
|
dm_os*
|
27
|
|
dm_qn*
|
1
|
|
dm_repl*
|
4
|
|
dm_tran*
|
10
|
So we have 85 of these views and function. To give a
further split, 76 of these are views and 9 of them are functions. So these
information can be queried from the system_objects system catalog table. A
typical query I used was:
select * from sys.system_objects
Where name like 'dm_%' Order by 1
Each of these views and functions have different
parameters or output columns and in the next couple of queries we will try to
find out how to get these values.
-- Getting the column details of the DMV's
Select o.name, c.name, t.name, c.column_id, c.max_length, c.precision, c.scale
FROM sys.system_columns c
INNER JOIN sys.system_objects o
ON c.object_id = o.object_id
INNER JOIN sys.types t
ON c.user_type_id = t.user_type_id
Where o.name = 'dm_os_loaded_modules'
order by 1
In the above query we query
we get the output columns for the DMV (dm_os_loaded_modules) using the
system objects. In the above query we get details like name of the output
column, datatype and other length specific values. Even though this will
not get us the values for the table valued functions. We will have to tweak the
above query for DMF's.
-- Getting the column details of the DMF's
Select o.name, t.name, p.*
FROM sys.system_parameters p
INNER JOIN sys.system_objects o
ON p.object_id = o.object_id
INNER JOIN sys.types t
ON p.user_type_id = t.user_type_id
Where o.name = 'dm_exec_sql_text'
order by 1
In the above query we try to get the parameters for the DMF
(dm_exec_sql_text) using the systtem_parameters system catalog. So the output
would show the above DMF has a parameter @handle. So if we queried this function
for the sql text for a given query in the cache. The handle can be got from
dm_exec_query_stats or other related views.
Conclusion
This is just a starter to understanding the different views
and functions available in SQL Server 2005. As said we have just touched the
surface of what these views can give us. In the future articles we will drill a
step deep into these views and functions.
PS: The values are based on September CTP, the number of
views and functions can change based on the RTM.
|