Foglight for SQL Server (Cartridge) 7.1.0

The Advisories dashboard is provided to continually analyze application performance to identify performance inefficiencies, to guide you through problem resolution strategies, and to deliver a step-by-step action plan for maximizing database performance.

The upper section of the Advisories dashboard includes general information on the monitored instance:

•

SQL server version

•

Host. For clustered host, the active host is displayed.

•

OS Version

•

Instance Collation

The lower section of the Advisories dashboard contains the following two elements:

•

Action Plan: Summarizes available advisories that present opportunities to increase the overall performance of your database.

•

Advisories: Lists advisories, action types, and the relevant descriptions. Click each advisory to view detailed analysis results, a complete description of the recommended action to be taken, and the background information of this tuning area.


	TIP: Advisories are listed in the order of their priority that considers the severity of the detected deviation and the type of advisory.

Refer to the table below for the Lists of advisories and short descriptions of their purpose:

General terms used in the Advisory List:

•

Active Time: Active time measures the amount of seconds all sessions inside connected to the instance used during their activity. This activity includes both using CPU and waiting for various resources like Locks, IO, etc. When the number gets bigger, it can indicate more sessions where connected, more waits were encountered, or both. Active Time rate shows how many activities were done each second on average and it is roughly correlate to the number of threads that were active.

•

Baseline: Normal range for a metric over a specific point in time.

Table 4. Foglight for SQL Server Advisory List
Advisory Name	Description
Overall Resource Consumption Exceeds the Baseline	Indicates that the database system’s performance during the specified time frame, exceeded the normal rate relative to a calculated baseline. Deviations outside the baseline range suggest abnormal or unexpected activity and should be investigated to determine whether they suggest a performance problem. Analyzed every 1 hour.
CPU Usage Deviation	Indicates that the total instance CPU usage exceeded the baseline. Deviations outside the baseline range suggest abnormal or unexpected activity and should be investigated to determine whether they suggest a performance problem. Calculated every 1 hour.
I/O Wait Deviation	Indicates that the total time spent waiting for I/O requests to finish, exceeded the baseline. I/O wait includes both waits for physical I/O operations and logical I/O (waits to read from the buffer pool). Deviations outside the baseline range suggest abnormal or unexpected activity and should be investigated to determine whether they suggest a performance problem. Analyzed every 1 hour.
Memory Wait Deviation	Indicates that the total time spent waiting on various wait types related to Memory, exceeded the baseline. Deviations outside the baseline range suggest abnormal or unexpected activity and should be investigated to determine whether they suggest a performance problem. Calculated every 1 hour.
Network Wait Deviation	Indicates that the total time spent waiting on various wait types related to Network, exceeded the baseline. Deviations outside the baseline range suggest abnormal or unexpected activity and should be investigated to determine whether they suggest a performance problem. Analyzed every 1 hour.
Lock Wait Deviation	Indicates that the total time spent waiting for locks to be released, exceeded the baseline. Deviations outside the baseline range suggest abnormal or unexpected activity and should be investigated to determine whether they suggest a performance problem. Calculated every 1 hour.
Latch Wait Deviation	Indicates that the total time spent waiting on various wait types related to Latch, exceeded the baseline. Deviations outside the baseline range suggest abnormal or unexpected activity and should be investigated to determine whether they suggest a performance problem. Analyzed every 1 hour.
Log Wait Deviation	Indicates that the total time spent waiting on various wait types related to Log, exceeded the baseline. Deviations outside the baseline range suggest abnormal or unexpected activity and should be investigated to determine whether they suggest a performance problem. Analyzed every 1 hour.
CLR Wait Deviation	Indicates that the total time spent waiting on various wait types related to CLR, exceeded the baseline. Deviations outside the baseline range suggest abnormal or unexpected activity and should be investigated to determine whether they suggest a performance problem. Calculated every 1 hour.
Remote Provider Wait Deviation	Indicates that the time spent waiting on various wait types related to Remote Provider, exceeded the baseline. Deviations outside the baseline range suggest abnormal or unexpected activity and should be investigated to determine whether they suggest a performance problem. Analyzed every 1 hour.
Other Wait Deviation	Indicates that the total time spent waiting on waits categorized as “Other”, exceeded the baseline. Deviations outside the baseline range suggest abnormal or unexpected activity and should be investigated to determine whether they suggest a performance problem. Analyzed every 1 hour.
XTP Wait Deviation	Indicates that the total time spent waiting on waits related to in-memory OLTP, exceeded the baseline. Deviations outside the baseline range suggest abnormal or unexpected activity and should be investigated to determine whether they suggest a performance problem. Analyzed every 1 hour.
Table Experiencing Lock Contention	The advisory highlights tables and indexes that are in the source of lock issues affecting the instance. It helps to identify the objects in question, analyzes the effect of locks on the entire system, and provides details to resolve them. Analyzed every 1 hour.
Deadlock Observed	The advisory is raised when the number of deadlocks per minute is bigger than 1. It includes details about the database, application, and objects that were involved in the deadlocks occurred. Analyzed every 1 hour.
Excessive Statements Re-compiles	The advisory is raised when the rate of recompiles compare to the amount of batches during the analyzed period is bigger than 10% and instance is CPU bound. Analyzed every 1 hour.
Buffer Latch Contention	The advisory is raised when the amount of waits for buffer latch is more than 10% of instance activity. It provides recommendations on how and what to tune to reduce it. Analyzed every 1 hour.
Excessive Memory Pressure	The advisory is raised when Foglight detects various symptoms that indicate memory pressure exists. It provides details on what those symptoms are and recommendations on how to fix it. Analyzed every 1 hour.
Excessive Log Wait	The advisory is raised when the amount of waits related to transaction log is more than 20% of instance activity. It provides recommendations on how and what to tune to reduce it. Analyzed every 1 hour.
Excessive I/O Wait	The advisory is raised when the amount of waits related to physical I/O is more than 5% of instance activity. It provides information about top statements consuming this I/O and details about missing indexes that may contribute to the situation. Analyzed every 1 hour.

Monitoring Data Replication

If the monitored SQL Server environment contains one or more instances that are configured as distributors (distributing instances) in a replication operation, Foglight for SQL Server now provides a replication monitoring module.


	IMPORTANT: Replication is monitored for instances running on SQL Server 2005 and higher.

The Replication Available flag indicates if an instance is configured as a distributor in these ways:

•

After the instance has been discovered (if the configuration as a distributor took place before the instance was discovered)

Upon the configuration as a distributor for monitored instances. Instances detected as distributors appear with the button Go to the Replication Home Page () next to the Show quick view button on the Name column of the Databases table.

The replication home page can be accessed using one of the following methods:

•

Click the button Go to the Replication Home Page.

Figure 5. The Replication button in the Databases table.

•

Click the Replication button on the QuickView panel.

Figure 6. The Replication button in the QuickView panel.

•

Click the link Investigate via Replication dashboard in the SQL Server Replication pop-up, which appears when you click the Replication icon under the Background Processes section of the instance's home page.

Figure 7. The Investigate using the Replication Drilldown link.

Viewing the Replication Home Page

The table identifies the main elements of the Foglight for SQL Server Replication Home page window, and provides a link to display more information, corresponding to the logical dataflow within the Replication Home page.

Table 5. The Main Elements of the Foglight for SQL Server Replication Home Page window.
Part Name	Description
Replication-related alarms	Monitoring Alarms
Publications drilldown	Monitoring the Replication Publications
Agents drilldown	Monitoring the Replication Agents
Subscriptions drilldown	Monitoring the Replication Subscriptions
Publishing pane	Reviewing the Publishing Process
Distributor Agents pane	Reviewing the Distributor Agents
Subscription pane	Reviewing the Subscription Process
Data flow indicators	Reviewing Data Flow Indicators

The following provides a description of panes of icons and gauges in the main activity areas:

•

Instance identification — used for identifying the instance, its type, and operation time. Identical to the instance identification section of the home page. For details, see Identifying the Instance .

•

Drilldowns — used for carrying out in-depth investigation of each of the main components of the replication process: publications, agents and subscriptions. Access drilldowns by clicking their button on the toolbar or the relevant link on their corresponding panes.

•

Components representing instance data flow — the main activity area in the Foglight for SQL Server Replication Home Page includes several flows that represent the data flow in the replication process. The data flow is described in this topic in a top-down design from the Publishing process to the Subscription process.

•

Panes — displays summarized numerical data about each of the components that take part in the replication-related processes of publishing, distributing, and subscribing. Clicking each component allows you to view a detailed list of the components (for example, Publisher servers). You can also access the relevant drilldown (for example, Publication Replications) for viewing more detailed information about the relevant component in the replication process.

Reviewing the Publishing Process

The Publishing pane provides data regarding the following components of the publishing process:

•

Publishing servers — displays the total number of publishing servers (Publishers) that are currently distributing publications using the selected instance. This number includes publishing servers that reside on the same computer as the distributing instance, or that reside on remote computers.

Clicking this row displays a pop-up, which contains the Publishing servers and Publication databases tables. Each of these tables shows the displayed entity’s name, status and highest severity level.


	NOTE: If more than one publishing servers exist, clicking each publishing server on the table displays only the publication databases residing under the selected server.

•

Publication databases — displays the total number of publication databases that reside under all currently distributing publishers.

Clicking this row displays a pop-up containing a table of all publication databases. This table is identical to the one shown when clicking the Publishing server row, except for the filtering by server, which is carried out by clicking the Server column.


	IMPORTANT: Clicking each publication database’s row opens the Replication Publications drilldown, which only displays publications stored on the selected publication database. To view the entire list of publications from all publication databases, click the Publications toolbar button.

•

Publications — displays the total number of publications that are currently distributed using the selected instance, along with an icon that indicates the highest severity level.

Clicking this row opens the Replication Publications drilldown, which allows reviewing publications by their type. For details, see Monitoring the Replication Publications .

Please select your product:

To serve you better, please complete the Purpose of your Chat:

Recommended Solutions for Your Problem

Foglight for SQL Server (Cartridge) 7.1.0 - User Guide

Advisories Dashboard

Monitoring Data Replication

Viewing the Replication Home Page

Reviewing the Publishing Process