Fotia Ltd

Fotia Ltd

Tuesday, 13 June 2006

Dynamic Management Views (DMVs) Part I

posted by Stefan Delmarco

In SQL Server 2000 we had to dig into the system tables to take a peek under the covers. Often many of these tables were undocumented and we had to rely on reverse engineering, sp_helptext, and the debugger to figure out what was going on. In addition, Microsoft was at pains to tell us that these tables are undocumented and as such they should be treated as 'internal-only'. In SQL Server 2005, Microsoft has really listened to their customers. We now have a set of virtual tables known as Dynamic Management Views (DMVs).

A lot needs to be read into the name. Firstly, they've called them dynamic not only because they change dynamically with the internal state of SQL Server, but also because Microsoft reserves the right to change the DMV's schema in future versions (leopards and spots comes to mind...). Management because they're used to inspect the state of SQL Server and diagnose issues. Views because they're not actual tables (actually, some of them are even functions). They're just a read-only table-like representation of SQL Server in-memory structures (a bit like sysprocesses in SQL Server 2000). The difference between the old system tables and DMVs is that DMVs are documented and fully supported by Microsoft. They reside in the sys schema (so all the DMVs must be prefixed with sys. - more on schemas vs. owner later) and have a dm_ prefix.

Just listing the DMVs with a one line'r would make this a very boring article. I'd rather show you which ones I think are cool and will be useful to use. However, there are so many that it will take some time for all the buried treasure to be found. Broadly speaking the DMVs are split along functional lines. There are DMVs for CLR-integration, I/O, replication, full-text, and many others. Each of these categories has its own prefix. For example, CLR related DMVs all begin with sys.dm_clr_*. To be able to view DMVs does not require sysadmin privileges. The new VIEW SERVER STATE permission is all the is required for most DMVs (i.e. can look but cannot touch). I know some people that will be very happy to hear this!

Whenever I first get my hands on a SQL Server, the first think I like to do is have a look at what processes are running on the SQL Server. In SQL Server 2000 this would be sysprocesses (albeit through sp_who/2). This view still exists in SQL Server 2005 for backwards compatibility.

There isn't just a single replacement DMV for sysprocesses. There are at least 3! sysprocesses was always a bit confused in that it mixed connection, session and request (what was being executed) into a single table. SQL Server 2005 has split this information up into 3 separate DMVs. This DMVs belong in the "Execution Related Dynamic Views" category and carry the sys.dm_exec_* prefix:

  • sys.dm_exec_sessions
  • sys.dm_exec_connections
  • sys.dm_exec_request

To call these DMVs a replacement for sysprocesses is doing a disservice to the DMVs. It would be like saying that the Ford GT90 is a replacement for the Focus! It is so much more, in a different league!

First, let's have a look at sys.dm_exec_sessions as this has the closest resemblance to sysprocesses. One row exists in this view for every connection / session to SQL Server. Run the following query to see what gets returned:

select *
from sys.dm_exec_sessions;

At first glance this looks pretty much the same as sysprocesses, but keep scrolling to the right. You'll find that each session's user / environment options are clearly visible across all connections!

ANSI_NULLS, DATEFORMAT, etc. are all there! In one foul swoop you can now check that all the sessions to the database are using the correct connection properties. Previously this information was only available by using the undocumented DBCC PSS command. Even then, no aggregated view like this DMV was available. Even better, the transaction isolation level is now visible as well. I don't know how many times I've had to convince a developer that their transaction actually gets upgraded to SERIALIZABLE when started under COM+! With this column, it will be the easiest thing in the world to check! Also observe the row count column (how many rows were returned in the last result set returned), previous error number and last request start / end times - all priceless when diagnosing issues.

Depending on how familiar you were with sysprocesses you'll have noticed sys.dm_exec_sessions did not contain all of the columns from sysprocesses, particularly the connection oriented columns. The DMV containing connection specific columns is sys.dm_exec_connections. Run the following query to get a flavour of what the table contains:

select *
from sys.dm_exec_connections;

You'll notice that this table contains a smaller number of rows than sys.dm_exec_sessions. This is because sys.dm_exec_sessions includes system sessions as well (such as the lazy writer, service broker, etc.).

The sys.dm_exec_connections DMV only contains local / remote connections to the SQL Server. This does not include any internal system connections (which aren't real connection anyway). Again, this DMV contains far more detail than sysprocesses. Amongst the new columns we can now see the packet size each connection is using, number of bytes that have been sent / received for each connection (see how the number of ways to spot the resource hogs has increased with DMVs?) and which authentication scheme the connection is using. Anyone that tried to figure whether a connection was using NTLM or Kerberos in SQL Server 2000 will love that!

The last DMV for this instalment is sys.dm_exec_requests. Again, ping the DMV to see what comes back:

select *
from sys.dm_exec_requests;

We can use this DMV to get the overall picture of what requests are currently executing, get handle to the TSQL and plan they are executing, see if the request is blocked / blocking (same as SQL Server 2000). Note that this DMV duplicates some of the session information from sys.dm_exec_sessions so you don't have to join the DMVs. Typically you'd want to see session- and request-oriented data together.

So, you might be wondering, given the amount of detail available in DMV's is there a way to be able to dig into them easily without having to come up with sp_who3...? Luckily Microsoft has integrated portions of Reporting Services into the Query Analyser replacement: SQL Server Management Studio (SSMS). Bring up the Reports window and select one of the many available. Context of the report is driven by the current selection in Object Explorer. For those that don't have ready access to SSMS have a look at the attached screen shot and be wow'd!

Now that we've had a bit of an introduction to DMVs as a transition from sysprocesses, next we'll have a look at the power user's DMVs!

You can download the SQL Server 2005 system view map (which includes DMVs) from the Microsoft Download Center.

Labels: ,

Previous Posts