First published on MSDN on Jun 12, 2007
Index usage
In the second part we want to look at some new DMVs which for the first time also get used by SAP Database Monitor functionality. We basically will take a look at the area of DMVs we developed for tracking the usage of indexes and the particular activity indexes or B-Trees encounter
The first DMV we want to take a look at is called sys.dm_db_index_usage_stats. It is used in SAP DBA Cockpit to show the usage of tables/indexes. The data is collected and available while the particular SQL Server instance is up and running. Stopping the SQL Server instance will result in a loss of all the accumulated data. As of now, there is no persistence of this kind of data in SQL Server. Different sort criteria can be defined to view the data in this DMV. SAP DBA Cockpit additionally has the functionality to show the information for a single table only. Query 1 below shows the usage of indexes of one particular table
--Query 1
select object_name(iu.object_id) as 'Table Name', i.name as 'Index Name', *
from sys.dm_db_index_usage_stats iu, sys.indexes i
where iu.database_id= db_id()
and iu.object_id = object_id('
TBTCO
') -- Put in name of table you want to check
and i.object_id=iu.object_id and i.index_id=iu.index_id
order by iu.index_id asc
The name of table the index used should be checked on is marked in
Red and Italic
.
Please note the columns with the prefix ‘user_’. They are the real interesting ones because they show access to the indexes by users or applications. Columns starting with the prefix ‘system_’ only show access of activity by SQL Server like update statistics or consistency checks. Thus for analyzing whether an index is useful or not only the ‘user’ part is important. One certainly will find a lot of indexes which are deployed by SAP which are not used at all. However one should never delete those since these indexes could be used when new functionality gets deployed. This query is best used to answer the question whether customer specific indexes still are used or not. For that purpose it is the best to concentrate on a few tables and run the query periodically and consolidate the results over longer period of time.
Result of the user_* part could look like:
name
|
user_-
seeks
|
user_-
scans
|
user_-
lookups
|
user_-
updates
|
last_-user_-
seek
|
last_-user_-
scan
|
last_-user_-
lookup
|
last_-user_-
update
|
TBTCO^9
|
0
|
3
|
0
|
37402
|
NULL
|
5/20/05 18:12
|
NULL
|
5/23/05 13:48
|
TBTCO__0
|
97294
|
12
|
10463
|
37402
|
5/23/05 13:48
|
5/23/05 4:00
|
5/23/05 13:45
|
5/23/05 13:48
|
TBTCO__5
|
13
|
0
|
0
|
37107
|
5/23/05 2:30
|
NULL
|
NULL
|
5/23/05 13:48
|
TBTCO_____1
|
0
|
0
|
0
|
37107
|
NULL
|
NULL
|
NULL
|
5/23/05 13:48
|
|
|
