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

Managing Foglight for SQL Server Agent Settings

You can use the Databases Administration dashboard to set options for collecting, storing, and displaying data about monitored SQL Server instances.

This section covers the following key areas:

• Viewing the Databases Administration Dashboard
  
• Reviewing the Administration Settings
• Customizing Alarms for Foglight for SQL Server
• Defining Connection Details
• Defining Error Log Filtering
• Configuring Performance Counters
• Setting Options for Displaying Data in the Buffer Cache
• Setting Options for Displaying Data in the Plan Cache
• Defining the Collection of Database Indexes
• Configuring User-defined Collections
• Administering SQL Performance Investigator
• Configuring the Database to be Excluded

Viewing the Databases Administration Dashboard

You can edit agent settings for one or more SQL Server instances on the Databases > Administration dashboard.

To open the Databases Administration dashboard:

1. In the navigation panel, under Homes, click Databases and then SQL Server.
2. Select the check boxes beside one or more SQL Server instances.
3. Click Settings and then click Administration.

The Administration dashboard opens, containing settings for all the selected agents. Settings are broken down into categories, which are organized under a SQL Server tree.

Reviewing the Administration Settings

The Databases Administration dashboard allows settings options for collecting, storing, and displaying data, which apply to all the currently selected agents. Click a category of settings on the left (for example: Connection Details) to display a view containing related settings on the right.

The metrics defined under the Databases Administration dashboard apply to all of the agents that were selected before opening the Databases Administration dashboard. As a result, the same unit of measure and aggregation value for display are enforced for all currently selected agents.

The SQL Performance Investigator category allows you to enable or disable SQL PI for the agents selected. In addition, you can also start or stop change tracking for the agents.

To view the full list of selected agents, click Selected Agents at the upper right corner of the screen. To change the list of agents to which the metrics will apply, exit the Databases Administration dashboard, select the requested agents, and re-open the view.

If the settings vary between the selected agents (for example: one agent uses the measurement unit kilobyte, while another uses megabyte), the fields that contain non-identical values are displayed as empty and marked with an Inconsistent Values () icon.

Changes made to settings should be saved before selecting another category of settings.

To save changes made in an Administration dashboard view:

1. In the Database Administration dashboard, select a category from the menu.
2. Make changes to settings as necessary.
3. Click Save changes at the bottom of the view. If you attempt to exit the view without saving changes, a Warning dialog box prompts you to confirm your action.

Customizing Alarms for Foglight for SQL Server

Many Foglight for SQL server multiple-severity rules trigger alarms. To improve your monitoring experience, you can use alarm templates to customize when alarms are triggered and whether they are reported. You can also set up email notifications. For more information, refer to Working with Alarms.

Defining Connection Details

Use the Connection Details category to define global connection settings, which apply to all instances and hosts selected in the view. You can configure SQL Performance Investigator connectivity, enable user-defined collections, and set VMware connection details.

Defining the Connection Settings for the Monitored Instances

The Connection Details view contains a table that displays all the agents that were selected before entering the Databases Administration dashboard.

To define the connection settings for the requested agents:

1. Select the check boxes to the left of the agents for which uniform credentials are to be set.
2. Click Settings, and then click Administration. The Administration dashboard opens, containing settings for all the selected agents.
3. Click Connection Details, and then click Set credentials. The Set dialog box used for editing the credentials of the selected instance appears.

   If multiple instances were selected before clicking Set credentials, this dialog box is empty (does not display the names of the instances, ports, and connection details). The default options in such a scenario are Windows authentication (for SQL Server connection details) and the use of existing host connection details (for OS monitoring).

4. On the Edit Credentials pane, provide port connection details. This field can be left empty, unless the TCP/IP connection port is not the default port: 1443.
5. Specify the SQL Server login credentials using either of the following authentication methods:
   • Active Directory (AD) Authentication- log in with the Active Directory account running on your agent manager or enter a new AD account. The user name should be entered in the following format: domain\user name.
   • SQL Server Authentication- log in using a SQL Server account
6. Use SSL: Select whether an SSL connection should be used.
7. In the Monitoring Extensions section you can select and configure which monitoring extension to add to the monitored instance:
   • Operating System- Correlate the SQL Server OS resources with the entire host.

     Monitoring OS can be made also by using stored credentials- Select this link to open the Stored Credentials dialog box, which allows you to review the login credentials and authentication methods used for logging in to Foglight.
     Foglight stores encrypted credentials in lockboxes, which may be password-protected for added security. The Credentials dialog box and DB-Agent Lockbox. If credentials have already been entered in another lockbox, use the Lockbox list to select from that lockbox.

   • VMWare- Collect VM statistics.
8. Click Set.
9. On the Connection Details pane, click Test Connection.

   If the monitoring verification fails, click the message that is displayed on the Status column and resolve the issue according to the instructions that appear in the dialog box. For example, insufficient privileges, incorrect credentials or an Agent Manager that reached its full monitoring capacity.

   Upon successful completion of the process, the status shows as Verified.
10. The option of enabling and editing credentials for user-defined collections is available also when multiple instances are selected. This option is carried out as follows:
    1. Click Set UDC Credentials. The Edit Credentials for User-defined Collections dialog box appears.
    2. Select the Enable user-defined collections check box.
    3. Select whether to perform the collection:
       • Using the current agent credentials
       • Manually specifying login credentials.
       • Using Windows authentication and the Windows account that is running Foglight.
    4. If you choose the option of manually specifying login credentials, then select the requested authentication method from one of these options:
       • Windows authentication- enter a user name and a password in the domain\username format (for example, COLUMBIA\JSmith).
       • SQL Server authentication

         If the Foglight Agent Manager that runs the instance resides on a UNIX host, the option of using Windows authentication and the Windows account that is running Foglight is unavailable.

    5. Click Set to return to the dialog box used for editing the instance’s credentials.

       To add user-defined collections, go to the User-defined Collections view in the Databases Administration dashboard. For more details, refer to Configuring User-defined Collections.

11. If you have SQL Server instances that run on virtual hosts, such instances require setting a dedicated connection profile, in order to connect to the requested VMware server. Establishing such a connection is necessary in order to retrieve the Virtualization overhead data, that is, the percentage of CPU that is unavailable to this virtual machine because it is being utilized either by other virtual machines or by VMware itself. The Virtualization Overhead indicator is displayed in both the realtime and history summary pages.
    
    To edit the VMware Connection Profile:
    1. Select the requested agents. To select all agents, select the check box on the title column of the table.
    2. Click Edit. The Edit VMWare Credentials dialog box appears.
    3. Select the Enable collecting VMWare CPU allocation data check box.
    4. Enter the details required for monitoring the CPU distribution data, that is, host, port, VMware user, and VMware password.
    5. Click OK. If multiple agents were selected, the settings will apply to all agents.
12. Ensure that all requested data has been entered. If so, click Test Connection. A progress bar appears.
    At the end of this process, any connectivity issues are indicated by the Status column of the agents table. This column displays either the status Verified for the instances that connected successfully to the database, or a status that indicates failure of the connectivity verification process, and the reason for the failure (for example, Login failed for user “X”).
    
    If the connectivity issue results from faulty login credentials, modify the credentials and carry out again the connection verification process. If the database to which the instance tries to connect is not running, clear the box against the appropriate database name.
    
    If some of the instances whose verification failed display a status of either Insufficient privileges, Click to Grant or Wrong sysdba Credentials, then such instances should be granted privileges, by clicking the status and using one of the following methods:

• Manually, using a script by clicking View script, copying the text and using it to grant privileges.
• By clicking Grant privileges. The Grant Privileges dialog box appears.
  
  If Insufficient Privileges are indicated, this dialog box allows specifying a SYSAdmin (System Administrator) user with sufficient privileges. Enter a SYSAdmin user and password, and then click Grant Privileges.

13. Click Save Changes. The Applying Modified Settings progress bar appears.

Upon successful completion of this process, the Status column of the instance table displays the status Changes applied. For instances that failed verification, the status column indicates that changes cannot be saved if the validation did not complete successfully.

Defining Error Log Filtering

The Error Log Filtering view allows selecting which error logs generated by the SQL Server database are to be displayed in the Logs drilldown. The error log alarm increments the error log count for each error log entry that matches one of the strings listed in this view.
Foglight for SQL Server provides a default list of error logs enabled in the scanning, and allows adding, modifying, or disabling error logs from the list.
The settings defined using the Error Log Filtering view affect the following alarms: SQL Agent Error Log Summary, SQL Server Error Log Summary, SQL Agent Log Informational Error, and SQL Server Log Informational Error.

The Error Log Filtering view allows defining the following settings:

• Minimal severity for invoking summary alarms- can be either turned off or set to one of the defined severity values, that is: Warning, Critical, or Fatal.

  By default, the value of this parameter is Critical.

• Minimal severity for invoking alarms — can be either turned off (the default setting) or set to one of the defined severity values: Warning, Critical, or Fatal.

For all SQL Server versions, the Error Log Filtering view allows defining the following settings:

• Display under a pre-defined name in the SQL Agent Error Logs and SQL Server Error Logs panels- using the Match List pane. This pane contains a default list of expressions within the alert logs that are to be retrieved and displayed in the Alert Log panel under a pre-defined name, category, and severity.
• Exclusion from the SQL Agent Error Logs and SQL Server Error Logs panels display- using the Ignore List pane, which contains a default list of alerts that are to be excluded when setting the alert logs display.

  Only messages that are explicitly defined in the Ignore List will not be displayed. Messages that were not added to either the Match or Ignore lists appear under name Other, type SQL Server errors messages and severity Informational. Therefore, ensure that messages that need not be displayed are added to the Ignore List.

Both the Match List and the Ignore List panes can be customized by adding, editing, or removing alert logs. Each filter can be enabled or disabled separately by clicking Edit and selecting or clearing the Enabled check box. Alternatively, to enable or disable all of the filters, click the Enable All or Disable All.

To add an error to the Match List:

1. Click Add. The dialog box Add Filter appears. Use this dialog box to assign a name to the filter. Alert filters are enabled automatically upon addition. To disable a filter, use the Edit button.
2. Click Add to save your settings. Each newly added alert filter is enabled by default. To disable the filter, click Edit and then clear the Enabled check box.

The entire list of filters displayed on the view can be enabled or disabled by clicking Enable All or Disable All at the bottom of the screen. Enabling a single filter requires editing it.

To edit a filter:

1. Select the requested filter.
2. Click Edit. The Edit Filter dialog box appears. Ensure that the Enable Filter check box is selected (default).
3. To change the name of the filter, edit the text in the Error Log Filter text field.
4. Click Set.
5. If another filter should be edited, repeat Step 1 to Step 4. If no more editing is necessary, or if all additional editing operations were carried out, save the changes before switching to another view.
   

Configuring Performance Counters

The Performance Counters view allows configuring user-defined performance counters and their Unit of Measurement/Indicator. The counters created in this view are accessible using the User Metrics drilldown.

Each user-defined performance counter is collected and plotted over the specified time range in the User Metrics drilldown.

The User Metrics drilldown is used only for displaying the user-defined performance counters; any creation or management operation of these counters is carried out using the Performance Counters view.

Use this view to add user-defined collections of performance counters to a specified agent by selecting them from the complete list of available performance counters. The user-defined counters value refers to raw data, which is derived directly from the counter provider.

To retrieve performance counters for a specific agent:

1. Select an agent from the list.
2. Click Add Counters at the bottom of the screen. The Retrieving Performance Counters progress bar appears. Upon successful completion of this operation, the Add Counters dialog box appears.
3. Select the requested counters. For each counter select the matching instance and unit.
4. Click Add.
5. After adding all of the requested counters, click Close to exit the Add Counters dialog box. All of the newly added performance counters now appear in the table. To delete unwanted counters, select the requested counter and click Remove. To add more counters, repeat Step 2 to Step 5.
6. After carrying out all of the requested changes, save the changes before switching to another view.

Setting Options for Displaying Data in the Buffer Cache

The Buffer Cache view allows configuring the default retrieval settings for Buffer Cache panel, accessed through the Memory drilldown.

A lower buffer cache hit rate can be resolved by investigating the objects that consume the largest amount of cache size.

Use this view to configure the number of objects to be displayed in the table and their sorting properties. Click Edit in the Buffer Cache Settings section to edit the following parameters:

Parameter	Description
Top N Buffer Cache Objects by Cached MB	Defines the maximum number of objects to be displayed in the Buffer Cache panel (default: 20).
Order direction	Defines whether the order of the items for retrieval is ascending or descending.
Order by	Defines the parameter by which the display is to be ordered

After carrying out all of the requested changes, save the changes before switching to another view.

Setting Options for Displaying Data in the Plan Cache

The Plan Cache view allows configuring the default display settings for the SQL Server’s plan cache panel, accessible using the Memory drilldown.

Use this view to filter the Plan Cache panel’s display, by setting criteria such as which object type is displayed, as well as the default number of records displayed and the sorting method. Click Edit in the Plan Cache Settings section to edit the following parameters:

Parameter	Description
Show Adhoc objects	Defines whether to display Adhoc SQL plans.
Show system objects	Defines whether to display SQL Server system objects.
Filter Database Name	Defines whether to display only objects that reside on certain databases. When setting this parameter, the character % can be used as a wild card. For example, to display objects from all of the databases that begin with Quest (Questdatabase, QuestWorkDatabase, and so on), enter Quest%.
Top N Records	Defines the maximum number of objects to be displayed in the Plan Cache panel (default: 20).
Filter Object Name	Defines whether to retrieve only certain objects. When setting this parameter, the character % can be used as a wild card.
Order by	Defines the criterion for determining the data display order. The available criteria are as follows: Database Name (Default)-the name of the database Schema Name- the name of the schema Object Name- the name of the object Object Type- the object type Use Count (Default)- the number of times this cache object has been used since inception Ref Count- the number of other cache objects that reference this cache object Cache Object Type- the type of the cache object SQL Bytes- the size of the text SQL Text- the SQL text Used Date Format- the date format used by the object Used Language- the language format used by the object MB- the amount of space in the plan cache that is allocated to this object % from Cache- the percentage of cache used by the object Used MB- the size, in megabytes, used by the object type
Order direction	Defines whether the display would be carried out in ascending or descending order.

After carrying out all of the requested changes, save the changes before switching to another view.

Defining the Collection of Database Indexes

The Database Indexes view allows configuring the requested settings for collecting and displaying data under the Database > Indexes pane.

To define the settings for collecting and displaying database indexes:

1. Click Edit. The Edit Database Indexes Settings dialog box appears.
2. Use the Maximum number of collected indexes field to enter the maximum number of database indexes that are to be retrieved by the agent.
3. Use the Sort the collected indexes by list to select the field by which the list is to be sorted. The possible values are as follows:
   • Rows- number of rows in each index
   • Index MB- the size of the index in megabytes
4. Use the Collect indexes of type field to select which types of indexes the agent collects. The possible types are as follows:
   • All
   • Clustered
   • Non-clustered
   • Heap
5. Click Set to save these settings.
6. After carrying out all of the requested changes, save the changes before switching to another view.

Configuring User-defined Collections

The User-defined Collections view in the Databases Administration dashboard allows adding user-defined collections to all of the currently selected agents, to provide for queries not included in Foglight for SQL Server.

After collections are added, this view displays all of the user-defined collections for all of the agents; for example, if a collection was added to 12 agents during its addition, the view will display 12 rows, showing the collection for each agent.

This view can also be used for configuring the sampling frequency for each collection.

The available sampling frequencies are as follows:

• Real-Time- When a user is currently focusing on a screen, the sampling frequency for all of the collections associated with this screen switches to Real-Time.
• Online- When at least one user is connected, the sampling frequency for all of the collections that are not. currently running at Real-Time frequency switches to Online.
• Offline- When no user is currently connected to the application.

To add user-defined collections:

1. Click Add. The User-defined Collections screen appears.

   If one or more of the selected agents is not enabled for user-defined collections, a dialog box appears to notify this issue, displaying a table of the currently disabled collections. Use this dialog box to enable all agents for user-defined collections. To enable only part of these agents, exit the Databases Administration dashboard and select only these agents that are currently enabled or need to be enabled for user-defined collections before entering again the Databases Administration dashboard.

2. Enter a name in the Collection Name field.
3. Enter a brief description of the collection in the Collection description field (optional).
4. Paste the query’s SQL text in the Query Text field.
5. Enter a value, in seconds, in the Query Timeout field.
6. Click Verify. The collection is verified by running the query on each of the currently selected agents. After the verification process is complete, the Verification Results pop-up appears, indicating whether the collection was verified successfully. In case the collection verification failed, the error message is displayed.
   If the verification succeeded on at least one agent, the collection details are displayed as below:

Section	Field	Description
Database	ID Column Name Column Type	The collections’ ID The name of the column The field type, as retrieved by the query (String, Integer and so on)
Data Storage	Display Name	The column’s display name
	Type	The topology type for storage purposes. This type can be one of the following: String Integer Double Boolean Date Note: When the Frequently modified check box is selected (the default state), the field’s change history is kept, including use of optional functionality such as use of the IntelliProfile mechanism and aggregation type selection. Clearing this check box is recommended only for fields whose values change infrequently, such as IP address of a specific host, as storing the change history of such fields is highly CPUintensive and may degrade the FMS performance.
	Unit of Measurement/Indicator	The metric’s Unit of Measurement/Indicator. The possible measurement unit values are: Percent, Count, Millisecond, Second, Minute, Hour, Day, Byte, Kilobyte, Megabyte and Gigabyte.
	Aggregation	Allows defining the value that is displayed in this metric, out of several values that were returned in the relevant time range. The available aggregation values are: As is- value of the last sample taken during the selected time range Sum- summarized value of all samples taken during the selected time range Average- average value of all samples taken during the selected time range
	Is Key	Indicates whether the field is the query’s key for retrieval. Note: Fields that are indicated as keys should correspond with the database result set unique values. Selecting a field which has frequently changing and repeating results as key is allowed, but may result in unexpected behavior from the defined collection.

Except for the Column Name and Column Type fields, whose values are retrieved by the query and cannot be changed, all other fields can be edited by clicking any of them. The Edit Collection Properties dialog box appears, allowing you to edit the values of the following parameters:

• Display name
• Type
• Unit of Measurement/Indicator
• Aggregation In addition, the collection’s sampling frequencies are displayed on the table at the bottom of the view, and can be edited by clicking any of them.

7. Ensure that all settings are appropriate, and click OK to finish the collection creation process. The newly created collection now appears on the table.

   After adding the requested user-defined collections, they can only be deleted or cloned to other agents. If one or more queries need to be modified, delete them and create new ones.

Administering SQL Performance Investigator

The SQL Performance Investigator view in the Administration dashboard allows you to enable and disable SQL PI monitoring for selected agents. In addition, you can start and stop the collection of change tracking data. In the SQL PI view, select one or more agents to enable or disable or for which to modify the change tracking status.

Configuring the Database to be Excluded

Monitoring all of the databases within all of the agents can unnecessarily load the system, as not all databases require such monitoring, due to being either non-mission critical or less significant.

The Exclude Databases from Monitoring view allows excluding such databases from monitoring. Use this view to select the agents from which databases are to be excluded from monitoring, and then specify, either manually or by selecting from a list, which databases to exclude.

To exclude databases from monitoring:

1. Click Exclude databases. The dialog box Specify Databases for Exclusion appears.
2. Use the list Select specific agent or all agents to select an agent from which databases are to be excluded. Alternatively, select the option All selected agents. 3 Specify which databases to exclude from monitoring, either by adding their names manually or by selecting them from the list and clicking the arrow icon to move them to the list of excluded agents. 4 Click OK.