Page History
This section includes the following topics:
...
...
...
...
| Anchor |
|---|
...
|
...
|
It is important to understand the relationship between database objects and statements to make well-founded tuning decisions.
...
Object tuning is a powerful tool. Taking into consideration important aspects regarding objects can ensure maximum application performance. Precise for SQL Server allows you to examine your index structure by displaying the usage of each index. Storage layout can be optimized by analyzing the object storage configuration and metric information.
Since Because Precise for SQL Server tracks changes in the database objects, the effect of schema changes on performance can be easily observed.
| Anchor |
|---|
...
|
...
|
The Objects tab displays information on a selected entity and its associated entities. When you open the Objects tab the selected entity is by default Instance, meaning that information is displayed on the instance level. When you launch the Objects tab from the Activity tab with one of the following entities—Database, Table, Index, DB file or Locked Object—the tab is launched in context with the entity you selected in the Activity tab.
...
The entities displayed in the Association area are associated with the selected entity displayed in the Main area. At times, the relationship between the entity displayed in the Main area and those displayed in the Association area is that of parent to child and sometimes it merely represents that there is a relationship between the selected entity and the entities displayed in the Association area.
About the Main area in the Explore Objects tab
The Main area shows comprehensive information on the selected entity. You can choose from several views to examine the entity from different angles. You can, for example, focus exclusively on SQL Server Log data, or Instance Parameters, or Instance Parameters changes.
About the Association area in the Explore Objects tab
...
see “About Precise for SQL Server tabs” on page 22.
About the Recommend tab
Precise for SQL Server uses the Microsoft® Index Tuning Wizard to achieve recommended indexes or statistics for the selected table. This helps the optimizer choose a better access plan and enhances the performance of the statements or batches that access the selected table. The recommendations are based on a workload. The workload contains all the statements stored in the PMDB that were executed during the selected time period that have an average duration time exceeding the value defined in the registry (where 0 is the default). The recommend indexes process only performs the recommend process on indexes or statistics additions.
The following information is displayed in the Recommend tab
■ Recommended Index/Statistics
■ Recommendation details
About viewing Recommended Indexes/Statistics
Displays the recommended indexes or statistics, as follows:
■ Icon—indicates whether the item is a recommended index or statistic.
■ Table—displays the name of the table to which the recommendation refers.
■ Unique—indicates whether the index is unique. This value is not relevant for recommended statistics.
■ Clustered—indicates whether the index is clustered. This value is not relevant for recommended statistics.
■ Columns List—displays a list of the index's or statistic's columns separated by commas. The sort order (ASC, DESC) is also displayed for recommended indexes.
About viewing Recommendation details Displays details on the recommended index or statistic. The following tabs are available:
Columns Displays a list of all the columns in the recommended index's or statistic's table as follows:
...
■ Used—number of times the column is used in the breakdown of the workload statements according to the following types: Range Scan, Table Scan, Sort and Join.
DDL Displays the DDL text of the recommended index or statistic.
Table's Indexes/Statistics Displays a list of the existing indexes or statistics of the table selected in the recommendation area.
...
The sort order (ascending, descending) is also displayed for indexes.
Analyzed Statements Displays a list of all the statements in the workload.
The following information is displayed:
...
■ Text—displays the statement's text.
| Anchor |
|---|
...
|
...
|
...
The following entities can be examined in the Objects tab:
■ Instance
■ Database
■ Active Objects
■ Table and Table Contain Same Column
■ Index and Unused Index
■ Partition
■ Column
■ View and View Contain Same Column
■ User
■ Logical File
■ File Group
■ Statistics
■ Stored Procedure
■ Function
■ Trigger
■ Statement
■ Batch
■ Physical Disk
■ Logical Volume
■ Disk Partition
About the Instance entity
...
In MS-SQL Displays resource consumption of the selected instance, over the selected time period, as collected by the Precise for SQL Server Collector.
Schema Changes Displays an overtime graph that indicates whether changes occurred in the database, or a breakdown of the instances into the following groups:
...
■ Instance Property Changed—any change in one of the instance configuration parameters.
The following configuration parameters that can be displayed:
■ Maximum recovery interval in minutes
■ Number of locks for all users
■ Number of open database objects
■ Maximum worker threads.
■ Network packet size
■ Memory for index create sorts (KB)
■ Priority boost
■ Minimum memory per query (KB)
■ Query wait(s)
■ Set working set size
■ Affinity mask
■ Cost threshold for parallelism
■ Maximum degree of parallelism
■ Minimum Server Memory size (MB)
■ Maximum Server Memory size (MB)
■ Maximum estimated cost of query allowed to run by query governor
■ User mode scheduler uses lightweight pooling
■ Recovery Model Changed
■ Auto Update Statistics Changed
■ Ansi Null Changed
■ Auto Shrink File Changed
■ Close Cursors On Commit Changed
■ Status Changed
■ Auto Create Statistics Changed
■ Truncate Log Changed
■ Compatibility Level Changed
About viewing databases that were created or dropped
The Databases Created/Dropped view displays the databases that were dropped or created vs. resource consumption over the selected time period to enable you to identify performance changes caused by these actions.
In MS-SQL Displays resource consumption of the selected instance, over the selected time period, as collected by the Precise for SQL Server Collector.
Schema Changes Displays resource consumption of the selected instance, over the selected time period, as collected by the Precise for SQL Server Collector.
About the Database entity
...
The following views are available:
■ Overview
■ Database Options
■ Database Option Changes
...
You can view general details on the entities associated to the selected Storage Units in the Association area. This information is based on the mapping carried out by the Precise for SQL Server Storage agents that are installed.
| Anchor | ||||
|---|---|---|---|---|
|
The following section describes a few examples on how you can use the Objects tab to identify performance problems.
...