Page History
...
- About the Objects tab
- How the Objects tab is structured
- About Objects tab entities
- About the Instance entity
- About the Database entity
- About the Active Objects entity
- About the Table and Table Contain Same Column entities
- How the Objects tab can help you identify performance problems
...
Displays details on the recommended index or statistic. The following tabs are available:Columns Displays
- Columns. Displays a list of all the columns in the recommended index's or statistic's table as follows:
...
- Icon. Indicates whether the data in the column will be stored in the index in ascending (ASC) or descending (DESC) order. No icon is displayed for columns that are not part of the recommended index.
...
- Column. Name of the column.
...
- Key Number. Key number of the column in the index.
...
- Column Type. The physical storage type of the column.
...
- Part of Index. Indicates whether the column participates in an existing index.
...
- First Key of
...
- Index. Indicates whether the column participates as the first key in an existing index.
...
- 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 following information is displayed:
...
- Icon. Indicates whether the item is an index or statistic.
...
- Name. Displays the name of the index or statistic.
...
- Unique. Indicates whether the index is unique. This value is not relevant for statistics.
...
- Clustered. Indicates whether the index is clustered. This value is not relevant for statistics.
...
- Columns List. Displays a list of the columns of the index or statistic separated by commas.
The sort order (ascending, descending) is also displayed for indexes.
- Columns List. Displays a list of the columns of the index or statistic separated by commas.
- Analyzed
...
- Statements. Displays a list of all the statements in the workload.
The following information is displayed:
...
- Statement. Indicates the statement ID.
...
- In MS-
...
- SQL. Displays the resource consumption (as a stacked bar) of the specified statement within the selected time period.
...
- Text. Displays the statement's text.
| Anchor | ||||
|---|---|---|---|---|
|
The following entities can be examined in the Objects tab:■ Instance
- 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
| Anchor | ||||
|---|---|---|---|---|
|
The Instance entity displays configuration information, configuration changes, as collected by the Collect Schema Changes process, and performance information, as collected by the Precise for SQL Server Collector for the entire instance.
The following views are available:■ Overview
- Overview
...
- SQL Server Log
...
- Instance Parameters
...
- Instance/Database Changes
...
- Databases Created/Dropped
About getting an overview of instance configuration and performance information
The Overview displays configuration and performance information for the entire instance. The Overview is divided into the following areas:
- Server
...
- Details. Displays server configuration details such as version and number of CPUs.
- Instance
...
- Details. Displays SQL Server instance configuration details such as version, service pack and server details.
- In MS-
...
- SQL. Displays resource consumption, over the selected time period, of the selected instance, as collected by the Precise for SQL Server Collector.
- Space
...
- Allocation. Displays the total free space and total used space (in MB) over the selected time period, of all the files in the selected instance, as collected by the Collect Space Utilization process.
About viewing SQL server log messages
The SQL Server Log view displays the SQL Server log messages from the recent error log file, for the selected time period.
About viewing instance configuration parameters
The Instance Parameter view displays the selected instance's configuration parameters. This includes the possible range values and configuration values, for each parameter. A change icon is displayed if the Collect Schema Change Process detects a change in any of the parameters.
Precise for SQL Server reports changes that occurred within the selected time period. If you observe that a parameter was changed, select Instance Parameter Changes from the View controls, to view additional information.
About viewing changes to instance parameters and database options
The Instance/Database view displays the changes to instance parameters and database options vs. resource consumption, over the selected time period, to enable you to identify configuration performance changes caused by these actions, as follows:
- Instance/Database
...
- Changes.
- Sample Date. The date when the changes were captured by the Collect Schema Changes process.
...
- Database. The name of the database where the change occurred. For Instance Configuration changes, the value is N/A.
- Data
...
- Changed. Displays one of the following values:
- Changed. Displays one of the following values:
...
- Schema. Any change in the database schema.
...
- Database Option. A description of the database option that was changed
...
- Configuration Parameter. A description of the instance configuration parameter that was changed.
- New
...
- Value. Displays the new value that resulted following the change.
- Old
...
- Value. Displays the previous value that preceded the change.
- 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:
...
- Schema Changed. Any change in one of the database's Schema.
...
- Database Option Changed. Any change in one of the database's options.
...
- Instance Property Changed. Any change in one of the instance configuration parameters.
The following configuration parameters that can be displayed:■ Maximum
- 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.
| Anchor | ||||
|---|---|---|---|---|
|
The Database entity displays configuration information, configuration changes collected by the Collect Schema Changes process and performance information collected by the Precise for SQL Server collector of the selected database.
The following views are available:■ Overview
- Overview
...
- Database Options
...
- Database Option Changes
...
- Object Changes
About getting an overview of configuration, performance and storage information for a selected database
The Overview displays configuration, performance and storage information for the selected database, as follows:Properties Displays
- Properties. Displays general database details, such as, status, collation, and compatibility level.
- In MS-
...
- SQL. Displays resource consumption of the selected database, over the selected time period, as collected by the Precise for SQL Server Collector.
- Space
...
- Allocation. Displays total free space and total used space (in MB), over the selected time period, of all the files in the selected database, as collected by the Collect Space Utilization process.
- Schema
...
- Changes. Displays an overtime graph that indicates whether changes were made to the database objects and the database configuration options.
- Data vs. Log.
...
- Displays total free space and total used space (in MB), over the selected time period, of all the files in the selected database, as collected by the Collect Space Utilization process, and displayed according to file types as datafiles or log files.
About viewing database options
The Database Options view displays the database options, such as Auto Create Statistics and Auto Shrink File, etc. If the Collect Schema Changes process detects a change in any of the options, an icon is displayed in the first column. Select the Database Option Changes view to observe additional information regarding the detected change. The following information is displayed:
- Database
...
- Options. Displays the current value of the database options.
- In MS-
...
- SQL. Displays resource consumption information of the selected database, over the selected time period.
- Schema
...
- Changes. Displays option changes of the selected database, over the selected time period, as detected by the Schema Change Process.
About viewing database option changes
The Database Option Changes view displays the database option changes vs. resource consumption, over the selected time period, so as to enable you to identify configuration changes that led to performance problems.
The following database options changes are displayed:■ Recovery
- Recovery Model Changed
...
- Ansi Null Changed
...
- Close Cursors On Commit Changed
...
- Auto Create Statistics Changed
...
- Compatibility Level Changed
...
- Auto Update Statistics Changed
...
- Auto Shrink File Changed
...
- Status Changed
...
- Truncate Log Changed
The following information is displayed:
- Database Option
...
- Changes. Displays the changes made to the database options of the selected database, over the selected time period.
- In MS-
...
- SQL. Displays resource consumption information of the selected database, over the selected time period.
- Schema
...
- Changes. Displays option changes of the selected database, over the selected time period, as detected by the Schema Change Process.
About viewing object changes
The Object Changes view displays the schema changes vs. resource consumption over the selected time period to enable you to identify performance changes caused by these actions, as follows:
- Object
...
- Changes.
- Sample Date. The estimated last change date of the table (and /or its sub-objects). The change is reported by the Precise for SQL Server Schema Change Process, which runs, by default, once a day. This means that the Sample Date actually represents the date when the Schema Change Process was executed.
- Object
...
- Type. The Object type can be one of the following: Table, View, Stored procedure, Trigger, Foreign key, Scalar function, Inline Table function, Table function, and Extended storage procedure.
...
- Object. Displays the object name.
- Object
...
- Owner. Displays the owner of the object.
- Change
...
- Type. Can be one of the following:
- Type. Can be one of the following:
...
- Created. Indicates object was created.
...
- Dropped. Indicates object was dropped.
...
- Index. Indicates any change that was made in the object's index(es).
...
- Change in structure. Indicates any change that was made in the object's column(s) and the creation or deletion of the object's trigger(s).
...
- Change in properties. Indicates any change that was made in one of the following object's properties: Name, Owner, File Group and Exec is ANSI null on.
...
- Partition function.
...
- Partition schema.
...
- Partition function values.
- In MS-
...
- SQL. Displays resource consumption of the selected database, over the selected time period, as collected by the Precise for SQL Server Collector.
- Schema
...
- Changes. Displays an overtime graph that indicates whether changes were made to the database objects, broken down into the following groups:
...
- Index. Indicates any change, addition, or deletion of an index.
...
- Object Change in
...
- Structure. Indicates any change in the object's structure. For example, whether a new column was added to a table.
...
- Object Change in
...
- Property. Indicates any change in the object's property. For example, whether the name of the object was changed.
...
- Object Created/
...
- Dropped. Indicates whether any of the objects sampled (table, view, stored procedure, etc.) was dropped or created.
About the Database entity in the Association area
You can view the entities associated with the selected Database entity in the Association area. It is possible to associate to the Database entity from the Instance entity.
| Anchor | ||||
|---|---|---|---|---|
|
The Active Objects entity pinpoints objects (tables including their indexes statistics) that may be a good candidate for tuning. If the object is an SQL Server 2005, additional information regarding I/O operations and waits, contentions and logical operations is also displayed. Clicking on the object 's icon launches to the Objects tab with the selected object in context.
The Active Objects entity indicates the following:■
- the name of the user, object, and database it is associated with
...
- a summary of the time it spent in SQL Server, during the time frame you selected
...
- its size (in pages)
See “About the Table and Table Contain Same Column entities” on page 113 . see and “About the Index and Unused Index entities” on page 117.
About the Active Objects Association area
The following additional information is available for SQL Server 2005 objects:
- I/O
...
- Operations. Indicates the amount of I/O operations performed on the table or index, over the selected time period.
- I/O
...
- Waits. Displays the I/O wait for the table or index, over the selected time period.
...
- Contentions. Displays the number of contentions experienced by the table or index, over the selected time period.
- Contention
...
- Waits. Displays the contention wait data for the selected table or index, over the selected time period.
- Logical
...
- Operations. Displays how many range scan and Singleton lookups were performed on the table or index, over the selected time period.
- Size (pages)
...
- . Displays the size of the table or index (in pages).
| Anchor | ||||
|---|---|---|---|---|
|
The Table entity displays general details regarding resource consumption, space utilization and schema changes for the selected table.
...
The following views are available:■ Overview
- Overview
...
- Show Contig
...
- Change Log Summary
...
- Change Log Detailed
...
- Usage Report
...
- Common Access Patterns (SQL Server 2005 only)
...
- Operational Statistics (SQL Server 2005 only)
About getting an overview of table entities
The Overview displays general table properties, along with the resource consumption, space utilization, schema changes and number of rows in the selected table, over the selected time period.Properties Displays
- Properties. Displays general table details, such as, number of columns, file group, and whether or not the table uses a clustered index.
- In MS-
...
- SQL. Displays the Using CPU and I/O Wait counters of the selected table, over the selected time period, based on its contribution to overall performance, when accessed by the execution plan.
- Space
...
- Allocation. Displays the amount of free space and used space (in MB) in the selected table, over the selected time period, and specifies whether the space is populated by an index or data.
The values may be incorrect due to out-of-date space usage information. To update the values, use the DBCC UPDATEUSAGE command to recalculate the space usage fields. For more information, see SQL Server Books Online.
...