Foglight for SQL Server (Cartridge) 5.9.3.20

Monitoring Log Files

The Log Files pane displays detailed information about all log files in the database currently selected in the Databases table.

Figure 23. The Log Files pane.

This pane includes the following areas:

•

Log Files table

•

Log File Space chart

•

Log File Growth chart

Log Files table

Table 42. The Log Files table displays information about all log files in the selected database.
Column	Description
File	The log file name.
File Group	The name of the file group to which the file belongs.
Type	The log file type.
Total Size	The size of the log file.
Used Size	The amount of the used space in the log file.
Free Size	The size of the currently unused space in the log file.
Used Pct	The percentage of used space within the log file.
Free Pct	The percentage of space available for use within the log file.
AutoGrow	Determines whether the log file can grow automatically.
Growths remaining	The number of times the file can AutoGrow before it can grow no more.
Max Size	The maximum size to which the log file can grow.
Growth Inc.	Growth increment. The amount by which the log file can AutoGrow at any one time.
Can Grow	Determines whether the log file can grow.
Path	The log file’s path.
Db and File	The database name, concatenated with the log file name.

Log File Space chart

The Log Files Space chart displays the total space allocated to each log file.

The chart values can be displayed in megabytes or as a percentage of disk space. Use the Log File Space list to change the values displayed on the chart.

Log File Growth chart

The Log File Growth chart shows the amount by which each log file is growing over time.

Use the Growth list to change the values displayed on the chart. This list can be displayed by the total amount of space (Total), used space (Used), or unused space (Free space).

Monitoring Tables and Indexes

The Tables and Indexes pane, which displays storage information for database tables and indexes, contains the following sections:

•

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

•

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

Figure 24. The Tables and Indexes pane.

Reviewing tables’ space usage

The Table list displays all tables in the selected database, including data such as their size, number of rows, free space, and owner name.

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.

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

Use the File Group Name field to display only tables belonging to a specific file group.

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.

Click Submit.

To create a custom filter for this table, use the options accessible by clicking the Customizer button at the table’s upper right side. For details, see Components Shared by all Foglight for SQL Server Screens .

The value displayed in the Table Size column indicates only the amount of space used by data, while the value displayed in the Used Size column indicates the amount of space used by both data and indexes, and the value displayed in the Reserved Size column indicates the entire amount of space reserved for data and indexes.

Table 43. The parameters displayed in the table are as follows:
Column	Description
Owner Name	The name of the table owner.
Table Name	The name of the table.
File Group Name	The name of the file group where the table is stored.
Table Size (MB)	The actual size of the table, in megabytes.
Reserved Size (MB)	The amount of space reserved for the table, in megabytes.
Used Size (MB)	The amount of used space in the table, in megabytes.
Free Size (MB)	The amount of free space in the table.
Percent of DB	The percentage of space that the table occupies in the database.
Rows	The number of rows in the table.
Reserved Memory	The amount of memory reserved for this table.
Used Memory	The amount of memory used by this table.
Number of Partitions	The number of partitions, if any.
Compression Type	What type of compression the table uses, if any.
Table Type	The type of table. Memory optimized, user tables and File tables are now included.

Reviewing Indexes for the selected table

The Indexes table shows the index statistics for all indexes in the selected tables. SQL Server stores statistics about the distribution of the key values in each index, and uses these statistics to determine which indexes to use in query processing.

Selecting a single row in the Indexes table displays detailed index statistics for that index in the lower section of the drilldown.

Table 44. The columns displayed in the Indexes table are as follows:
Column	Description
Owner Name	The owner of the index.
Table Name	The table that the index is associated with.
Index Name	The name of the index.
Index ID	The ID of the index.
File Group Name	The name of the File Group where the index resides.
Type	The type of the index.
No. of Keys	The number of keys in the index.
Index Size (MB)	The size of the index at the time of its last statistics update, measured in megabytes.
Used Size (MB)	The amount of used space in the index, in megabytes.
Free Size (MB)	The amount of free space in the index, in megabytes.
Rows	The number of rows in the table.
Row Mod Ctr	The number of row modifications made since statistics were last updated for this index.
Original Fill Factor	The fill factor used when creating an index, in order to reserve a percentage of free space on each data (leaf level) page.

This table allows selecting one or more indexes. When only one index is selected, its distribution statistics are displayed at the bottom part of the drilldown. When multiple indexes are selected, statistics are displayed only for the index with cursor focus (indicated by a black box around the index row).

Clicking a single index in the Indexes table displays the Index Properties pop-up.

The Index Properties pop-up contains the tables described in the following sections:

•

Index Density table

•

Fragmentation table

•

Index Distribution chart

Index Density table

The Index Density table shows the density values for each combination of columns in the index.

Table 45. The Index Density table contains the following columns:
Statistic	Description
Columns	The name of the columns in the index.
Density	Density is the term used by SQL Server to represent the selectivity of the index columns. The more selective an index is, the more useful it is in searches. Density is calculated as 1 / distinct values for all values in the first key column of the statistics object, excluding the histogram boundary values. NOTE: Starting from SQL Server 2008, the density values is not used by the query optimizer, and is displayed only for backward compatibility with earlier versions.
Average Key Length	Average number of bytes per value for all of the key columns in the statistics object.

Fragmentation table

The Fragmentation table displays all indexes in the selected tables and shows the latest fragmentation information collected for each index.

Fragmentation information is collected by running the DBCC SHOWCONTIG command on each selected index.


	NOTE: Running DBCC SHOWCONTIG on large tables can take a long time and put significant load on the server.

For information about the Fragmentation statistics displayed on the Fragmentation page, see the DBCC SHOWCONTIG topic in the Transact-SQL Reference section in the Microsoft® SQL Server Books Online.

How fragmented are my tables?

The following values can be used for determining the overall fragmentation in tables:

•

Extent Scan Fragmentation and Logical Scan Fragmentation are expressed as percentage values.


	NOTE: A value of -1 is displayed for heaps (tables without indexes) and for text structures.

•

Average Page Density shows how full each page is. This is affected by the FILL FACTOR setting used when creating the index; for example, immediately after creating an index with a FILL FACTOR of 30, the Average Page Density for that index is 30. This value is then change as data in the table is modified. Average Page Density can be thought of as the “real” fill factor at the time the data was collected, as opposed to the “original” fill factor that was specified when the index was created. The Original Fill Factor is used when an index is created to leave free space in index pages to allow for the insertion of new records without having to split the page.

•

Fill Factor Variance is a computed metric, used for comparing the Average Page Density with the original fill factor used when creating the index. The value of this metric is calculated as the absolute difference between the average page density (AvgPage Density) and Original Fill Factor. The value of this variance shows how much the fill factor of the index has changed since the index was created.

•

Scan Density is a value computed by dividing the optimal number of extent switches by the actual number of extent switches. It is expressed as a percentage value, where higher values indicate less fragmentation. Scan density is displayed with a value of -1 for tables that reside on more than one file.

Removing fragmentation

Several options are available for eliminating (or at least reducing) fragmentation. In general, fragmented indexes should be rebuilt by SQL Server. For details, see Microsoft® SQL Server Books Online.

Index Distribution chart

When a single index is selected in the Indexes table, the Index Distribution chart displays the index distribution histogram for the selected index.

When SQL Server collects statistics on an index for use in determining the indexes usefulness for resolving queries, it samples the data in the index and produces a histogram of the key values found. This chart shows that histogram for the selected index.

The values shown in the Index Distribution chart derive from the latest statistics for the selected index. If the index statistics are out of date, these values are also outdated. Check the Last Updated statistic on the Statistics page for the date when statistics were last collected. Use the Update Statistics SQL command to re-collect statistics.

Each series in the chart corresponds to one sample value from the first column in the index. The chart shows the number of rows in the table that have that value.


	NOTE: SQL Server does not inspect every key value, but uses only sampling. The histogram displays the percentage of the table that falls in a sample range. Not all of the records counted have the exact sample value shown.

Example:

If the selected index is on a Name column, the chart may display the following values:

•

•

•

•

•

This means that five of the people have a name that is alphabetically before or equal to ABRAHAM, 20 people are between ABRAHAM and GEORGE, 25 people are between GEORGE and PETER, and so on.

This chart can be used for identifying skewed indexes, that is, indexes that have a very uneven distribution of data. For example, if 95% of the table has the same value for a key, it is difficult for SQL Server to determine if it is useful index. Searches on values that fall in the 5% might find the index very useful, but searching on the value that occurs 95% of the time are not so useful.

Monitoring Disk Space

The Disk Space pane displays storage information about disks that reside on the currently monitored server.

The term disk in this tab is used on the assumption that each physical disk contains only one partition. If the disk is divided to several partitions (logical drives), the tab displays disk space utilization per partition, and not a summary of the utilization of the entire physical drive.

This pane is designed to answer the following questions:

•

How much space are the SQL Server database files using on each disk?

•

What file space is available?

This pane features the Disk Space chart, which displays each disk on the server. The display is divided into SQL Data files, SQL Log files, Non-SQL files or Free Space. This chart allows to easily view how much of each disk is used by SQL databases, and how much is free.

Table 46. The Disk Space chart displays several parameters:
Parameter	Description
Data Used	The amount of space on the disk used by data files in the currently monitored SQL Server databases.
Data Free	The amount of space on the disk allocated to data files in the currently monitored SQL Server databases but not used.
Log Used	The amount of space on the disk allocated to SQL Server log files for the currently monitored databases.
Log Free	The amount of space on the disk allocated to log files in the currently monitored SQL Server databases but not used.
Disk Free	The amount of space on the disk not used by any files.
Non SQL Files	The amount of space on the disk used by files not associated with the currently monitored SQL Server databases.

To include the Disk Free and the Non SQL files parameters on the chart, select the box Include Non-SQL files in the chart.

To restrict the data and log-related figures to display only the space used by specific databases, select one or more of the databases in the Databases table.

To choose whether to display the chart in megabytes (MB) or percentage, use the Disk Space Utilization drop-down list box.


	NOTE: The space shown for SQL data and log files refers only to files in this instance of SQL Server. If multiple SQL Server instances are running on this server, the data/log files for other instances are included in the Non-SQL Files figure.

If the Windows server being monitored does not have Logical Disk performance counters enabled, the value of the Non-SQL Files component of this chart is always zero. If disk counters are not enabled, the Disk Counters Disabled alarm is displayed on the home page. To enable disk counters, it is advisable to run the exctrlst.exe utility, provided by Microsoft.

To use the exctrlst.exe utility:

Download the utility from the following location:

http://www.microsoft.com/downloads/details.aspx?displaylang=en&familyid=7FF99683-B7EC-4DA6-92AB-793193604BA4

Double-click the exctrlst.exe binary file, to display the Extensible Counter List dialog box.

Use this dialog box to either enable the Performance Counter Resource indicated by the Collector log, or manually review every performance counter to ensure that its check box is selected.