Foglight for SQL Azure 5.5.8.6

Viewing the Database's Storage-related Data

The Databases drilldown’s panels allow carrying out the following tasks:

•

Viewing the database’s tables and their indexes—using the Tables & Indexes panel, which displays all of the database’s largest tables, by either size or row number. Selecting each table displays the indexes created for the table, and allows viewing detailed statistics for each index. For details, see Reviewing the Database’s Tables and Indexes on page 40.

•

Viewing the database’s storage utilization and the resulting estimated costs—using the Database Storage panel, which displays the database properties, its utilization and growth during the specified time range, with the resulting effect on the estimated costs. For details, see Reviewing the Storage Growth and the Cost-related Results on page 45.

•

Assessing the suitability of the current pricing plan—using the Estimated Storage Cost panel, which displays the database properties and the current storage cost model, alongside the estimated daily cost for the last days and the entire month. For details, see Reviewing the Estimated Storage Cost on page 47.

Reviewing the Database's Tables and Indexes

The Tables and Indexes panel contains the following sections:

•

Table list—provides detailed information about the disk space usage the database’s largest tables. For details, see Reviewing tables’ space usage on page 40.

•

Indexes list—displays all indexes created for the table selected on the Table list. For details, see Reviewing indexes for a selected table on page 42.


	IMPORTANT: To create a custom filter for these tables, use the options accessible by clicking the Customizer button at the upper right side of each table. For details, see Components Shared by all Foglight for SQL Azure Screens on page 13.

Reviewing tables' space usage

The Table list displays detailed information regarding all of the database’s largest tables.

To set a user-defined display of the Table list:

Set the criterion for a large table—either size or number of rows—by selecting a value from the Order By list.

Set the number of tables to be displayed, by selecting a value from the Top list.

To filter the display, proceed to Step 3. To display the entire list according to the parameters set now, go to Step 5.


	NOTE: The following steps, used for filtering the display, are optional.

Use the Table Name field to display either a specific table, by entering its exact name, or all tables that share a character string (for example: product), by entering that string.

Use the Owner field to display only tables owned by a specific owner.

Click Submit.

Table 1. The Tables List
Parameter	Description
Database Name	Name of the database, unique within an instance of SQL Azure.
In Row Free Size	The amount of space, in megabytes, within the table that is allocated to in-row data but not used.
In Row Reserved Size	The amount of space, in megabytes, reserved within the table for in-row data.
In Row Used Size	The amount of space, in megabytes, occupied within the table by in-row data.
Leaf Level Size	The amount of used space within the table that is occupied by leaf level (namely, data level only) entities, in megabytes.
LOB Free Size	The amount of space, in megabytes, within the table that is allocated to, but not used by, large object (LOB) data types (text, ntext, and image).
LOB Reserved Size	The amount of space, in megabytes, reserved within the table for large object (LOB) data types (text, ntext, and image).
LOB Used Size	The amount of space, in megabytes, occupied within the table by large object (LOB) data types (text, ntext, and image).
Non-leaf Level Size	The amount of used space within the table that is occupied by non-leaf level entities, in megabytes. Non leaf level are pointers to data; in tables, this term refers to data header pages.
Owner	The database owner’s name.
Partition ID	The ID of the partition. Together with the partition number, provides a unique identification of the partition within a table or an index.
Partition Number	Sequential number indicating the number of a partition within a table or an index.
Percent of DB	The percentage of space the selected table occupies within the entire database.
Row Overflow Free Size	The amount of space, in megabytes, within the table that is allocated to, but not used by, data types whose combined widths can exceed the maximum limit defined for a row.
Row Overflow Reserved Size	The amount of space, in megabytes, within the table that is reserved for data types whose combined widths can exceed the maximum limit defined for a row.
Row Overflow Used Size	The amount of space, in megabytes, within the table that is occupied by data types whose combined widths can exceed the maximum limit defined for a row, namely: varchar, nvarchar, varbinary, and sql_variant.
Rows	The number of rows in the table.
Table Cost Weight	The percentage the table occupies within the entire database size (including free space).
Table Name	The name of the table.
Total Free Size	The total amount of unused space in the table, in megabytes.
Total Reserved Size	The total amount of space reserved for the table, in megabytes.
Total Used Size	The total amount of space used by the table, in megabytes.

Reviewing indexes for a selected table

The Indexes list allows viewing and optionally re-collecting the SQL Azure index distribution statistics on any indexes in the currently selected SQL Azure database.

SQL Azure keeps statistics about the distribution of the key values in each index, and uses these statistics to determine which indexes to use in query processing. SQL Azure can be configured to automatically maintain the index statistics. Alternatively, these statistics can be maintained manually, using Transact-SQL commands.

Upon selecting a single index in the Tables list and clicking Submit, Foglight for SQL Azure retrieves the index statistics for that index and displays them in the Indexes table.

The index statistics in the Indexes table should be updated manually whenever the index was modified. Such a modification can be indicated as follows:

•

Change in the RowModCtr column—resulting from a significant modification of the key values in the index.

•

Change in the distribution of key values—resulting from addition, modification, or removal of a large amount of data in an indexed column.

•

Change in a specific table—if the table has been truncated using the TRUNCATE TABLE statement and then repopulated.

Initially, the Indexes pane displays only the Indexes table. Upon clicking an index in the table, the Index Properties popup appears. For details, see The Index Properties popup on page 43.