SQLStarters

In my previous post we took a quick look into one of the SQL Server features that I personally consider to be a great boon to any DBA, the Dynamic Management Views and Functions. Im going to show you through some basic examples on how you can use these views to get meaningful information out of your SQL Server instances.

Personally I always like to look at what the DMV has to offer before doing SELECT statements into them. Often there’s a lot of information available through these and some of it you might just not need all the time (and using SELECT * is just a bad, bad habit). Lets start by using the one DMV we already looked at previously, sys.dm_db_index_usage_stats, that holds information about index usage of your SQL Server.

sp_help "sys.dm_db_index_usage_stats";

This particular view will give you 19 different columns (at least in SQL Server 2012). The ones that I’d consider to be interesting in this one are:

There’s also a number of system_ columns that look similar to User_ ones, these however are quite different from each other. System_ columns hold the information about the times your system has accesses these indexes, for example during statistic operations. Now I’m not going into very complex issues here, as there’s a number of scripts available on the net you can just copy’n’paste to your Query Tool and execute. But if you like to learn by doing, this should help you get started.

Let’s do a basic query first, to see what we’ll be getting out of the DMV using the columns I listed.

Query:

SELECT DB_NAME(database_id) AS UserDb,
   object_id,
   index_id,
   user_seeks,
   user_scans,
   user_lookups,
   user_updates
FROM sys.dm_db_index_usage_stats
WHERE database_id > 4;

Result:

And there you have it, not much as this is a pretty “cold” test instance I have running, but as you can see there are few indexes that I’ve used and you can even see how they’re used. There are few things, however, that probably need little more digging into before this turns into anything useful. You do see a name of the database there, but the object_id doesn’t really tell you all that much.

The object_id is the ID of the table (or a view) where the index is located, you can find actual name for it through querying Catalog View called sys.tables. Catalog Views are an interface through which you can access catalog metadata. Sys.tables view is part of the Object Catalog View category with other views that include information about indexes, constraints, stats etc. Catalog views are bit outside the scope of this article, so lets not get further into it. As previously shown, you can use the sp_help “sys.indexes” to find the columns of interest.

Query:

SELECT object_id, name FROM sys.tables
WHERE object_id = '981578535'


Result:

The query tells us that the object id is that of an table called “Employee”, this can be easily confirmed by doing few more queries to the said table. After that you should see the number of user operations going up by using the first query.

The index_id is bit easier to understand. Index_ID 1 tells me that it’s a Clustered Index. If it’s 2, or anything above that then we’ll know we’re looking at Nonclustered Index. If the Index_ID is 0, then we have a “heap” table, one that has no clustered index in it. So let’s look at the sys.indexes Catalog View, what it tells us.

Query:

SELECT object_id, name, index_id, type_desc FROM sys.indexes
WHERE object_id = '981578535'


Result:

This table has a whole bunch of indexes apparently. PK_Employee_BusinessEntityID has the Index_ID of 1 and that’s our Clustered Index for the table. Rest of the indexes have Index_ID 2 or higher, indicating Nonclustered Indexes. But let’s get back to the original work now that we have all the information we require to build our original query into more informative one.

We’ll do this by adding the table and index name into one of the columns we’re looking for and then joining sys.dm_db_index_usage_stats Dynamic Management View with the sys.tables and sys.indexes Catalog Views.

Query:

SELECT DB_NAME(database_id) AS UserDb,
   STBL.name AS TblName,
   USTATS.index_id,
   SIDX.name,
   user_seeks,
   user_scans,
   user_lookups,
   user_updates
FROM sys.dm_db_index_usage_stats AS USTATS
   INNER JOIN SYS.TABLES AS STBL
ON USTATS.object_id = STBL.object_id
   INNER JOIN SYS.INDEXES AS SIDX
ON USTATS.object_id = SIDX.object_id
AND USTATS.index_id = SIDX.index_id
WHERE database_id > 4;

Result:

And now we have bit more information on the index usage and objects involved. Keep in mind that this is just a very, very simple example of gathering information about indexes from multiple sources (DMV’s and Catalog Views) and getting more informative results. There are other options also, such as combining information from two Dynamic Management Views, which I’ve ofteen seen done with the sys.dm_exec_connections and sys.dm_exec_sessions.

Hopefully this gave you a taste of what you can do with the DMV’s and how they might benefit you as a DBA. With the DMV’s being very important part of any DBA’s toolbox, you can find quite advanced scripting examples from Microsoft sources, but also from SQL Server community sites. The latter ones usually have gone even bit further than the official samples. Have fun learning!