X hits on this document

Word document

SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and ... - page 55 / 76

234 views

0 shares

0 downloads

0 comments

55 / 76

SAP with Microsoft SQL Server 2005: Best Practices for High Availability, Maximum Performance, and Scalability51

sys.dm_db_index_usage_stats

In a SAP landscape over a period of time, the custom indexes on tables for particular programs can change. For example, because of business or program changes, some indexes might no longer have relevance. In addition, a nonclustered index that was created previously by a customer can consume extra space.

The sys.dm_db_index_usage_stats view monitors index utilization. This view shows where index utilization is occurring on the aggregate and identifies queries and indexes that are not being used.

The sys.dm_db_index_usage_stats view:

Determines whether indexes are actually being used. Generally, there should be no more than about 10 indexes on a table.

Lists the index, categorized by user and system, and indicates how the indexes have been used. This view indicates when an index has been affected by maintenance commands such as DBCC CHECKDB or by a user-initiated query. It lists the category of usage of indexes as user queries (read or modify data) and system usage (update statistics, consistency checks).

The sys.dm_db_index_usage_stats view counts the data during the uptime of the database server. When the server is brought down, the data is lost. This functionality will be used by SAP Database Monitor. The data is used to analyze whether custom deployed indexes are still in use. The data is tracked on a long-term basis to ensure that special periods are covered such as month-end or quarter-end reporting.

Do not use the data to delete SAP standard indexes. In some cases, changes in the use of SAP functionality or changes in customization or in leveraging new SAP functionality might require that SAP indexes be deployed during the installation of the SAP product. For this reason, do not delete SAP deployed indexes even when those shown are not used.

The following table shows the data of the user category, including the categorization of seeks, scans, and lookups in SQL Server 2005.

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

TBTCO__3

6399

0

0

37402

5/23/05 13:45

NULL

NULL

5/23/05 13:48

TBTCO__7

4078

4

0

37402

5/23/05 13:45

5/23/05 4:00

NULL

5/23/05 13:48

A seek accesses a table that uses the index B-tree; the scan does not indicate the number of rows being retrieved. A seek can read the whole table by using the index B-tree.

A scan reads the data layer without the index B-tree, for example, to scan the table’s data layer to create an index.

Microsoft Corporation © 2005

Document info
Document views234
Page views234
Page last viewedSun Dec 04 14:50:23 UTC 2016
Pages76
Paragraphs1372
Words23715

Comments