Introduction

You fought for days to get your ASP .NET web application in perfect shape. You optimized every detail of the interface. No unnecessary round trips to the server, session state is disabled when you don’t need it, server controls are used only if necessary, buffering is enabled, exceptions are well handled – everything is perfect. Today you start to optimize the back-end. You have the perfect tools for that – System Monitor, SQL Server Profiler and Dynamic Management Views (DMV). The first two are well known from SQL Server 2000 but with DMVs you need some help to get started. While there are also many other tools out there, these are the most common.

The System Monitor (or Performance Monitor in Windows NT) is used primarily to track resource usage like CPU activity or memory usage. There is a set of predefined objects and counters for monitoring events. A distinct characteristic of the System Monitor is that collected data will have the form of counts and rates – you will get the number of active transactions, the number of logins or any other counter value. No other details, it will not tell you who is connected to your database, or what those active transactions are doing.

The other tool, SQL Server Profiler, tracks Database Engine, Analysis Services and Integration Services events using traces. You can capture events and save them to a file or a table. The common use for this tool is auditing, monitoring stored procedures performance, collecting sample events for stress testing or for tuning the physical database design with Database Engine Tuning Advisor, etc. One of the great features of SQL Server Profiler is the correlation of trace events with performance monitor counters offering a greater insight.

Another tool and the subject of this article, Dynamic Management Views (DMVs), offer a view of the internal structures of your server. Not just detailed information about memory, execution, transactions, scheduling, locking, I/O but also about components of the server like Service Broker, Common Language Runtime, Replication, Full Text Search or Query Notifications. In a nutshell DMVs will tell you anything you need to know about your server and to make things even better you will get the information as a table.

This article explores the use of DMV’s. I will use some basic examples to get you started. This is followed up by going through the summary reporting that is now available. Lastly I will put some DMV’s to use in some real life problem solving samples.

System Requirements

To run the sample code for this article you should have:

• Any edition of SQL Server 2005


• The AdventureWorks sample database

About the Sample Code

The sample download for this article contains a series of SQL scripts that highlight the various DMVs described throughout the article To run the scripts you can use the new client tool for SQL Server 2005 – SQL Server Management Studio.

If you are unsure how to install the AdventureWorks sample database, see the Related Links section for details.

An Overview of Dynamic Metadata

Dynamic metadata is not a new concept. If you are familiar with SQL Server 2000 you may recall virtual tables such as sysprocesses (contains information about processes running on the server) or syscacheobjects (shows how the cache is being used). Virtual tables represent dynamic metadata in SQL Server 2000. Dynamic metadata can be defined as simple non-persisted information about the current state of the server (information like current locks, memory allocation, current threads, tasks and connections). In contrast, static metadata is usually persisted on disk and offers information about the server and database objects such as logins, defined linked servers, databases, database files, users, tables, views, columns, indexes etc.

In SQL Server 2005, dynamic metadata is exposed via Dynamic Management Views and also Dynamic Management Functions (DMFs) which can accept parameters. I will refer to both of them as DMVs to avoid repeating DMVs and DMFs throughout the article.

For static metadata you can use catalog views like sys.assemblies for example. This catalog view returns one row for every assembly registered in a specific database. Both categories of metadata views share the same schema – the sys schema. A very interesting fact about the objects of sys schema is their physical location. Though you will find sys schema’s objects in every database, their actual location is the Resource database, a read-only database having the physical file name Mssqlsystemresource.mdf. Before I dig into the Resource database, let me go over the kernel built into SQL Server 2005.

DMV High Level Architecture

The kernel of SQL Server 2005 was completely re-architected to be a user mode operating system known as SQLOS (SQL Server Operating System). SQLOS is highly configurable, has a powerful API and provides services like memory management, exception handling, deadlock detection, hosting for the Common Language Runtime etc.

Building the new kernel brought the opportunity to expose the internal structures and statistical data (through DMVs), providing advantages in tuning (for queries, memory, indexes), easier diagnosis and help in troubleshooting performance. Another great advantage is for support teams at Microsoft: In SQL Server 2000, in order to diagnose a problem, a physical dump was required to provide the internal structures of the SQL Server process memory. In SQL Server 2005, in most cases, DMVs eliminate the need for physical dumps, helping the support team to make a diagnosis more quickly and simply.

Let’s get back to the Resource database for a moment. To make the installation of a service pack easier, all system objects are physically located in one place, the Resource database. This also facilitates the rollback of a service pack. Figure 1 illustrates the architecture of SQL Server 2005. You can see the DMVs in your user databases, but their definitions actually reside in the Resource database. This method is used not to hide anything but complexity. When you apply a service pack, behind the scenes will be a simple copy operation and the new Resource database files will overwrite the old files instead of modifying the system objects in each database. Another reason for exposing system objects as views instead of system tables is to avoid breaking code when the structure of the system tables is changed.

Figure 1. Architectural Blueprint of DMVs and DMFs

Getting Started: A DMV Query

Let’s start out by using a basic DMV to see how it works. I will show you how to obtain information about the urgently held locks. To do this, open SQL Server Manager (and keep it open, as you’ll be using it again) and run the following query:

— Listing01.sql

— change database context

USE AdventureWorks;

GO

— start a transaction

BEGIN TRAN

UPDATE Person.ContactType 

SET Name = Name

GO

— use a DMV

SELECT * FROM sys.dm_tran_locks

— rollback of transaction

ROLLBACK TRAN

This should return something like the following:

—Partial Results

resource_type resource_associated_entity_id request_mode request_session_id

——————- ——————————————- —————— —————————

METADATA      0                             Sch-S        51

DATABASE      0                             S            51

OBJECT        437576597                     IX           51

PAGE          72057594043367424             IX           51

KEY           72057594043367424             X            51

Now let’s put the statements under the microscope and see how it works. I started a transaction using BEGIN TRAN and updated the table ContactType table with 20 rows. The transaction will help to maintain some locks so you can see them with the sys.dm_tran_locks DMV. You should see displayed all the columns of the view and that the number of resulted rows is very small. The columns of the result set describe the resources and requests for resources. The category of the column (for most of them) is denoted using resource and request prefixes. You can find a detailed explanation for all the columns in the SQL Server Books Online. I have included just four columns (out of nineteen, to make the results readable). The column resource_associated_entity_id, a bigint data type, may need additional explanation. It retrieves the ID of an entity from the database. The locked resource is associated with this entity. The ID represents an object ID if the resource type is an OBJECT, a HoBt ID (Heap or B-tree ID) for KEY and PAGE resource types. To obtain the object name a case statement that uses the resource_type column can be used. To see this run the previous example with the following modification to the select query:

— Listing02.sql

— use a DMV

SELECT resource_type,

(CASE 
    WHEN resource_type = ‘OBJECT’ 
        THEN object_name(resource_associated_entity_id)
    WHEN resource_type=‘DATABASE’ 
        OR resource_type=‘FILE’ THEN ‘N/A’
    WHEN resource_type=‘KEY’ 
        OR resource_type=‘PAGE’ 
    THEN 
    (
        SELECT object_name(object_id) 
        FROM sys.partitions 
        WHERE hobt_id=resource_associated_entity_id
    )
    ELSE ‘Undefined case’
    END) AS objname, 
	

		
FROM sys.dm_tran_locks

This should give you something like the following query results (again I’ve just displayed some of the results here):

resource_type     objname              request_mode    request_session_id

——————-     ——————-        ——————    —————————

METADATA          Undefined case       Sch-S           51

DATABASE          N/A                  S               51

OBJECT            ContactType          IX              51

PAGE              ContactType          IX              51

KEY               ContactType          X               51

…

If nothing else is running on your AdventureWorks database, you will obtain for the object name, the name of the table – ContactType.

Locking

Just in case you are not familiar with locking in SQL Server, I provide a quick overview here. As you most likely already know SQL Server allows multiple users to read and modify shared data at the same time. To avoid conflicts SQL Server uses locking. The database engine can lock a resource at multiple levels from single row to an entire database. Locking at the row level will increase concurrency but will generate more overhead, compared to locking at a higher level. Below is a list of some of the resources that can be locked:

• RID – (row identifier) is used to lock a row in a heap (a heap is a table without a clustered index)


• KEY – used to lock a row within an index


• PAGE – the fundamental unit of storage in SQL Server (this is 8 KB in size)


• EXTENT – a contiguous group of eight pages


• HOBT (heap or B-tree) – protects a B-tree structure of an index or a heap


• METADATA – a metadata lock (on a part of a catalog information for example)


• OBJECT – a lock on an object such as table, view or stored procedure


• DATABASE – a lock on a database

The complete list of resources can be found in Books Online, a link is provided in the Related Links accompanying this article.

As you use DMVs, you will notice that all DMVs are named starting with the prefix, dm, followed by their category. In this particular case sys.dm_tran_locks belong to sys.dm_tran_* group, used for transaction related DMVs. The other categories are as follows:

General server DMVs

• dm_exec_* for code execution related DMVs


• dm_os_* for SQL Operating System related DMVs


• dm_io_* for I/O related DMVs


• dm_db_* for database and database objects related DMVs

Component level DMVs

• sys.dm_clr_* for Common Language Runtime related DMVs


• dm_repl_* for Replication DMVs


• dm_broker_* for Service Broker DMVs


• dm_fts_* for Full-Text Search DMVs


• dm_qn_* for Query Notifications

I don’t want to duplicate existing information that you can read in the online documentation, so I won’t spend time explaining the resulting columns for every DMV used; instead I’ll aim to give you an idea on what each category of DMVs has to offer, and show you as many as possible in action. The complete definition of every column for each DMV (there are more than 80 of them) can be found in the SQL Server 2005 Books Online which does an excellent job in explaining each of them.

Some DMV Categories

Let’s have a quick look at some of the categories of DMV that are available:

Execution

The dm_exec_* category groups together execution related DMVs. They can give you information about current sessions (sys.dm_exec_sessions), current connections (sys.dm_exec_connections), current requests (sys.dm_exec_requests), opened cursors (sys.dm_exec_cursors) or cached query execution plans (sys.dm_exec_cached_plans). You will see some DMVs from the this category at work later in this article.

SQLOS

The largest category of DMVs is dedicated to the SQL Server Operating System (SQLOS) – a component of the database engine that is responsible amongst other things for managing scheduling, resource monitoring, memory management, exception handling. In the second half of this article I will be using the sys.dm_os_memory_clerks DMV to display memory allocation.

I/O

A smaller category of DMVs (dm_io_*) handle I/O operations. You can use them to get I/O statistics for your database files (sys.dm_io_virtual_file_stats), to see the pending I/O requests (sys.dm_io_pending_io_requests), to obtain the drive name of the shared drives in case of a clustered server (sys.dm_io_cluster_shared_drives) or to get a list of backup devices (sys.dm_io_backup_tapes).

Database

The database related DMVs category (dm_db_*) will help you manage indexes, get information about a tempdb database or about your database partitions.

Components

The component level categories deal with several components of SQL Server such as replication, common language runtime integration, the new service broker and the full-text search and query notifications. For example you can see the application domains on your server (sys.dm_clr_appdomains) or get information about replicated transactions (sys.dm_repl_traninfo), and the list goes on.

Using Summary Reports

In this second sample I will display the same locking information as in the first sample but using a different technique that produces a friendly interface. You will see how to get the same results enhanced by a graphical representation without any coding, just by a making few mouse clicks.

Start by going to SQL Server Manager and run the following query:

— Listing03.sql

USE AdventureWorks;

GO

BEGIN TRAN

UPDATE Person.ContactType 

SET Name = Name

GO

It’s just a portion from the first sample query. After you run it, press F8 to display the Object Explorer (or use the View menu) and click on the AdventureWorks database (navigate using the Server name – Databases). With AdventureWorks selected in the Object Explorer, press F7 (or use the View Menu – and click Summary) to display the summary page. On the summary page use the Report drop-down list and select All Transactions. As a result, you will see a nice report with details about the current transactions as shown in Figure 2.

Figure 2. Summary Page “ All Transactions Report

Behind the scenes, it is the DMVs that are the source of the report data. You don’t have to take my word for it, if use SQL Server Profiler, refresh the report and you can see them. When I encountered DMVs the first time, I had to use SQL Server Profiler traces as a replacement for the then almost nonexistent documentation. Be sure to end the transaction (otherwise the ContactType table will remain exclusively locked) by running the following statement in the same query window:

— rollback transaction

ROLLBACK TRAN

Summary Reports Under the Hood

Summary reports are worth taking a look at since they provide the power of DMVs without requiring more effort than a few mouse clicks. I would suggest you spend some time with them in order to become familiar with them. The set of the reports available will change as you navigate around the Object Explorer hierarchy. Probably the most useful will be the reports located at the server and database level.

You may wonder what is behind all the SQL in the summary reports. From the previous section, you already know that DMVs are one of the data sources for the reports. Another source is the default trace, a lightweight trace that logs mainly the changes in the server’s configuration. The default trace is limited to 100 MB and is stored in the MSSQLLOG folder using a rollover trace file. The use of the default trace is possible due to an innovation brought by SQL Server 2005 – the ability to read a still active trace. You can read the trace using the function fn_trace_gettable(). The next set of queries will explore the use of this function. The first query returns general information about the default trace (that has the ID 1). In the information returned, you will find the exact path for your instance. Replace the path from the second query with the location returned by the first one. On my machine, SQL Server 2005 is installed in the default location and as a default instance. The last query will return all the events recorded by the default trace.

— Listing04.sql

— get general information

SELECT * 

FROM fn_trace_getinfo(1) 

GO

This query gives these results (I’ve formatted them to make the data clearer in the HTML page)

traceid property value

———- ———— —————————————————————————————-

1       1        2

1       2        C:Program FilesMicrosoft SQL Server
                    MSSQL.1MSSQLLOGlog_93.trc

1       3        20

1       4        NULL

1       5        1

These results are, in row order: The trace options (in this case 2 means that is a rollover trace), the file name, the maximum size, the stop time if any, and the current trace status

Now try the following to read the default trace

SELECT * 

FROM fn_trace_gettable

(‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGlog.trc’, default)

GO

You’ll see something like this

TextData DatabaseID NTUserName LoginName     EventClass

———— ————— ————— ——————- —————

NULL     7          Cristian   TESTCristian 46

…

This gives the information recorded by the trace, and you can see an event that represents the creation of an object

Finally, to get a complete list of events:

SELECT T.eventid, E.name

FROM fn_trace_geteventinfo(1) T
  JOIN sys.trace_events E
  ON T.eventid = E.trace_event_id

GROUP BY T.eventid, E.name

GO

Here’s some of the results you should see from this, it’s basically a listing of all events recorded by the default trace:

eventid     name

—————- ——————————————-

18          Audit Server Starts And Stops

20          Audit Login Failed

22          ErrorLog

46          Object:Created

47          Object:Deleted

…

Summary reports could be the subject of an entire article themself, but we will move on to the second section of the topic at hand.

The last two samples have shown the basics of DMVs as well as the use of summary reports. We will use this going forward into the rest of the article.

DMV Practical Usage Scenarios

In this section I will explore scenarios that can be applied in your applications and provide a more practical usage of DMV’s.

Indexes

Creating indexes for your application can improve performance of your queries. But as the universal truth says "there is no free lunch", and adding indexes is unfortunately not an exception. Operations that modify data, have to also modify indexes too and the result will be reduced performance. That does not mean that you should not use indexes. You just have to monitor the usage of your indexes to see if and how they are used. The sys.dm_db_index_usage_stats DMV will help you in monitoring the usage of indexes.

The query below will illustrate how to use this DMV:

— Listing05.sql

USE AdventureWorks

GO

SELECT FirstName, LastName

FROM Person.Contact

WHERE ContactID = 100

SELECT index_id, user_seeks, user_scans, user_lookups, user_updates

FROM sys.dm_db_index_usage_stats 

WHERE database_id = DB_ID(‘AdventureWorks’) 

AND object_id = OBJECT_ID(‘Person.Contact’)

Here’s some typical results

FirstName LastName

————- ————-

Jackie    Blackwell

index_id user_seeks user_scans user_lookups user_updates

———— ————— ————— —————— ——————

1        1          0          0            0

The first query returns a row using a seek operation.

After the first run of the complete batch, you could run just the first query a couple of times and after that run the second query again. You will see that the value of the user_seeks column will be incremented. Each individual query execution that uses operations like seek, scan or lookup will increment by one the corresponding counters. When the index is modified using insert, update, or delete operations on the underlying table, the user_updates counter will be incremented. All of these counters will be initialized each time the server is restarted. How can you benefit from using this DMV in production? You can poll it periodically and see if your indexes are really being used and if they are used as many times as the index is modified. This will help you make a decision if the index is worth the cost of having it.

Another potential problem for performance is index fragmentation. If data is modified – and it usually is, eventually the performance of indexes will degrade. In SQL Server 2000, the DBCC SHOWCONTIG statement displays index fragmentation. To use it efficiently you have to create a temporary table in which to insert the results. The replacement for this statement in SQL Server 2005 is the DMF sys.dm_db_index_physical_stats. The sys.dm_db_index_physical_stats DMF is a table-valued function with the following syntax:

sys.dm_db_index_physical_stats ( 
    { database_id | NULL }
    , { object_id | NULL }
    , { index_id | NULL | 0 }
    , { partition_number | NULL }
    , { mode | NULL | DEFAULT }

)

In order to identify a specific index you have to specify its ID, the ID of its containing object (table or view) and the ID of the containing database. You have the option to see information about all databases, all tables and views, or for all indexes of a table or view by specifying NULL for

database_id,object_id or index_id parameter. If the index is portioned you can see the information for a specific partition or for all partitions if you specify NULL for the partition_number parameter. The last parameter, mode, is used to specify the level of scanning performed. The following example will display information about all the indexes for the Person.Contact table, all partitions and with the default mode of scanning (LIMITED mode – scans the smallest number of pages and thus is the fastest mode).

— Listing06.sql

SELECT * 

FROM sys.dm_db_index_physical_stats

(DB_ID(‘AdventureWorks’),OBJECT_ID(‘Person.Contact’),NULL,NULL,DEFAULT)

Partial results might look like this:

database_id index_id  index_type_desc  avg_fragmentation_in_percent   

—————- ————  ———————— ——————————————

6           1         CLUSTERED INDEX  0.536672629695885

Troubleshooting Performance

In this section I will investigate several causes for poor query performance – CPU, memory and I/O operations. DMVs can provide a hand in identifying the cause for the resource bottleneck. Let’s take a look at CPU first. The processor can be affected by various things, such as excessive compilation and recompilation, non-optimal query plans, and in some situations the workload can simply be too heavy. If you need a list with the top ten CPU consuming queries, you can use the following DMV:

— Listing07.sql

SELECT TOP 10 

total_worker_time AS [CPU Time], *

FROM sys.dm_exec_query_stats 

ORDER BY [CPU Time] DESC

Here’s typical partial results:

CPU Time  sql_handle  

————  ————————————————————————————

223496    0×0200000007AAD215072E14483BD3112E623F72479A76D223

207701    0×0200000097EA4D17A711D29225C8D415A9C6C047557F6913

58008     0×0300FF7FC3D4DD0436CC1B00ED9600000000000000000000

This uses the sys.dm_exec_query_stats DMV to get the total CPU time used by the queries (with the plans still in cache). If you want to see the sql text for the queries you can use the sys.dm_exec_sql_text DMV by simply adding a new column to the query with the following code:

— Listing08.sql

SELECT TOP 10

…,

(SELECT CASE WHEN statement_end_offset = -1 
 THEN LEN(CONVERT(nvarchar(max">SUBSTRING, text)) * 2 
 ELSE statement_end_offset END 
 -statement_start_offset)/2) 
 FROM sys.dm_exec_sql_text(sql_handle)
 ) AS text_of_query

…

Let’s spend some time on compilations and recompilations. The generation of a query plan is CPU intensive so ideally we would want to see a plan reused as much as possible. In reality things are different, changes in data or poor query design can cause recompilations, and that is OK to some extent – we can live with some recompilations. Excessive recompilations are a different thing that can affect the CPU pretty seriously. One improvement in SQL Server 2005 is statement level recompilation in stored procedures. Recompiling just a statement is a better approach then recompiling an entire stored procedure, especially when the stored procedure is large. To find out how recompilation is affecting your server you can use System Monitor, SQL Server Profiler and of course DMVs. I will give you two examples, the first one returning the time spent by SQL Server with optimizations and the second for listing top 10 recompiled stored procedures.

— Listing09.sql

SELECT * 

FROM sys.dm_exec_query_optimizer_info

Partial results:

counter       occurrence value

——————- ————— —————————

Optimizations 35	     1

elapsed time  35	     0.0273481410714286

final cost    35	     0.614029214024793

…

Now try this:

SELECT TOP 10

s.text AS query_text,

plan_generation_num,

execution_count,

db_name(dbid) as db_name,

object_name(objectid) as object_name

FROM sys.dm_exec_query_stats 

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s

WHERE plan_generation_num > 1

ORDER BY plan_generation_num DESC

For this second query I used the new CROSS APPLY operator with sys.dm_exec_sql_text to get the sql text. The CROSS APPLY operator applies the sys.dm_exec_sql_text function to every row of sys.dm_exec_query_stats view.

The next resource you should investigate is memory. For that the sys.dm_os_memory_clerks DMV is the best choice. The sys.dm_os_memory_clerks returns all memory clerks currently active. If you use CLR integration you might want to take a look at the value for the CLR memory clerk (MEMORYCLERK_SQLCLR). If you still use extended stored procedures, the responsible memory clerk is MEMORYCLERK_SQLXP. Let’s see the list of top 10 memory clerks ordered by the amount of allocated memory:

— Listing10.sql

SELECT TOP 10 

type, 

SUM 
 AS [Allocated Mem (KB)]

FROM sys.dm_os_memory_clerks

GROUP BY type

ORDER BY [Allocated Mem (KB)] DESC

GO

Partial results:

type                    Allocated Mem (KB)

———————————  —————————

MEMORYCLERK_SOSNODE	    9056

CACHESTORE_SQLCP	    4072

MEMORYCLERK_SQLGENERAL  2384

…

The last bottleneck I will refer to is an I/O bottleneck. To diagnose possible I/O bottlenecks we have available both the I/O related DMVs and executed related DMVs. As a sample I will use a DMV that you already see in a precedent example – sys.dm_exec_query_stats, to generate a list with the top 10 queries ordered by the total I/O generated. Only the queries that have the execution plan still in cache will be listed.

— Listing11.sql

SELECT TOP 10 
       total_logical_reads,
       total_logical_writes, 
       execution_count,
       total_logical_reads+total_logical_writes AS [IO_total],
        st.text AS query_text,
       db_name(st.dbid) AS database_name,
       st.objectid AS object_id

FROM sys.dm_exec_query_stats  qs

CROSS APPLY sys.dm_exec_sql_text(sql_handle) st

WHERE total_logical_reads+total_logical_writes > 0 

ORDER BY [IO_total] DESC

Partial results:

total_logical_writes execution_count IO_total query_text

—————————— ———————- ———— ——————————-

0                    1               13877    SELECT * FROM …

7                    3               8670     SELECT T.eventid, …

…

Extreme Situations

I define an extreme situation as a situation in which your server has stopped responding. SQL Server 2005 provides a new feature called Dedicated Administrator Connection (DAC) that will allow you to connect to the server in most cases. The DAC has reserved memory and its own separate SQL Server scheduler. A word of caution, the name can be misleading. I would have named it Troubleshooting Connection or Emergency Connection because it is not meant for administrative operations on a daily basis. The only time you should use it is when you cannot connect normally. When it is necessary to use it, use it with caution. Remember that the DAC has limited resources allocated and since your server stopped responding, running big intensive queries using DAC is not recommended. You should also be sure to close it after you use it, the DAC will not time out and only a single DAC is supported so use it and close it. There are multiple causes for an unresponsive server.

I usually try to connect to an unresponsive server using the sqlcmd utility with the -A switch (to activate DAC) and I will take a look at the existing connections and the current locking status. My favorites DMVs for this purpose are:

• sys.dm_os_scheduler to see the condition of schedulers


• sys.dm_exec_requests to get information about current requests


• sys.dm_exec_sessions to get information about current sessions


• sys.dm_tran_locks to get information about locking status

Then if is possible I will terminate any misbehaving connection using the KILL command. If that does not work I still have the options to shutdown the server using SHUTDOWN WITH NOWAIT. The SHUTDOWN command will immediately stop your SQL Server. If you use the WITH NOWAIT parameter SQL Server will not try to insert a checkpoint in every database and it won’t wait for currently running code to finish.

Conclusion

In this article, you have seen some of the new features in SQL Server 2005 that can help in the administration and troubleshooting of problems. I have shown samples of the use of several of the 80-plus different DMVs available to get you started. Next I discussed the use of summary reports and how they can help to simplify the use of DMVs. I have also described some of the new architecture that SQL Server 2005 is running under. Lastly I went over some practical uses of the new DMV’s and provided samples with each scenario.