Foglight for SQL Server (Cartridge) 5.7.5.30

The Databases table provides details about all of the selected SQL Server databases within the selected instance. The information displayed in the table can be changed by selecting, from the View list, one of the options detailed in the following sections:

•

Overview — general information about each database.

•

Transactions — details about the oldest active transaction in each database.

•

Backup Status — details on database backups, including date and total file sizes.

•

Properties — the properties of the various SQL Server databases.

•

In Memory -

Overview

To refresh

Table 33. The Overview view displays general information about each of the monitored databases.
Parameter	Description
dbid	ID of the database, unique within an instance of SQL Server.
Database Name	Name of the database, unique within an instance of SQL Server.
Status	The database’s current status (for example, ONLINE, OFFLINE, RESTORING, or SUSPECT).
Database Space	Provides a visual representation of the database size and space usage within each database.
Data Size	The total size allocated to data files.
Data Used Size	The total size actually used by data files in the database.
Data Free Size	The total available data size (allocated to data files but not used).
Log Size	The total size allocated to log files.
Log Used Size	The total size actually used by log files in the database.
Log Free Size	The total available log size (allocated to log files but not used).
# Tables	The number of tables, where table type=user, which exist in the database.
# Indexes	The number of indexes that exist in the database.
# File Groups	The number of file groups that exist in the database.
Last Backup	The date and time of the database’s most recent backup.
Last Backup Full Time	The date and time when the most recent full database backup of any type took place.
Last Backup Differential Time	The date and time when the most recent differential database backup of any type took place.
Last Backup Log Time	The date and time when the most recent database log backup of any type took place.
NOTE: The Last Backup Full Time, Last Backup Full Differential Time, and Last Backup Log Time columns are not displayed by default; to view these columns, click the Customizer button at the right side of the table
Recovery	The recovery model for the database; for example, Simple, Bulk Logged, or Full.

Transactions

The Transactions view displays details about the oldest active transaction in each of the monitored databases. A transaction open for a long time may result in degraded performance.

Table 34. The Transactions view displays the following parameters:
Parameter	Description
dbid	ID of the database, unique within an instance of SQL Server.
Database Name	Name of the database, unique within an instance of SQL Server.
Oldest Tran Start Time	The date and time at which the oldest active transaction in the database began.
Oldest Tran SPID	The system process ID of the session that owns the oldest active transaction in the database.

To view the session details of a transaction:

Select the SQL Activity drilldown > Sessions panel > Sessions table.

In the Sessions table, browse for the requested SPID.

Click the SPID number to display the Session Details page.

Use this page to review the information relevant for handling the transaction, such as the transaction count and the text of the most recent SQL batch.

Backup Status

The Backup Status view displays backup details for the database, such as the data size and the date and time of the most recent backup of any type for the database.


	NOTE: The summarized value of the data size and the log size indicates the anticipated size of the backup file.

Table 35. The Backup Status view displays the following parameters:
Parameter	Description
dbid	ID of the database, unique within an instance of SQL Server.
Database Name	Name of the database, unique within an instance of SQL Server.
Status	The database’s current status (for example, ONLINE, OFFLINE, RESTORING, or SUSPECT).
Data Size	The total size allocated to data files.
Log Size	The total size allocated to log files.
Last Backup	The date and time of the most recent backup operation.
Last Backup Full Time	The date and time when the most recent full database backup of any type took place.
Last Backup Differential Time	The date and time when the most recent differential database backup of any type took place.
Last Backup Log Time	The date and time when the most recent database log backup of any type took place.
NOTE: The Last Backup Full, Differential, and Log Time columns are not displayed by default; to view these columns, click the Customizer button at the right side of the table.

Properties

The Properties view displays database properties that correspond to the data returned by the SQL Server DatabasePropertyEx function. For more information about these properties, see the Transact-SQL Reference section in Microsoft® SQL Server Books Online.

In Memory

The In Memory view displays metrics on space usage by objects in memory. To view details for a specific database, click on the database row. Detailed metrics will be displayed below the table.

Table 36. The In Memory view displays metrics on space usage by object in memory
Parameter	Description
dbid	The database ID number.
Database Name	The name of the database.
XTP Enabled	Whether or not the database is XTP enabled.
Resource Pool Name	The name of the resource pool allocated to the database.
Percent of Pool	Total memory usage of the objects within the database.
Tables Allocation	Total amount of memory (in MB) allocated for the tables.
Indexes Allocation	Total amount of memory (in MB) allocated for the indices.
System Allocation	Total amount of memory (in MB) allocated for the system.
Table Used	Total amount of memory (in MB) used by the tables, including row versions.
Indexes Used	Total amount of memory (in MB) used by the indices, including row versions.
System Used	Total amount of memory (in MB) used by the system, including row versions.

Monitoring Database Details

You can display detailed data for a database. by selecting the database row in any one of the tables displayed on the Overview, Transactions, Backup Status, Properties or In Memory tabs.

The Database Details panel allows carrying out the following tasks:

•

Monitoring database space and history — using the Summary pane, which displays a graphical representation of the disk space usage for databases, as well as various statistics for each database. For further details, see Monitoring Database History.

•

Monitoring file groups — using the File Groups pane; see Monitoring File Groups in the Selected Databases.

•

Monitoring data files — using the Data Files pane, which displays all files (excluding the Transaction Log) in the selected databases; see Monitoring data files.

•

Monitoring transaction logs — using the Transaction Logs pane; see Monitoring Transaction Logs in the Selected Database.

•

Monitoring log files using the Log Files pane; see Monitoring Log Files.

•

Monitoring tables — using the Tables & Indexes pane, which displays statistics for all tables and indexes in the selected databases (by default, 50 rows are displayed); see Monitoring Tables and Indexes.


	NOTE: The panes from File Groups to Tables and Indexes display tabular and graphical information about other areas down the storage level hierarchy, currently selected in the Databases table. The charts on each of these panes display the amount of space allocated to the area for each selected database (Space Chart), and the amount by which each of the areas is growing over time (Growth Chart).

•

Monitoring disk space — using the Disk Space pane, which displays each logical disk on the server, with a detailed graphical representation of the disk space usage for all disk. For further details, see Monitoring Disk Space.

Monitoring Database History

The Summary pane displays three separate charts showing various statistics relating to recent I/O activity on each database. You can select which data each graph should display.


	NOTE: The graphs in the chart are available for each database.

To view a particular graph, select it from the Database History list.

Table 37. The following graphs are available for each database:
Graph	Description
% Log Used	The amount of file space allocated to the transaction log of each currently used database. Log space can be freed up by backing up the log, or truncating it, using the truncate option (backup log <dbname> with truncate_only). NOTE: All log records that exist after the oldest open transaction cannot be freed.
Data File Size	The amount of disk space that the data files are using. This graph indicates how the size of data files has changed over time.
Log File Size	The amount of disk space that the log files are using. This graph shows how the size of log files has changed over time.
Active Transactions	The number of open transactions in each database.
Transactions Rate	The rate of transactions for each database.
Log Cache Hit Rate	Percentage of log cache reads satisfied from the log cache. This metric reflects the amount of physical log I/O that is being avoided by caching log data. The log caches normally have a non-zero hit rate only if Rollback activity is taking place.
BCP Throughput Rate	The rate (measured in kilobytes per second) at which data is being loaded into the database using BCP (Bulk Copy Program) or BULK INSERT. Despite the speed at which BCP and BULK INSERT can import data into SQL Server, system I/O performance may degrade while BCP operations are underway.
Backup Throughput Rate	The rate (measured in kilobytes per second) at which backup or restore operations are reading or writing to the database. Throughput of a database backup or restore operation allows determining the progress and performance of these operations; for example, measuring how the performance of the database backup operation changes when more backup devices are used in parallel or when faster devices are used.
DBCC Scan Bytes Rates	The rate (measured in kilobytes per second) at which Database Console Commands (DBCC) are processing data. This rate represents the number of logical read scan kilobytes per second for database command console (DBCC) statements.
Log Flushes Rate	The rate at which the log cache for each database is being flushed to disk, which is necessary to guarantee that transactions can be recovered in the event of a system failure.
Log Flush Wait Time	The amount of time spent, in milliseconds, waiting for log flushes in each database. High log flush wait time can be caused by a slow or overworked disk subsystem. If a database has a consistently high Log Flush Wait Time that never changes, run the SQL command CHECKPOINT on that database to force another log flush and re-check the value in Foglight for SQL Server.
Log Growths Rate	The number of times the log has been expanded for each database.
Log Shrinks Rate	The number of times the log has been reduced for each database.
Log Truncations Rate	The frequency of log truncations for each database.

Monitoring File Groups in the Selected Databases

The File Groups pane displays detailed information about all file groups in the databases that are currently selected in the Databases table.

This pane includes the following areas:

•

File Groups table

•

File Group Space chart

•

File Groups Growth chart

File Groups table

The File Groups table shows information about all file groups in the selected database. File stream types (used by memory optimized objects and file-stream objects) are now supported.

Table 38. This table displays the following parameters:
Column	Description
File Group	The name of the file group in the database.
File Count	The number of files in each file group.
Type	The type of file group; for example, data or log file.
Total Size	The total size of all files in the group.
Used Size	The total size of used space of all files in the group.
Used Pct	The percentage of used space of all files in the group.
Free Size	The total size of free space of all files in the group.
Free Pct	The percentage of free space of all files in the group.
Can Grow	Indicates whether the files in the file group (if any) can grow automatically as they fill.

File Group Space chart

The File Groups Space chart displays the amount of space allocated to each file group. Each file group consists of used space and free space.

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

File Groups Growth chart

The Growth chart displays the amount by which each file group 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 (File total size), used space (File used size), or unused space (File free size).

Monitoring data files

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

Figure 19. The Data Files pane.

This pane includes the following areas:

•

Data Files table

•

Data Files Space chart

•

Data Files Growth chart

Data Files table

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.

Table 39. The Data Files table displays the following indicators for all database data files:
Column	Description
Files	The name of the data file.
File Group	The name of the file group to which the file belongs.
Type	The type of file group; for example, data or log.
Total Size	The size of the file.
Used Size	The total size of the file’s used space.
Free Size	The total size of the file’s free space.
Used Pct	The percentage of the file’s used space.
Free Pct	The percentage of the file’s free space.
Auto Grow	Indicates whether the file can grow automatically.
Growths Remaining	The number of times the file can grow, considering its currently configured autogrow increment, before exceeding its space limits.
Max Size	The maximum size to which the file can grow.
Growth Inc.	Growth increment. Indicates the amount by which the file grows every time, if the autogrow option is enabled. for example, if the data file’s initial size is 1 megabyte and it has to grow to a final size of 11 megabytes, setting the Growth Inc. parameter to 2 megabytes causes the file to grow five times, each time by a 2 megabyte increment.
Path	The path of the data file.
Db and File	The database name, concatenated with the database file name.

Data Files Space chart

The File Space chart displays the amount of space allocated to the data file for each selected database. Each data file consists of used and free space.

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

Data Files Growth chart

The Data Files Growth chart displays, in megabytes, the amount by which each 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 (File Total Size), used space (File Used Size) or unused space (File Free Size).

Monitoring Transaction Logs in the Selected Database

The Transaction Logs pane displays detailed information about all transaction logs in the database currently selected in the Databases table.

Figure 20. The Transaction Logs pane.

This pane includes the following areas:

•

Transaction Logs table

•

Transaction Log Space chart

•

Transaction Log Growth chart

Transaction Logs table

The Transaction Logs table displays information about all transaction logs in the selected databases, as detailed in the following table.

Table 40. The Transaction Logs table details.
Column	Description
File Group	The name of the file group to which the file belongs.
File Count	The number of transaction logs in the database.
Type	The type of file; for example, log.
Total Size	The total size of all files in the transaction log.
Used Size	The amount of used space.
Used Pct	The percentage of used space within the log file.
Free Size	The amount of free space.
Free Pct	The percentage of space available for use within the log file.
Can Grow	Indicates whether any of the files in the transaction log can grow.

Transaction Log Space chart

The Transaction Log Space chart displays used space and free space for each of the selected databases’ transaction logs.

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

Transaction Log Growth chart

The Transaction Log Growth chart shows the amount by which each transaction log 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 either all transaction logs (File Total Size), used logs database (File Used Size), or unused transaction logs (File Free Size).

Monitoring Log Files

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

Figure 21. 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 41. 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 22. 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.

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 42. 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.

Table Space chart

The Table Space chart displays the amount of space used by each table, as well as the amount of space reserved for each table.

The chart values can be displayed in megabytes or as the number of rows. Use the Table Space list to change the values displayed on the chart.

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 43. 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 44. 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 45. 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.

Monitoring TempDB Status

The TempDB system database is a global resource that exists in any SQL Server instance and is used for various functions within it. There is one such database in every instance.

It is used to hold the following:

•

User objects — Temporary objects explicitly created by users such as global or local temporary tables, temporary stored procedures, table variables, or cursors.

•