The Tables & Indexes tab allows you to view statistics for all the tables and indexes on a particular database. To see all the tables and indexes for a database, use the database drop-down list to select your database.
Along with displaying statistics, the Tables & Indexes tab includes the ability to update your statistics and rebuild your indexes on the selected table. These options are available by right-clicking a table and selecting the appropriate option from the context menu. To perform these actions on multiple tables, use the buttons on the ribbon.
Table growth statistics are collected on the schedule configured on the Table Statistics tab of the Monitored SQL Server Properties window. This includes the table fragmentation and size statistics.
The Tables and Indexes view provides a good way to view the overall health of your tables. In addition, the Tables and Indexes view allows you to rebuild the index for any of your tables that have a high fragmentation percentage.
The Rebuild Indexes function is enabled for User tables. System tables cannot have indexes rebuilt.
Access the Tables & Indexes view
To open the Tables & Indexes view of the SQL Diagnostic Manager Databases tab, select the appropriate SQL Server instance, click Databases, and select Tables & Indexes.
Use the Table Details section
The Table Details section of the Table and Indexes view allows you to view various details on the selected table. To update the statistics for your tables, click Update Statistics at the top of the view. The following tables are available in the Table Details section:
Size
Displays the size of the data, text, and indexes associated with the selected table.
Dependencies
Lists other database objects that the selected table contains references to and other database objects that reference the selected table. In addition to the object name, the type of object and action associated is listed.
Indexes
Lists the associated indexes and index information for the selected table.
Index Statistics
Lists the number of columns, average length, average row hits, and row hits percentage for each column in an index associated with the selected table. It also lists the data distribution data values and occurrences of each value.
Table Size Details chart
The Table Size Details chart displays the size of each component in a table. To view the Table Size Details chart, select a table from the list and click the Size tab in the Table Details section.
Analyze table dependencies
The Table Dependencies tab displays the objects the selected table references and is referenced by, the type of dependency, and the action of the dependency. This allows you to quickly see how the table is utilized and how it is affected by and affects other tables and objects.
Indexes tab
The Indexes tab displays all indexes defined for the selected table and an overview of each index.
Analyze index statistics
The Tables and Indexes view allows you to see general table and index statistics for the database selected. You can also rebuild indexes for highly fragmented tables and update your statistics, which are displayed in the chart and tables in the Table Details section of the view. For more information, see the Microsoft document, Tables and Indexes.
The Collection Service may not gather fragmentation data for a particular table because:
- Table statistics collection has not occurred
- The database hosting the table was excluded from collection
- The table size does not meet the minimum size threshold
- The database hosting the table is not accessible
- The table is locked