Chat now with support
Chat with Support

Foglight for SQL Server (Cartridge) 6.3.0 - User Guide

Introduction to this Guide Using Foglight for SQL Server
Viewing the Databases Dashboard Overview Dashboard Advisories Dashboard Monitoring Data Replication Monitoring SQL Performance Reviewing Memory Usage Reviewing the Instance Activity Reviewing Database Usage Reviewing the Services Using the HADR Drilldown Using the Logs Drilldown Reviewing Configuration Settings Viewing User-defined Performance Counters and Collections
Monitoring Business Intelligence Services Administering Foglight for SQL Server
Configuration Settings Managing Foglight for SQL Server Agent Settings Reviewing Foglight for SQL Server Alarms Generating Reports Monitoring SQL Server instances on VMware servers
Glossary
A
Access methods Adhoc SQL Plans Alarm Alert Allow updates Anonymous subscription Authentication AutoClose AutoGrow Automatic Discovery AutoShrink B Batch BCP (Bulk Copy Program) Blocking Books Online Bound trees Buffer Buffer cache Buffer pool Bulk copy Bulkinsert Bulk load C Cache CAL Calibration Cardinality Cartridge Chart Checkpoint process Client network utility CLR Compile Connect Connection Connectivity software CPU Usage Cursors D Data access components Data file Data storage engine Database Database object DataFlow DBCC DBID DBO Deadlock Disk queue length Disk transfer time Disk utilization DiskPerf Distributing instance Distributor DMO Drilldown DTC DTS E Error log Event alert Execution contexts Extended stored procedures Extent External procedures F File Filegroup File cache Foglight Agent Manager Foglight Management Server Foreign key Forwarded records Free buffers Free list Free pages Free potential Free space Freespace scans Full text search G GAM Ghosted records Growth increment GUID H Hash buckets Hashing Heap Hit rate Hit ratio Host name Host process I I/O Index Indid Integrated security Intent Locks J Job K Kernel memory Kernel mode Kill L Latch Lazy writer Licensing Lightweight pooling Lock Lock area Lock escalation Lock mode Log Log cache Log writer Logical I/O LRU LSN M Master MaxSize MDAC Metric Misc.normalized trees Model Monitor page file N Named pipes Net library NIC Null O OBID Object plans OLAP OLAP service OLTP Optimizer Optimizer cache osql P Page life expectancy Paging Panel Parse Parser Per seat licensing Per server licensing Performance alert Physical I/O Physical read Physical write PID Pinned Plan Plan cache Potential growth Prepared SQL plans Primary key Privileged mode Procedure cache Procedure plans Process Profiler Publication database Publisher Publisher databases Publishing server Pull subscription Pulse Push subscription Q Query plan R RAID Random I/O Read ahead Recompile Referential integrity Relational data engine Replication procedure plans Role Rollback S sa Schema locks Sequential I/O Session Severity SGAM Shared locks Show advanced options SMP Sort, Hash, Index Area SPID Spike Spinner SQL Agent Mail SQL Mail SQL Plans SQL Server Agent SQL Server authentication SQL Server books online Standard deviation Stolen pages Stored procedure Support service SYSADMIN role T TDS TempDB Temporary tables and table variables Threshold Torn page detection Transaction Trigger Trigger plans Truncate Trusted U UMS Unused space User connection area User mode V Virtual log file VLF W Waitfor Windows authentication mode Working set
Reference
SQL Performance Investigator Metrics
Active Time All SQL Agents CPU Usage All SQL Agents Resident Memory Usage Availability Average Physical I/O Operations Average SQL Response Time Backup Recovery Wait Blocked Lock Requests Checkpoint Pages CLR Wait CPU Usage CPU Wait Cursor Synchronization Wait Database Replication Wait Deferred Task Worker Wait Degree of Parallelism Disk Utilization DTC CPU Usage DTC Resident Memory Usage Distributed Transaction Wait Executions Ended Executions Started External Procedures Wait Full Scans Full Text Search CPU Usage Full Text Search Resident Memory Usage Full Text Search Wait Free Buffer Wait Hosted Components Wait IO Bulk Load Wait IO Completion Wait IO Data Page Wait IO Wait Latch Buffer Wait Latch Wait Latch Savepoint Wait Lazy Writes Lock Wait Lock Bulk Update Wait Lock Exclusive Wait Lock Intent Wait Lock Requests Lock Schema Wait Lock Shared Wait Lock Update Wait Lock Wait Log Buffer Wait Log Flushes Log Other Wait Log Synchronization Wait Log Wait Log Write Wait Memory Wait Network IO Wait Network IPC Wait Network Mirror Wait Network Wait Non SQL Server CPU Usage Non SQL Resident Memory Usage OLAP CPU Usage OLAP Resident Memory Usage OLEDB Provider Full Text Wait Other CPU Usage Other Miscellaneous Wait Other Wait Overall CPU Page Life Expectancy Page Splits Parallel Coordination Wait Physical I/O Physical Memory Used Physical Page Reads Physical Page Writes Probe Scans Plan Cache Hit Rate Range Scans Rec Ended Duration Remote Provider Wait Run Queue Length Samples Service Broker Wait Session Logons Session Logoffs SQL Agent CPU Usage SQL Agent Resident Memory Usage SQL Executions SQL Mail CPU Usage SQL Mail Resident Memory Usage SQL Recompilations SQL Response Time SQL Server Background CPU Usage SQL Server Cache Memory SQL Server Connections Memory SQL Server Connections Summary SQL Server Foreground CPU Usage SQL Server Resident Memory Usage SQL Server Swap Memory Usage Synchronous Task Wait Table Lock Escalation Target Instance Memory Total CPU Usage Total Instance Memory Virtual Memory Used
Rules Collections and Metrics
SQL Server Agent's Default Collections Access Methods Agent Alert List Agent Job List Always On Availability Groups Backup Locations Blocking History Blocking List Buffer Cache List Buffer Manager CLR Assemblies Cluster Summary Configuration Database Index Density Vectors Database Index Details Database Index Fragmentation Info Database Index Histogram Database Index List Database Information Database Properties Database Sessions (Session List) Database Summary Database Tables List Databases Deadlock DTC Information Error Log Error Log List Error Log Scan File Groups File Data Flow Statistics File Groups Files Files Drive Total Files Instance Summary Full Text Catalog InMemory OLTP (XTP) Instance Wait Categories Instance Wait Events Job Messages Latches and Locks Lock Statistics Locks List Log Shipping Log Shipping Error Logical Disks Long Running Session Memory Manager Mirroring Mirroring Performance Counters Missing Indexes Plan Cache Distribution Plan Cache List Replication Agents Replication Agent Session Actions Replication Agent Session Merge Articles Replication Agent Sessions Replication Agent Sessions by Type Replication Available Replication Publications Replication Subscriptions Reporting Services Resource Pool Session Data Session Trace SQL PI Instance Statistics SQL Server Connections Summary SQL Server Global Variables SQL Server Host SQL Server Load SQL Server Services SQL Server Throughput SQL Server Version Info SSIS OS Statistics SSIS Summary Statistics Top SQLs Top SQL Batch Text Top SQL Long Text Top SQL Plan Top SQL Short Text Top SQL Summary Traced SQL PA Usability User-defined Performance Counters User-defined Queries Virtualization XTP Session Transactions Statistics

Reviewing the Always On Availability Groups

If one or more databases within the monitored instance is of version SQL Server 2012 Enterprise Edition, is part of an OS cluster and has the Always On feature enabled, the Always On panel allows viewing the status of the Always On availability groups that reside on the cluster.

* 
NOTE: Always on cannot function at the same time with Mirroring operation: enabling Mirroring requires disabling Always on and conversely. Therefore, if the monitored instance contains databases that support Always On but have Mirroring enabled, the Always On panel displays the note This instance has no database configured for Always On.

The Always On panel includes the following sections:

•
OS Cluster information — see OS Cluster Information .
•
High Availability groups table — displays all High Availability groups that are configured on the instance. For details, see High Availability groups table .
•
Availability group map — displays the dependency (primary/secondary relations) between the databases in the groups selected in the High Availability groups table, or between all groups in the table. For details, see Availability group map .

OS Cluster Information

The OS cluster information section displays the following details:

•
OS Cluster Name — the name of the OS cluster hosting the SQL Server instances that are enabled for AlwaysOn Availability Groups.
•
Quorum Type — the type of quorum used by the selected OS cluster; can have one of the following values:
•
0 — Node Majority.
The cluster is defined as healthy only if indicated so by more than 50% of the voting nodes in the cluster. For example, on a nine node cluster this quorum configuration can sustain up to four node failures.
•
1 — Node and Disk Majority.
This mode is similar to Node Majority quorum mode, with the addition of a shared disk cluster resource as a voting witness. More than 50% of the cluster’s possible votes must be affirmative in order to determine the cluster’s state as healthy, and connectivity from any node to that shared disk is also counted as an affirmative vote.
•
2 — Node and File Share Majority.
This mode is similar to Node Majority quorum mode, with the addition of a remote file share configured as a voting witness. More than 50% of the cluster’s possible votes must be affirmative in order to determine the cluster’s state as healthy, and connectivity from any node to that remote file share is also counted as an affirmative vote.
•
3 — No Majority: Disk Only.
A shared disk cluster resource is designated as a witness, and connectivity by any node to that shared disk is counted as an affirmative vote.
•
Quorum State — the state of the quorum used by the selected OS cluster; can have one of the following values:
•
0 — Unknown quorum state
•
1 — Normal quorum
•
2 — Forced quorum

High Availability groups table

The High Availability group table displays information about the availability and health of all availability groups configured in the instance and the databases they serve.

IMPORTANT: It is possible that one or more the availability replicas that take part in a group are not currently monitored. In such a case an indication appears near the replica in the availability group map. Click this indication to launch the instance monitoring wizard. For details, see Availability group map .

The High Availability group table includes the following columns:

•
HA Group Status — displays an icon that indicates the worst severity reported within the group from the group alarms.
•
Group Name — displays the High Availability group name.
•
Role — the current state of the database that runs on the selected instance and belongs to the selected group; either Secondary or Primary. When there is no value for Secondary/Primary, this column should indicate the status Off line.
•
Alarms — displays alarms related to the health of the availability group or its associated databases.
•
Max Estimated Recovery Time — displays the maximum value of the estimated recovery time parameter within all databases that belong to the selected group. If all databases are fully synchronized, this column displays a zero value.
•
Max Estimated Data Loss — displays the maximum value of the estimated data loss parameter within all databases that belong to the selected group. If all databases are fully synchronized, this column displays a zero value.
•
Explore — contains a shortcut icon which, when clicked, displays the Availability Group details pop-up.
Figure 26. Availability Group details pop-up.

This pop-up contains the Group Info, Availability Replica, and Databases tabs.

The Group Info tab includes the following fields:

•
Primary replica — the primary replica’s name.
•
Primary recovery health — indicates the primary replica’s recovery health, with the following possible values: In progress, Online or Null.
•
Synchronization health — summarizes the synchronization status of all replicas in the availability group:
•
Healthy — if all replicas are fully synchronized.
•
Partially healthy — if only some of the replicas are fully synchronized.
•
Not healthy — if no replica is fully synchronized.
•
Automated backup preference — indicates whether backups should run on the primary replica. The possible values are:
•
0 - Primary — backups should always be carried out on the primary replica.
•
1 - Secondary_only — performing backups on a secondary replica is preferable.
•
2 - Secondary — performing backups on a secondary replica preferable, but performing backups on the primary replica is acceptable if no secondary replica is available for backup operations. This is the default behavior.
•
3 - None — no preference about whether backups are performed on the primary replica or on a secondary replica.

The Availability Replica tab lists all replicas associated with the group. This tab includes the following fields:

•
Server Name — the name of the server.
•
Join State — can have the following values:
•
Not joined
•
Joined standalone — joined as a standalone instance
•
Joined Failover — joined as failover cluster instance
•
Role — either Primary or Secondary.
•
Operational State — indicates whether the replica is online, offline or in a pending stage. The possible values are as follows:
•
Pending failover
•
Pending
•
Online
•
Offline
•
Failed, no quorum
•
Null — replica is not local
•
Connected State — indicates whether a secondary replica is currently connected to the primary replica. Possible values are either Connected or Disconnected.
•
Recovery Health — indicates whether all databases joined to the availability group on the availability replica, are online, or are being recovered after a failover. Possible values are:
•
Online
•
In progress — recovering from a failover
•
Synchronization Health — summarizes the synchronization status of all replicas in the availability group:
•
Healthy — if all replicas are fully synchronized.
•
Partially healthy — if only some of the replicas are fully synchronized.
•
Not healthy — if no replica is fully synchronized.
•
Secondary Role Allow Connections — indicates whether an availability replica that is performing the secondary role (that is, a secondary replica) can accept connections from clients:
•
No — no connections are allowed to the databases in the secondary replica, and the databases are not available for read access. This is the default setting.
•
Read only — read-only connections have exclusive access to the databases in the secondary replica. All databases in the replica are available for read access.
•
All — all connections are allowed to the databases in the secondary replica for read-only access.
•
Availability Mode — can have either of the following values:
•
Asynchronous commit — the primary replica can commit transactions without waiting for the secondary to write the log to disk.
•
Synchronous commit — the primary replica waits to commit a given transaction until the secondary replica has written the transaction to disk.
•
Failover Mode — either Manual or Automatic.

The Databases tab displays all databases associated with the group. This tab includes the following fields:

•
Replica Server Name — the name of the server.
•
Database Name — the name of the database.
•
Database State — indicates whether the database is online, offline or in a pending stage. The possible values are as follows:
•
Online
•
Restoring
•
Recovering
•
Recovery pending
•
Suspect
•
Emergency
•
Offline
•
Synchronization State — indicates the data movement state, and can have one of the following values:
•
Not Synchronizing
•
Synchronizing
•
Synchronized
•
Reverting pages from undo log
•
Initializing Undo log
•
Synchronization Health — summarizes the synchronization status of all replicas in the availability group:
•
Healthy — if all replicas are fully synchronized.
•
Partially healthy — if only some of the replicas are fully synchronized.
•
Not healthy — if no replica is fully synchronized.
•
Estimated Recovery Time (sec) — the estimated time that the secondary replica will require in order to catch up with the primary replica.
•
Estimated Data Loss (sec) — the time difference of the last transaction log record between the primary and secondary replicas. Failure of the primary replica will result in the loss of all transaction log records within the time window.
•
Is Failover Ready — indicates whether the secondary database is synchronized in the cluster with the corresponding primary database. Possible values are Yes or No.

Availability group map

The Always On configuration supports one primary replica and up to four secondary replicas for each availability group. The Availability Group Map section allows viewing the dependencies between the various replicas that form the availability group selected in the table. In addition to indicating the name of the OS cluster where the availability replicas reside, the map displays the connections between the primary replica and the secondary replicas, showing on the left the replica that resides on the currently monitored instance.

* 
IMPORTANT: On each replica there is an indication of the highest severity level encountered on the replica. A replica that is not currently monitored is indicated by the icon. Click this icon to launch the wizard used for configuring an instance for monitoring.

Clicking the monitored replica displays a pop-up with the following tabs:

•
Group Info — identical to the Group Info tab on the pop-up that appears upon clicking the Explore column on the High Availability groups table. For details, see High Availability groups table .
•
HA Summary — displays the selected replica's availability-related alarms, including the severity and the error message.
•
Databases Health — a table displaying all alarms sent during the specified time range, including the severity and the error message.
•
Listeners — if one or more availability group listeners are configured, displays the listeners' names and their IP status, either Online or Offline.

Clicking any other replica displays a pop-up with the following tabs:

•
HA Summary — displays the selected replica's availability related alarms, including the severity and the error message.
•
Databases Health — a table displaying all alarms sent during the specified time range, including the severity and the error message.

To view the dependencies between all replicas in all availability groups, click View map for all availability groups. To return from this map to the Always On panel, click Back.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating