Getting familiar with the SQL Servers DMV’s

I’ve been working with SQL Server ever since 1999, back then it was the 6.5 version which was soon replaced by the SQL Server 2000 (we skipped the 7.0 completely). Back in those days getting information out of SQL Server was much, much more difficult and you had way less tools for it. Then came the SQL Server 2005 and suddenly there was a plenty of information available to make troubleshooting and working on performance issues so much easier.

In fact there’s so much information available these days, that finding out where simply to start can be a daunting task. When I first started working with SQL Server 2005 my first task was to familiarize myself with the new feature called Dynamic Management Views and Functions, or DMV’s as they’re sometimes called. In SQL Server 2005 you have about 90 DMV’s, in SQL Server 2012 the number of DMV’s is almost 180. They’re definately an important tool in any DBA’s toolkit. And that is what this article is about, to get yourself started with the DMV’s.

While there’s no shortage of ready scripts and some pretty advanced DMV stuff made by the awesome people in SQL Server community, I’ve always thought that part of the fun is figuring stuff out yourself. So if you’re new to SQL Server and/or DMV’s you might find the following hints useful in your learning efforts.

DMV’s come in two different scopes, database and server. You can get information about databases, operating system, replication, indexes, disk I/O, etc from them by simply using a SELECT queries. The actual difficulty, at least for me, was to find the correct DMV to query. Fortunately the good folks at the Microsoft had made it (somewhat) easy by using good naming convention. In fact you can get a nice list of DMV’s out from the SQL Server with simple SELECT to sys.system_objects.

SELECT name FROM sys.system_objects WHERE name LIKE 'dm%'

dmvs

So now that you have a list DMV’s how to figure out what they actually do? The first hint comes from the same user friendly naming convention I already mentioned earlier. For example, sys.dm_db_index_usage_stats seems rather obvious name for a DMV that’d give information about how indexes in your database are used. To confirm this we can use Stored Procedure called sp_help to get information about the columns belonging to DMV.

sp_help "sys.dm_db_index_usage_stats"

usage_stats

If you’re a curious sort you can also get the CREATE VIEW statement for the DMV by using Stored Procedure called sp_helptext.

sp_helptext "sys.dm_db_index_usage_stats"

sp_helptext_usage_stats

And that’s it for today. In my next article, we’ll look into doing some simple queries to DMV’s.

Read the next article on DMVs.

Part 2: Using Dynamic Management Views and Functions

Author: Mika Sutinen

Hi, My name is Mika Sutinen and I'm a Senior Database Administrator for a company called Tieto. I've been working in IT-industry for two decades and I've spend most of my career working with healthcare information systems. I've worked with SQL Server for most of my career, starting with version 6.5 a long, long time ago. My other interests are high availability, everything related to performance (testing, monitoring, etc), Windows operating systems and I'm currently learning more about Azure.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s