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.
