Chat now with support
Chat with Support

Foglight for SQL Server (Cartridge) 5.9.5.10 - 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 PI Repository Cold Backup Procedure 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 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

Configuring Multiple Instances for Monitoring using Silent Installation

1
Navigate to Administration > Cartridges > Components for downloads.
mssql_cli_installer.groovy — a groovy script file that runs the silent installation. This file should be copied to the <FMS_HOME>/bin directory.
silent_installer_input_template.csv — a template file that should serve as the basis for inserting contents into the input CSV file. This file can be copied to any folder of your choice, if the path indicated by the <csv_instances_file_name> parameter (see below) points to the selected path. For details about the contents of this file, see Contents of the Input CSV File .
MSSQLPermissionsCheck.sql — this file contains the SQL that the user should run on the monitored instance to check permissions needed for monitoring
MSSQLPermissionsGrant.sql — this file contains the SQL that the user should run on the monitored instance in order to grant the instance the required permissions for monitoring.
5
Copy the mssql_cli_installer.groovy file to the <FMS_HOME>/bin directory.
6
Go to the command line and run the command: <FMS_HOME>/bin/fglcmd -srv <fms_host_name> -port <fms_url_port> -usr <fms_user_name> -pwd <fms_user_password> -cmd script:run -f mssql_cli_installer.groovy fglam_name <fglam_name> instances_file_name <csv_instances_file_name> lockbox_name <lockbox_name> lockbox_password <lockbox_password>
A file with the input file's name and _status suffix (for example: if the input file is named input, this file is named **input_new_-_**).
The _status file includes the name of the monitored Foglight for SQL Server agent, the result of the monitoring validation process — MONITORED, FAILED, or AGENT EXISTS — and the error message, in case the agent creation failed.
A file with input file's name and _new suffix (for example: if the input file is named input, this file is named input_status).

Contents of the Input CSV File

The input CSV file contains the following fields, which are used as columns in the resulting file:

Description

The SQL Server host name.

The name of the SQL Server instance.

The SQL Server instance port. If no value is inserted in this field, the port number should match the instance name.

DB authentication type. The possible values are:

If this field is left empty, the Windows_Default_Account option is used.

The user name required for connecting to the database. If the Windows_Default_Account value was selected, this field can be left empty.

The password required for connecting to the database. If the Windows_Default_Account value was selected, this field can be left empty.

The type of database authentication of a user that can grant privileges to the regular database user. The possible values are:

If this field is left empty, the grant privileges script will not run.

The user name of a user that can grant privileges to the regular database user required only if the regular user does not have sufficient privileges.

The password of the database power user name mentioned above; required only if the regular user does not have sufficient privileges.

Specifies whether to create an Infrastructure Cartridge agent for the given host name; the possible values are either True or False.

OS authentication type; can have one of the following values:

If this field is left empty, the Windows_Default_Account value is used by default.

The user name required for connecting to the OS if the Windows_Default_Account option was selected. The user name should be compatible with the selected authentication type.

If the Windows_Default_Account value was selected, this field can be left empty.

The password of the OS user specified above; if the Windows_Default_Account option was selected, this field can be left empty.

When using this property, the 'OS User Name', 'OS Password', 'Private Key File Path' and 'Passphrase' properties will be ignored.

Specifies whether to monitor VMware metrics; the possible values are either True or False.

The VMware host name.

The VMware port. If no value is inserted in this field, the port number will use the default port number 443.

The user name required for connecting to the VMware.

The password of the VMware user specified above.

Indicates whether to enable PI; the possible values are True or False. Leaving this field empty indicates a False value.

A PI repository must be already installed and assigned to at least one agent on the monitoring FglAM.

Indicates whether the given monitored instance is in a mounted stand-by mode (data-guard); the possible values are True or False. Leaving this field empty indicates a False value.

The database group names you want to add the monitored instance to; Multiple group name should separate with ";". For example: groupA;groupB;groupC

When the group already exists, then the monitored instance will be attached to the existing group. When the group does not exist, then new group will be created under the default group "Database Groups".

Configuring the On Demand Data Port on the Agent Manager Concentrator

If a firewall is installed between the Management Server and the Agent Manager, a bidirectional On-Demand Data Port connection is not possible because On-Demand data requests require direct connection.

This issue can be resolved by creating an On-Demand Data Port on the concentrator — an Agent Manager instance that works similarly to an HTTP proxy. This On-Demand Data Port can be configured to accept On-Demand data requests from the Management Server and direct them to the Agent Manager instances (called downstream instances). For additional details about the concentrator, see Configuring an Agent Manager Instance as a Concentrator in the Agent Manager Installation Guide.

A concentrator agent, created specifically for Foglight for SQL Server, allows controlling the On-Demand Data Port so that the server is initialized (starts collecting data) when the agent starts, and stops when data retrieval by the agent ends. The agent, which the user creates manually, requires configuring the following parameters:

Each time the Foglight Management Server sends RMI request, the list of downstream hosts submitted by the concentrator agent is being searched, as follows:

1
Go to Dashboards > Administration > Agents > Agent Status.
2
Click Create Agent.
3
Select the agent type DB_SQL_Server_Concentrator.
5
Click Create.
2
Click Edit Properties.
3
Click Modify properties for this agent only.
6
Click Edit to edit the list of downstream Agent Managers.
7
Click Add Row.
IMPORTANT: When editing the properties of the Foglight Agent Manager concentrator (Dashboards > Administration > Agents > Agent Status), the name of the Foglight Agent Manager should be entered in the Downstream FglAMs section exactly as it appears in the topology, under Home > Agents > All agents > <Agent name> > RMI data > FglAM host property. The <Agent name> parameter refers to the Foglight for SQL Server agents that reside on the selected Foglight Agent Manager.
9
Repeat Step 7 to Step 8 as many times as required.
10
Click Save Changes.
11
Click Save at the lower right corner of the screen.
12
Click Back to Agent Status.
14
Click Activate.
15
Click OK to complete the process.

In an environment that includes a Foglight Agent Manager concentrator, when upgrading the environment where the concentrator does not contain any regular agents, the concentrator is not upgraded. Therefore, after the upgrade process takes place the Foglight Agent Manager concentrator has to be deployed manually.

 

 

Using Foglight for SQL Server

Foglight for SQL Server monitors the SQL Server database activity by connecting to and querying the SQL Server database. The agents provided monitor the SQL Server database system. The dashboards included with the cartridge provide a visual representation of the status of the major components of the SQL Server agents. They allow you to determine any potential bottleneck in database performance.

Related Documents