DMV’s or Dynamic Management Views

Posted: November 19, 2008 in Uncategorized

 

In the last few versions of SQL Server Microsoft has given the DBA a new way to look at what is going on inside SQL Server for one of the SQL on Call clients. The information that is contained in these views can at times be a little difficult to read or actually relate to your data. However there are many times when a common question and a valid question could not be answered. Let me give you an example.

A database developer is chasing down a performance issue on a table that he or she has. It appears that inserts are taking a long time and they have reviewed their code and cannot understand why. The developer did notice that there appear to be a large number of indexes on the table and are curious if they are all being used.

The Question that needs to be answered here are there indexes that are not being used that may be slowing down the insert statements. If these rows that are being inserted are causing index pages to be written and split when the index is not even being used could be the cause of this performance problem. This is an actual problem that I was trying to resolve not to long ago. One of the ways that I can answer that question is by using the Dynamic Managed Views.

For the rest of this post we will use the above example. Please keep in mind that this code that I was using was to solve a specific problem that I was having. As I work my way through the DMV’s I find more and more information all the time. Reason I mention this is that I join my DMV back to the sysobjects table. This is the way I use to get information about my database but as I move around the DMV’s I am adjusting the way I look at and for information.

The first goal was to see how many indexes were not used at all. This needs to be run in the context of the database that you are reviewing.

— This will give you a list of Indexes that are not being used at all

 

Select
name, i.*

from
sys.dm_db_index_usage_stats i

    Join sysobjects o on i.object_id
= o.id

Where user_lookups = 0

and user_seeks = 0

and user_scans = 0

and system_lookups = 0

and system_seeks = 0

and system_scans = 0

After some time reviewing this I wanted to check to see how many of these indexes were not being used very often.

— This will give you a list of Indexes that are not being used very little

 

Select
name, i.*

from
sys.dm_db_index_usage_stats i

    Join sysobjects o on i.object_id
= o.id

Where user_lookups < 100

and user_seeks < 100

and user_scans < 100

and system_lookups < 100

and system_seeks < 100

and system_scans < 100

and
object_id
> 1000

 

With a little bit more discover I was able to start expanding my Query.

 

— This will give you a list of Indexes with the Tablename and Index name

— of the indexes that are not used very often

 

Select
Distinct o.name,si.name

from
sys.dm_db_index_usage_stats i

    Join sysobjects o on i.object_id
= o.id

    Join sysindexes si on i.object_id
= si.id

Where user_lookups < 100

and user_seeks < 100

and user_scans < 100

and system_lookups < 100

and system_seeks < 100

and system_scans < 100

and
object_id
> 1000

and
(o.name is
not
null
and si.name is
not
null)

and si.name not
like
‘PK_%’

and si.name not
like
‘_WA%’

and si.name not
like
‘_hind’

 

The way that many of the DMV’s are used is based upon the last time the system was restarted. So in these examples that I have posted if you were to run these seconds after a restart and before you have allowed traffic on the server then you may be in a situation where you would be getting false results. The best way to avoid this is to run this after your server has been on-line for some time. This then becomes a task on if the developers want to review the indexes to see if they can start to remove these unwanted indexes.

Comments
  1. Those are great. Those queries have found a place in my personal toolbelt.

    The goal is to identify tables that are getting updated often, but not used. By removing these indexes, we avoid the updates on them which helps performance.

    Another side-benefit would be to free up the disk space that is used by these indexes.

    If that’s the goal, then you could take the last query you wrote above and join it to sys.dm_db_partition_stats. This helps us guess at how much space is used by each of the redundant indexes.

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