Page History

...

The following section describes a few examples on how you can use the Objects tab to identify performance problems.■    Analyzing

• Analyzing how configuration changes affect resource consumption

...

• Analyzing how schema changes affect the resource consumption of various entities

...

• Examining an object's space usage over time

...

• Examining fragmentation of table data and indexes

...

• Examining access methods to tables and indexes

...

• Examining the distribution statistics of indexes

...

• Determining whether to recommend an index for a table

Anchor
Analyzinghowconfigurationchangesaffectresourceconsumption Analyzinghowconfigurationchangesaffectresourceconsumption

Analyzing how configuration changes affect resource consumption

Changing an instance configuration parameter (or database parameter) may affect the performance of your system. Precise for SQL Server allows you to track the configuration changes and compare them with the resource consumption of your application, over time. This can be done by examining the Instance/Database Changes view in the Instance entity. The Instance/Database Changes view allows you to compare the changes made to the instance configuration parameters and the database options, with the resource consumption of the entire instance, over a selected time period. In this way you can pinpoint the changes that affect the resource consumption of the entire database.

...

1. In the Time Frame list, choose the period of time you want to analyze.
2. In the Instance list, choose the item you want to analyze.
3. On the View controls in the Main area, click Instance/Database Changes. Compare the changes made to the instance configuration parameters and the database options, with the resource consumption of the entire instance, over the selected time period. This allows you to pinpoint the changes that affect the resource consumption of the entire database.
4. On the Association controls in the Association area, click Databases.
5. Compare the changes made to the instance configuration parameters and the database options, with the resource consumption of the entire instance, over the selected time period. This allows you to pinpoint the changes that affect the resource consumption of the entire database.
6. On the View controls in the Main area, click Database Created/Dropped. Analyze how changes to database options affect In MS-SQL resource consumption.

Anchor
Analyzinghowschemachangesaffecttheresourceconsumptionofvariousentities Analyzinghowschemachangesaffecttheresourceconsumptionofvariousentities

Analyzing how schema changes affect the resource consumption of various entities

Changing the database schema may affect the performance of your system. These changes are done to improve the performance of your application. You need to monitor them to ensure that you have achieved your goals. Precise for

...

1. In the Time Frame list, choose the period of time you want to analyze.
2. In the Instance list, choose the item you want to analyze.
3. On the Association controls in the Association area, click Databases. Drill down to the database you want to analyze.
4. On the View controls in the Main area, click Object Changes. Check the database objects that where changed, dropped or created in reference to the resource consumption of the entire database over time and determine if they affect In MS-SQL resource consumption.
5. Drill down to a specific table entity. On the View controls in the Main area, click Change Log Detailed and view detailed information on the changes made to the table's schema.

Anchor
Examininganobjectsspaceusageovertime Examininganobjectsspaceusageovertime

Examining an object's space usage over time

Space usage information, collected by the Collect Space Utilization process, allows you to examine the space allocated versus the space used, in a table and index. This enables you to easily keep track of object growth over time and decide how to spread database files over various disks and how to connect objects to file groups in the specified database.

...

1. In the Time Frame list, choose the period of time you want to analyze.
2. In the Instance list, choose the item you want to analyze.
3. On the Association controls in the Association area, click Databases. Drill down to the database you want to analyze.
4. Drill down to a specific table entity. On the View controls in the Main area, click Overview.
5. In the Main area, analyze the Space over Time graph to determine how tablespace has been utilized over time.

...

Anchor
Examiningfragmentationoftabledataandindexes Examiningfragmentationoftabledataandindexes

Examining fragmentation of table data and indexes

Fragmentation of table data and indexes may affect the performance of your system. The fragmentation occurs as a result of data modification (INSERT, UPDATE, and DELETE statements) performed on the table. Fragmentation increases the amount of page reads performed by the query, increasing their resource consumption.

...

1. In the Time Frame list, choose the period of time you want to analyze.
2. In the Instance list, choose the item you want to analyze.
3. On the Association controls in the Association area, click Databases. Drill down to the database you want to analyze.
4. On the Association controls in the Association area, click Tables or Indexes. Drill down to a specific table or index entity (depending upon your selection).
5. On the View controls in the Main area, click Show Contig and analyze the fragmentation information displayed for the selected table or index. Determine whether the table or index is heavily fragmented and needs to be reorganized.

See “About viewing data storage and fragmentation information in the Show Contig view” on page 114 and “About viewing data storage and fragmentation information of a selected index in the Show Contig view” on page 118.

Anchor
Examiningaccessmethodstotablesandindexes Examiningaccessmethodstotablesandindexes

Examining access methods to tables and indexes

Examining the access methods used on tables and indexes can help you make decisions regarding the types of indexes you should employ. For example if most of the statements accessing the specified table are of type update (INSERT, UPDATE, DELETE) and only a few are of type SELECT you should add as few indexes as possible to efficiently access data, on the one hand, and avoid overhead when performing updates, on the other hand. Also if you observe an increase in I/O wait you should consider rewriting the statements using the specified table or index, making sure you implement appropriate indexes and reorganize any table data and indexes that may suffer from fragmentation.

...

1. In the Time Frame list, choose the period of time you want to analyze.
2. In the Instance list, choose the item you want to analyze.
3. On the Association controls in the Association area, click Databases. Drill down to the database you want to analyze.
4. On the Association controls in the Association area, click Tables or Indexes. Drill down to a specific table or index entity (depending upon your selection).
5. On the View controls in the Main area, click Usage Report to observe information on the access methods used on the table or index.

See “About viewing usage information and access methods in the Usage Report” on page 116 and “About viewing a usage report” on page 121.

Anchor
Examiningthedistributionstatisticsofindexes Examiningthedistributionstatisticsofindexes

Examining the distribution statistics of indexes

Distribution statistics of an index can help you determine whether or not the index is useful to the query optimizer. The information displayed in the Statistics view of the Index entity indicates the selectivity of an index; the more selective an index is, the more useful it is, since this means more rows can be eliminated from consideration.

...

1. In the Time Frame list, choose the period of time you want to analyze.
2. In the Instance list, choose the item you want to analyze.
3. On the Association controls in the Association area, click Databases. Drill down to the database you want to analyze.
4. On the Association controls in the Association area, click Indexes. Drill down to a specific index.
5. On the View controls in the Main area, click Statistics and observe the distribution statistics for the selected index in the Distribution table.

Anchor
Determiningwhethertorecommendanindexforatable Determiningwhethertorecommendanindexforatable

Determining whether to recommend an index for a table

The decision to add a specific index to a table is complex. The index can improve one statement and may negatively affect another. Therefore, before adding an index, you should examine every statement accessing the table to see how the new index will impact it.

...

1. In the Time Frame list, choose the period of time you want to analyze.
2. In the Instance list, choose the item you want to analyze.
3. On the Association controls in the Association area, click Databases. Drill down to the database you want to analyze.
4. On the Association controls in the Association area, click Tables. Drill down to the table you want to analyze.
5. On the Recommend tab, analyze the statements that are associated with the selected table and observe their resource consumption.

...